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

  • Migration from Oracle Legacy to postgreSQL dMZR

    //on the ora2Pg management server : 

    0- prerequisite 

    create role role_own_DB login;

     

    1 - export schema

    gives the metadata

     

    // on the dMZR jump server (inside ibm cloud)

    2 - create the roles in hashicorp vault ( script : new_deploy.sh) 

    create the roles : dba/own/app

     

    //on the ora2Pg management server : 

    3 - import schema (script : automation.sh)

    import the metadata and upload the data inside the DB postgresql.

    4 - backup the postgresql database (pg_dump)

    pg_dump --format custom --clean --no-owner --no-privileges apro > bckp_DB.dmp

    5 - transfert the backup to dMZR jump server

     

    // on the dMZR jump server 

    5 - restore only the schema (metadata and data)  on ibmclouddb (pg_restore)

    pg_restore --role=role_own_DB -n <schemaTOIMPORT> -d ibmclouddb bckp_DB.dmp

  • Modify the owner of the tables and the sequences (post migration) 

    Modify the owner of the tables and the sequences (post migration) 




    #change table owner
    for tbl in `psql -h $SERVER$DOMAIN -p $PORT -U <dbauser> -qAt -c "select tablename from pg_tables where schemaname = '<your_schema>';" <database>` ; do  psql -h $SERVER$DOMAIN -p $PORT -U <dbauser> -c "alter table "$tbl" owner to <your_role>" <database>; done

    #change sequence owner
    for tbl in `psql -h $SERVER$DOMAIN -p $PORT -U <dbauser> -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = '<your_schema>';" <database>` ; do  psql -h $SERVER$DOMAIN -p $PORT -U <dbauser> -c "alter sequence "$tbl" owner to <your_role>" <database>; done

  • Activité (BNP Paribas Fortis)

    • Création d’un document technique d’exploitation pour la migration des bases oracle vers postgresql.
    • Création d'un document technique d'exploitation pour la création des bases postgresql en dMZR
    • Script de déploiement en dMZR
    • script de migration de legacy vers dMZR. 
    • Template script de connexion aux bases du cloud.

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