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 killtbuffer.py
:
:
<code bash>
cd ~/killtbuffer/
bash getall_metadata.sh nosync HR
</code>
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
getallmetadata.sh:
<code bash>
cd ~/getallmetadata
bash getallmetadata.sh nosync HR
</code>
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
SECUREDML
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 SECUREDML'':
<code sql>
SET sqlmode=ORACLE;
DROP PROCEDURE SECUREDML;
DELIMITER $$
CREATE PROCEDURE SECUREDML
IS
BEGIN
IF TIMEFORMAT(SYSDATE(), '%H:%i') NOT BETWEEN '08:00' AND '18:00'
OR DATEFORMAT (SYSDATE(), '%w') NOT BETWEEN 1 AND 6 THEN
signal sqlstate '20205' set messagetext = 'You may only make changes during normal office hours!';
END IF;
END SECUREDML;
$$
DELIMITER ;
</code>
Test:
<code sql>
CALL SECURE_DML ;
</code>
Maybe you want test it out of the secure time window:
<code sql>
DROP PROCEDURE SECUREDML;
DELIMITER $$
CREATE PROCEDURE SECUREDML
IS
BEGIN
IF TIMEFORMAT(SYSDATE(), '%H:%i') NOT BETWEEN '11:00' AND '11:01'
OR DATEFORMAT (SYSDATE(), '%w') NOT BETWEEN 0 AND 1 THEN
signal sqlstate '20205' set messagetext = 'You may only make changes during normal office hours!';
END IF;
END SECUREDML;
$$
DELIMITER ;
</code>
=== ADDJOBHISTORY Procedure ===
Oracle:
<code sql>
CREATE OR REPLACE EDITIONABLE PROCEDURE “HR”.“ADDJOBHISTORY”
( pempid jobhistory.employeeid%type
, pstartdate jobhistory.startdate%type
, penddate jobhistory.enddate%type
, pjobid jobhistory.jobid%type
, pdepartmentid jobhistory.departmentid%type
)
IS
BEGIN
INSERT INTO jobhistory (employeeid, startdate, enddate,
jobid, departmentid)
VALUES(pempid, pstartdate, penddate, pjobid, pdepartmentid);
END addjobhistory;
/
</code>
MySQL:
<code sql>
SET sqlmode=ORACLE;
DROP PROCEDURE ADDJOBHISTORY;
DELIMITER $$
CREATE PROCEDURE ADDJOBHISTORY (
pempid JOBHISTORY.EMPLOYEEID%TYPE ,
pstartdate JOBHISTORY.STARTDATE%TYPE ,
penddate JOBHISTORY.ENDDATE%TYPE ,
pjobid JOBHISTORY.JOBID%TYPE ,
pdepartmentid JOBHISTORY.DEPARTMENTID%TYPE )
AS
BEGIN
INSERT INTO JOBHISTORY (EMPLOYEEID, STARTDATE, ENDDATE, JOBID, DEPARTMENTID)
VALUES(pempid, pstartdate, penddate, pjobid, pdepartmentid);
END ADDJOBHISTORY;
$$
DELIMITER ;
</code>
Test:
<code sql>
select * from JOBHISTORY ;
–
CALL ADDJOBHISTORY(666, '1997-09-21 00:00:00.000000', '1998-09-21 00:00:00.000000', 'DBA', 99) ;
–
select * from JOB_HISTORY ;
</code>