====== 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 "[[https://en.wikipedia.org/wiki/Track_Down|takedown]]" Oracle Database from any organization that wants to be "[[https://en.wikipedia.org/wiki/Free_as_in_Freedom|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 [[https://wiki.ciberterminal.net/doku.php?id=mysql:oracle_2_mysql_data_transfer|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 [[https://min.io/|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 "[[https://mariadb.com/kb/en/sql_modeoracle-from-mariadb-103/|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 ;