[POSTGRES] Easy to migrate

Little bash script

Oracle backup batch file

Oracle backup batch file - Stack Overflow

need to split in two files: a batch file and RMAN script

Batch file: backup.bat

@echo off
echo Automatic Backup Oracle
pushd .
SET ORACLE_HOME=C:apporacleproduct12.2.0dbhome_1
SET ORACLE_SID=ORCL
echo ----------------------------------------------------
echo ORACLE_HOME : %ORACLE_HOME%
echo ORACLE_SID  : %ORACLE_SID%
echo ----------------------------------------------------
RMAN TARGET / @file.rman log=backup.log

Script RMAN called file.rman or another name

run {
backup database;
backup archivelog all delete input;
} 

This part is setup:

CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'c:backup%F';
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'c:backup%d_%T_%u';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'c:backupSNCFORCL.ORA';

Can be removed from the script and run once for each bank. Once configured, the settings are permanent.

script need two files: backup.bat and file.rman

[Postgres] Mettre à jour les séquences

Script pour mettre à jour les séquences. Indispensable pour la réplication logique. 

 

PostgreSQL Defrag les plus gros indexes et tables

Réduire la fragmentation 

https://www.timescale.com/learn/how-to-reduce-bloat-in-large-postgresql-tables

Migration from Oracle Legacy to postgreSQL dMZR

Migration from Oracle Legacy to postgreSQL dMZR

[SYSTEM]

This script uses the local tnsnames.ora file to create a connection, and requires the variable ${p} to hold the current SYSTEM password.

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

[HVAULT] Roles postgreSQL

Script de création des roles HVAULT dans ibm cloud pour  postgreSQL (dMZR);

[PostgreSQL] Streaming Replication Monitoring Script

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

[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