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

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.

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

[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] Streaming Replication Monitoring Script

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

Découpage fichiers BDD MSSQL

split_db_mssql.pdf

[script] migration ora2pg

migration_ora2pg.pdf

Optimisation table - stockage - 2 procs stock

affiche_espace_occupe d'une table.

affiche_espace_inutilisee d'une table.

Oracle 12C : Scripts d'admin

Scripts d'administration d"Oracle 12C extrait du livre Oracle12c Administration aux édition Eyrolles.

chkxml.txt

dPDB.txt

sPDB.txt

listall.txt

modifpara.txt

seekpdb.txt

showinst.txt

triPDB.txt 

Audit Oracle

Simple audit Oracle à lancer (fichier SQL).

Oracle Tuning

Scripts de Tuning pour Oracle, déposer les scripts sur la machine à tuner et lancer le script : ./main_menu_audit.sh

 

Get Op

#!/bin/bash
function usage
{
 if [ $# -gt 0 ]; then
        echo $@ >&2
 fi
 cat << EOF >&2
usage : t [-v][-h host_name][-P port_num][-u user_name][-p password][-D db_name]
Other parameters (-h, -P, ... -D) = Same as mysql command (refer to mysql documentation).
EOF
 exit 1
}
function cat_args
{
 echo $msql $1 $2
}
msql='/usr/local/mysql5/bin/mysql '

called_with="$@"
while getopts "vh:P:u:p:D:" arg
do
        case $arg in
                h|P|u|p|D)
                        msql=$(cat_args -$arg$OPTARG)
                        ;;
                v)
                        verbose=1
                        ;;
                *|?)
                        usage incorrect parameter
                        ;;
        esac
done

echo $msql
echo show tables|$msql

[Oracle]Nouveau script de duplicate RMAN

Le duplicate.sh

ET le rcv qui va bien...

 

dossier partagé sur google drive

Des petits scripts ?

Qui qui veut des petits scripts ...?

 

LA