Migration de base oracle vers postgreSQL

Script de déploiement en préparation d'une migration de base oracle vers postgreSQL (dMZR) avec roles hashicorp.

EDB feedback

Why Mastercard's Secret to Zero Downtime is Postgres (enterprisedb.com)

[ora2pg] Conversion datatype

Conversion datatype 

useful to convert object into postgresql

[HVAULT] Roles creation for dMZR postgreSQL

Roles : own , app,  devpg

[Cloud IBM PSQL] Role creation with HVAULT (static dba)

script : prepare_dba_static_role.sh

 

[Cloud IBM PSQL] Role creation with HVAULT

Roles creation using hvault : 

- dynamic roles

The roles are created with this command :

vault write -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/${SERVER}/roles/${ROLE} 
db_name=${SERVER} 
creation_statements=@${ORA2PG_HOME}/dynamic_role/${TYPE}/create_dyna_role_${TYPE}_${ASSET}.sql 
revocation_statements=@${ORA2PG_HOME}/dynamic_role/${TYPE}/drop_dyna_role_${TYPE}_${ASSET}.sql 
default_ttl=${ROTATION} 
max_ttl=${ROTATION}

[PostgreSQL] Privilèges

Privs useful to any new user created.

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

[Systeme] Create a logical volume for ora2pg

Create a logical volume of 450 Go

