Table of Contents
Project Takedown
Documentation | |
---|---|
Name: | Project Takedown |
Description: | Born to takedown OracleDB |
Modification date : | 26/10/2020 |
Owner: | dodger |
Tags: | OracleDB, MySQL, PostgreSQL, Ceph… |
Description
This project has born to “takedown” Oracle Database from any organization that wants to be “free as in freedom” using any or many of the available free options in the “GPL” world.
Quick Demo
Oracle estructure
ssh dodger@192.168.56.100 tmux a sqlplus '/ as sysdba'
-- object information SELECT owner, object_type, object_name FROM dba_objects WHERE owner='HR' ORDER BY object_type ; SELECT object_name FROM dba_objects WHERE owner='HR' AND object_type='TABLE' ORDER BY object_type ; -- table row information ALTER SESSION SET CURRENT_SCHEMA=HR ; SET feed off SELECT COUNT(*) AS REGIONS FROM REGIONS ; SELECT COUNT(*) AS COUNTRIES FROM COUNTRIES ; SELECT COUNT(*) AS LOCATIONS FROM LOCATIONS ; SELECT COUNT(*) AS DEPARTMENTS FROM DEPARTMENTS ; SELECT COUNT(*) AS JOBS FROM JOBS ; SELECT COUNT(*) AS EMPLOYEES FROM EMPLOYEES ; SELECT COUNT(*) AS JOB_HISTORY FROM JOB_HISTORY ;
From Oracle to MariaDB/MySQL (Tables)
Using fully customizable ora2my script:
ssh dodger@192.168.56.100 tmux a cd /home/dodger/ora2my_transfer cat tables.txt bash ora2my_sync_v002.sh recreate "$(cat tables.txt)"
Check:
mysql -p$(cat mysql_password.txt) use takedown_hr -- table count select count(*) as REGIONS from REGIONS ; select count(*) as COUNTRIES from COUNTRIES ; select count(*) as LOCATIONS from LOCATIONS ; select count(*) as DEPARTMENTS from DEPARTMENTS ; select count(*) as JOBS from JOBS ; select count(*) as EMPLOYEES from EMPLOYEES ; select count(*) as JOB_HISTORY from JOB_HISTORY ; -- table description show create table REGIONS ; show create table COUNTRIES ; show create table LOCATIONS ; show create table DEPARTMENTS ; show create table JOBS ; show create table EMPLOYEES ; show create table JOB_HISTORY ;
From Oracle LOB to Amazon S3
Demonstration on how We can move oracle LOB (Large OBjects) to Amazon S3 (or any compatible technology, in this demo we will use min.io:
cd ./minio server data
Check: http://192.168.56.200:9000/
Using fully customizable script kill_tbuffer.py
:
cd ~/kill_tbuffer/ bash get_all_metadata.sh nosync HR
This script will:
- Connect Oracle
- Connect S3
- Get LOB's from Oracle and store them in S3 (it will not temporary store the objects on disk, but is customizable).
Extracting DDL/Metadata from Oracle
Using fully customizable script get_all_metadata.sh
:
cd ~/get_all_metadata bash get_all_metadata.sh nosync HR
This script will:
- Create destination directory (if not exists)
- Based on a pre-defenied list of objects, obtain the objects of all that types owned by the schema owner.
- Generate a sql file (
${SQLGENERATOR}
inside script), which will generate one.sql
file for every object in the configured database/scheme. - Execute the
${SQLGENERATOR}
in the target database, this will generate 1 sql file for every object. - The script can upload the generated files to github.
PL/SQL in MySQL
MariaDB 10.3 introduces the “Oracle Mode” So we can take PL/SQL from oracle and plug in MariaDB with a little work.
SECURE_DML Procedure
Oracle SECURE_DML
:
CREATE OR REPLACE EDITIONABLE PROCEDURE "HR"."SECURE_DML" IS BEGIN IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00' OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN RAISE_APPLICATION_ERROR (-20205, 'You may only make changes during normal office hours'); END IF; END secure_dml; /
MySQL SECURE_DML
:
SET sql_mode=ORACLE; DROP PROCEDURE SECURE_DML; DELIMITER $$ CREATE PROCEDURE SECURE_DML IS BEGIN IF TIME_FORMAT(SYSDATE(), '%H:%i') NOT BETWEEN '08:00' AND '18:00' OR DATE_FORMAT (SYSDATE(), '%w') NOT BETWEEN 1 AND 6 THEN signal sqlstate '20205' SET message_text = 'You may only make changes during normal office hours!'; END IF; END SECURE_DML; $$ DELIMITER ;
Test:
CALL SECURE_DML ;
Maybe you want test it out of the secure time window:
DROP PROCEDURE SECURE_DML; DELIMITER $$ CREATE PROCEDURE SECURE_DML IS BEGIN IF TIME_FORMAT(SYSDATE(), '%H:%i') NOT BETWEEN '11:00' AND '11:01' OR DATE_FORMAT (SYSDATE(), '%w') NOT BETWEEN 0 AND 1 THEN signal sqlstate '20205' SET message_text = 'You may only make changes during normal office hours!'; END IF; END SECURE_DML; $$ DELIMITER ;
ADD_JOB_HISTORY Procedure
Oracle:
CREATE OR REPLACE EDITIONABLE PROCEDURE "HR"."ADD_JOB_HISTORY" ( p_emp_id job_history.employee_id%TYPE , p_start_date job_history.start_date%TYPE , p_end_date job_history.end_date%TYPE , p_job_id job_history.job_id%TYPE , p_department_id job_history.department_id%TYPE ) IS BEGIN INSERT INTO job_history (employee_id, start_date, end_date, job_id, department_id) VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id); END add_job_history; /
MySQL:
SET sql_mode=ORACLE; DROP PROCEDURE ADD_JOB_HISTORY; DELIMITER $$ CREATE PROCEDURE ADD_JOB_HISTORY ( p_emp_id JOB_HISTORY.EMPLOYEE_ID%TYPE , p_start_date JOB_HISTORY.START_DATE%TYPE , p_end_date JOB_HISTORY.END_DATE%TYPE , p_job_id JOB_HISTORY.JOB_ID%TYPE , p_department_id JOB_HISTORY.DEPARTMENT_ID%TYPE ) AS BEGIN INSERT INTO JOB_HISTORY (EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID) VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id); END ADD_JOB_HISTORY; $$ DELIMITER ;
Test:
SELECT * FROM JOB_HISTORY ; -- CALL ADD_JOB_HISTORY(666, '1997-09-21 00:00:00.000000', '1998-09-21 00:00:00.000000', 'DBA', 99) ; -- SELECT * FROM JOB_HISTORY ;