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.

Le dernier des DBAs

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

    Lire la suite

  • 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

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

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



  • 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