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.

  • [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



  • un peu de compta

    règle de calcul pour l'octroit d'un prêt immo pour une société avec bilan comptable :
     
     - moyenne de l'excédant brut d'exploitation des deux dernières années 

    règle de calcul pour l'octroit d'un prêt immo pour une micro sans bilan comptable :

     - 66 % du CA de la dernière année.

  • 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.

    Lire la suite