[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

[Oracle] Calculate the lobs size

#!/bin/sh
#script to calculate the lobs size

sqlplus /nolog<<!EOF 
conn / as sysdba
spool req1.sql
set head off
set feed off
select 'SELECT max(dbms_lob.getlength('''||col.column_name||''')) from '||col.owner||'.'||col.table_name||';'
from sys.dba_tab_columns col inner join sys.dba_tables t on col.owner = t.owner and col.table_name = t.table_name where col.data_type in ('BLOB', 'CLOB', 'NCLOB', 'BFILE') 
and col.owner not in ('GSMADMIN_INTERNAL','OCOR_OWN','AUDSYS','ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST', 'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS', 'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
;
spool off
exit
!EOF

sqlplus -s / as sysdba  @req1.sql

[PostgreSQL] pgBackRest sauvegarder la standby

Backup From a Standby | pgBackRest (pmatseykanets.github.io)

[PostgreSQL] Vérifier le statut de la réplication

On master:

select * from pg_stat_replication;

On replica (streaming replication in my case):

select * from pg_stat_wal_receiver;

On your master, pg_stat_replication provides data about ongoing replication:

select client_addr, state, sent_location, write_location,
        flush_location, replay_location from pg_stat_replication;

On postgresql v10:

select client_addr, state, sent_lsn, write_lsn,
    flush_lsn, replay_lsn from pg_stat_replication;

On server

postgres=# select usename,application_name,client_addr,backend_start,state,sync_state from pg_stat_replication ;


On client

postgres=# select pg_is_in_recovery();

postgres=# select pg_last_xlog_receive_location();

postgres=#    SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location()
                  THEN 0
                ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
              END AS log_delay;

postgres=# select pg_last_xlog_replay_location();

[PostgreSQL] COMPRESS

Detecting inline, inline-compressed and TOAST storage

+--+--------------+------------------+----------+-----------+-------------+
|id|full_name     |uncompressed_bytes|compressed|out_of_line|bytes_on_disk|
+--+--------------+------------------+----------+-----------+-------------+
|1 |joe toast     |4004              |false     |true       |4000         |
|2 |joe compressed|3000              |true      |false      |44           |
|3 |joe inline    |10                |false     |false      |11           |

[PostgreSQL] Find all the table and index size

SELECT
    TableName
    ,pg_size_pretty(pg_table_size(TableName)) AS TableSize
    ,pg_size_pretty(pg_indexes_size(TableName)) AS IndexSize
    ,pg_size_pretty(pg_total_relation_size(TableName)) AS TotalSize
FROM 
(
     SELECT ('"' || table_schema || '"."' || table_name || '"') AS TableName
     FROM information_schema.tables
) AS Tables
ORDER BY 4 DESC

source :How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database (tutorialdba.com)

[PostgreSQL] Streaming Replication Monitoring Script

Script de création d'une fonction "streaming_slave_check" pour la réplication en postgreSQL.

[PostgreSQL] repmgr tuto

Tutoriel d'installation pour repgmr

[PostgreSQL] Anonymisation des données

Procédure d'anonymisation des données

[PostgreSQL] Déploiement d'un cluster PATRONI

Convert a Standalone to a Patroni Cluster

Déployer le cluster PATRONI dans un nouvel environnement

[PostgreSQL]Kill une session

SELECT *, pg_terminate_backend(pid)
FROM pg_stat_activity 
WHERE pid <> pg_backend_pid()
AND datname = '<dbname>';

select pg_terminate_backend(pid) 
from pg_stat_activity
where pid = '<PID>';

[ORACLE]Evaluer une base de données

Il peut être utile d'évaluer une base de données avant de penser à la migrer dans un autre SGBD.

On va alors s'intéresser aux types d'objets qu'elle contient pour valider ou invalider la migration dans tel ou tel SGBDR.

Exemple le plus probant : une migration d'une base oracle dans une base postgreSQL.

La requête suivante générique et passe partout pourra aider à faire l'évaluation.

select (select name from v$database) as database_name, owner,sum(bytes)/1024/1024 as "Number", 'Size in MB' as object_type
from dba_segments 
where owner not in ('WMSYS','APPQOSSYS','DBSFWUSER','DBSNMP','AUDSYS','GSMADMIN_INTERNAL','ORACLE_OCM','OUTLN','PUBLIC','REMOTE_SCHEDULER_AGENT','XDB','SYS','SYSTEM')              
group by owner              
union
select (select name from v$database) as database_name,
col.owner as schema_name, count(distinct col.table_name),
'Lobs' as object_type
from dba_tab_columns col 
inner join dba_tables t on col.owner = t.owner and col.table_name = t.table_name 
where col.data_type in ('BLOB', 'CLOB', 'NCLOB', 'BFILE')
and col.owner not in ('OAUTHDBSCHEMA','AUDSYS','GSMADMIN_INTERNAL','ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST', 'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS', 'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC') 
group by col.owner 
union    
SELECT 
(select name from v$database) as database_name,
TABLE_OWNER,count(distinct table_name) as partitionned_tables,'Partitions'              
FROM   dba_tab_partitions                                    
WHERE TABLE_OWNER not in('WMSYS','SYS','SYSTEM','DBSFWUSER','DBSNMP','AUDSYS','GSMADMIN_INTERNAL','XDB','ORACLE_OCM')                    
group BY TABLE_OWNER
union    
select (select name from v$database) as database_name,
owner,
count(distinct object_name),
'Packages' as object_type 
from dba_objects where object_type='PACKAGE' and owner not in ('WMSYS','SYS','SYSTEM','DBSFWUSER','DBSNMP','AUDSYS','GSMADMIN_INTERNAL','XDB','ORACLE_OCM')               
group by owner
union
select (select name from v$database) as database_name,
owner,
count(distinct object_name),
'Procedures' as object_type 
from dba_objects where object_type='PROCEDURE' 
and owner not in ('WMSYS','SYS','OUTLN','REMOTE_SCHEDULER_AGENT','SYSTEM','DBSFWUSER','DBSNMP','AUDSYS','GSMADMIN_INTERNAL','XDB','ORACLE_OCM')
group by owner
union
select (select name from v$database) as database_name,
owner,
count(distinct object_name) , 'Functions'
from dba_objects 
where object_type='FUNCTION' 
and owner not in ('WMSYS','SYS','OUTLN','REMOTE_SCHEDULER_AGENT','SYSTEM','DBSFWUSER','DBSNMP','AUDSYS','GSMADMIN_INTERNAL','XDB','ORACLE_OCM') 
group by owner
union
select (select name from v$database) as database_name,
owner ,
count(distinct trigger_name),'Triggers' as object_type  
from dba_triggers
where owner not in ('GSMADMIN_INTERNAL','ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
   'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')  
group by owner
union
select (select name from v$database) as database_name,
owner,count(distinct table_name),'Global Temporary Tables' 
from dba_tables 
where owner not in ('GSMADMIN_INTERNAL','ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
   'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
  'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
   and TEMPORARY = 'Y'
   group by owner
union 
select (select name from v$database) as database_name, OWNER,count(distinct TABLE_NAME) ,'External Tables' 
 from dba_external_tables 
where owner not in ('GSMADMIN_INTERNAL','ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
   'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
   group by owner
union
SELECT (select name from v$database) as database_name,schema,count(distinct namespace),'Context' as object_type
  FROM dba_context
  where schema not in ('GSMADMIN_INTERNAL','ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
   'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','OCOR_OWN')
   group by schema;

 

Résoudre l'ORA-600 doc id 2523249.1

Optimizer expression statistics monitoring is introduced in 12.2 database version.

 For specific applications, this might end up in growing repository segments. Monitoring expression statistics is introduced for a new optimizer feature in 12.2 to collect expression usage statistics in SQL queries.

Monitoring feature is controlled by parameter "_column_tracking_level". High growth have been noticed when monitoring expression statistics is active, enabled by default.

 

 

Solution:

Disable future monitoring:

alter system set "_column_tracking_level"=17 scope=both;

--wait 10 minutes

 

To purge data from tables:

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();

truncate table sys.exp_head$ drop storage;

truncate table sys.exp_obj$ drop storage;

truncate table sys.exp_stat$ drop storage;

alter index SYS.I_EXP_HEAD$ rebuild tablespace sysaux online;

alter index SYS.I_EXP_STAT$ rebuild tablespace sysaux online;

alter index SYS.I_EXP_OBJ$ rebuild tablespace sysaux online;

 

To re-enable expression statistics monitoring later :

alter system set "_column_tracking_level"=21 scope=both;

Connexions aux db du cloud

#!/bin/sh
#-------------------------------------------------------------------------------
#
# Script to connect to IBM Cloud DB, version 2.0
# Author : JJY
#
#-------------------------------------------------------------------------------
#set -vx
##############
#INSTANCIATION
##############
DOMAIN="<domain>"
DATABASE="postgres"
USERNAME="<user_dba>"

while true
do
DATABASE="postgres"
 clear
 echo "CONNEXION AUX BASES DU CLOUD "
 echo " "
 echo "CONNEXION A l ECOSYSTEM : <ecosystem>"
 echo " "
 echo "1 - DEV(dbname) - serveur : <instance> connexion DBA"
 echo " "
 echo "2 - DEV(dbname) - serveur : <instance> connexion dynamique applicative"
 echo " "
 echo "3 - QUAL(dbname) - serveur : <instance> connexion DBA"
 echo " "
 echo "4 - QUAL(dbname) - serveur : <instance> connexion dynamique applicative"
 echo " "
 echo "0 - TOOLBOX VAULT "
 echo " "
 echo " Faites votre choix (0 a 4) :"
 echo " Taper Q pour quitter."
 echo " "
 read var
 case $var in
        1)
        echo "Connexion a la base, veuillez patienter..."
        export ECOSYSTEM=<ecosystem>;export SERVER=<instance>;export PORT=<port>;
        vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/$SERVER/static-creds/$USERNAME
        secret=$(vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM"  database/postgres/$SERVER/static-creds/$USERNAME | grep password |  awk '{print $2}')
        export PGPASSWORD=$secret;psql -h $SERVER$DOMAIN -p $PORT -U $USERNAME -d $DATABASE
        ;;
        2)
        export ECOSYSTEM=<ecosystem>;export SERVER=<instance>;export PORT=<port>;export DATABASE=<dbname>
        echo "Liste des roles:"
        vault list -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/roles
        echo " "
        echo "A quel role souhaitez-vous vous connecter:"
        read ROLE
        clear
        echo "Connexion a la base en mode dynamique, veuillez patienter..."
        var=$(vault read -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/creds/$ROLE)
        username=$(echo "$var" | grep username |  awk '{print $2}');
        password=$(echo "$var" | grep password |  awk '{print $2}');
        echo "User is : $username"
        echo "Password is : $password"
        echo " Tapez entrée pour continuer."
        read bidon
        export PGPASSWORD=$password;psql -h $SERVER$DOMAIN -p $PORT -U $username -d $DATABASE
        ;;
        3)
        echo "Connexion a la base, veuillez patienter..."
        export ECOSYSTEM=<ecosystem>;export SERVER=<instance>;export PORT=<port>;
        vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM" database/postgres/$SERVER/static-creds/$USERNAME
        secret=$(vault read -ns="$VAULT_NAMESPACE/$ECOSYSTEM"  database/postgres/$SERVER/static-creds/$USERNAME | grep password |  awk '{print $2}')
        export PGPASSWORD=$secret;psql -h $SERVER$DOMAIN -p $PORT -U $USERNAME -d $DATABASE
        ;;
        4)
        export ECOSYSTEM=<ecosystem>;export SERVER=<instance>;export PORT=<port>;export DATABASE=<dbname>
        echo "Liste des roles:"
        vault list -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/roles
        echo " "
        echo "A quel role souhaitez-vous vous connecter:"
        read ROLE
        clear
        echo "Connexion a la base en mode dynamique, veuillez patienter..."
        var=$(vault read -ns=$VAULT_NAMESPACE/$ECOSYSTEM database/postgres/$SERVER/creds/$ROLE)
        username=$(echo "$var" | grep username |  awk '{print $2}');
        password=$(echo "$var" | grep password |  awk '{print $2}');
        echo "User is : $username"
        echo "Password is : $password"
        echo " Tapez entrée pour continuer."
        read bidon
        export PGPASSWORD=$password;psql -h $SERVER$DOMAIN -p $PORT -U $username -d $DATABASE
        ;;
        0)
        ./toolbox.sh
        echo " Tapez entrée pour continuer."
        read bidon
        ;;
        Q)
        echo "Merci d'avoir utiliser ce programme."
        exit
        ;;
        *)
        echo "Merci de renseignez un chiffre entre 0 et 4."
        echo " Tapez entrée pour continuer."
        read bidon
        ;;
 esac
done
exit

[Cloud db] liste des commandes vault

#!/bin/sh

DOMAIN="<nom_du_domaine>"
DATABASE="postgres"
USERNAME="<userpostgres>"
ECO=<nom_ecosystem>
SERVER=<instance_de_l_ecosystem>
clear
echo "Entrer le nom de l ecosystem (ex:$ECO)"
read $ECO
echo "Entrer le nom de l instance (ex:$SERVER)"
read $SERVER

echo "-- Infos sur l instance :"
echo " "
echo "vault read -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/config/$SERVER  "
echo " "
echo "-- Liste des rôle dynamiques : "
echo " "
echo "vault list -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/roles "
echo " "
vault list -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/roles
echo " "
echo "-- Liste des rôle statiques : "
echo " "
echo "vault list -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/static-roles " 
echo " "
vault list -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/static-roles
echo "-- Informations sur un compte spécifique (dont les SQL de creation et révocation) : "
echo " "
echo "Entrez le nom du role:"
read ROLE
echo "vault read -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/roles/$ROLE"
echo " "
echo "-- Récupérer les creds d un utilisateur dynamique: "
echo " "
echo "vault read -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/creds/$ROLE"
echo " "
echo "-- Récupérer les creds d un utilisateur statique: "
echo " "
echo "vault read -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/static-creds/$ROLE"
echo " "
echo "-- Créer les creds d un utilisateur: "
echo " "
echo "vault write -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/roles/${ROLE} db_name=${SERVER} creation_statements=@create_dyna_role_OWN.sql revocation_statements=@drop_dyna_role_OWN.sql default_ttl=${ROTATION} max_ttl=${ROTATION} "
echo " "
echo "-- Révoquer un compte avant son TTL : "
echo " "
echo "lease_id  database/postgres/$SERVER/creds/OWN_IBMCLOUDDB_SCHEMA1/qdqsdqsdqsdsdqsdqsd.mxxxx"
echo " "
echo "vault lease revoke -ns=$VAULT_NAMESPACE/$ECO database/postgres/$SERVER/creds/$ROLE/qdqsdqsdqsdsdqsdqsd.mxxx "
echo " "

[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

Configuration application cloud postgres

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

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

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

Changer le owner des tables

#Change owner of tables qui sont à postgres

for tbl in `psql -qAt -c "select tablename from pg_tables where tableowner = 'postgres' and schemaname='SCHEMA';" dbname` ; 
do  psql -c "alter table SCHEMA."$tbl" owner to NEW_OWNER" dbname ; done

Postgres kill session

# kill sessions
# trouver la sessions
select * from pg_stat_activity;
select pg_terminate_backend(pid) 
from pg_stat_activity
where pid = 'PID';

POST MIGRATION

# 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] 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.