As a DBA, most of my job involves recurring execution of the same tasks, like patching, upgrading, setting up new databases etc. Over a period of time, I have automated almost all of these tasks using shell scripts.
But I wanted to do one better. I thought that it would have been great if I can have a UI to display all the database details, along with the ability to run all DBA tasks from within the UI itself.
For example, it would be much better if I can have a single dashboard where I can see all the databases and their patch versions, along with a button to patch each database to any available patch version.
Being a DBA who is been working on Oracle technologies, my first choice of technology to use for this project is Oracle APEX.
BTW, here is a handy little guide to install Oracle APEX and ORDS.
It is important to note that before I started to work on this project, I had a NFS share which is made available in all DB servers, where I kept all those shell scripts. Whenever I needed to execute any scripts, I just executed the script from within that particular DB server.
I needed to make a couple of changes to make this work with an APEX UI
- I needed to have to initiate all the scripts from within one single server (preferably where the APEX DB is hosted). This script can in-turn call the individual scripts from within the server, but there should be password-less login available for the APEX DB server to all other database servers
- I need to configure this wrapper script from the APEX server to have all the parameters available during initiation itself, since those scripts cannot be executed in an interactive way. Along with that I need to make validations to make sure that each step is completed before starting the next step
- Finally there should be a monitoring mechanism, where I can monitor the current progress of each task from within the APEX database. I created some log tables which each script would update/insert into upon completion of each step
Once this is done and I could to DBA tasks in all DB servers from a single server and monitor the progress using database log tables, the next step is to make a java object in the APEX DB within the APEX application schema, to run the OS command from within the DB. The code for the JAVA object is something like below:
import java.io.*;
public class Host {
public static void executeCommand(String command) {
try {
String[] finalCommand;
if (isWindows()) {
finalCommand = new String[4];
// Use the appropriate path for your windows version.
//finalCommand[0] = "C:\\winnt\\system32\\cmd.exe"; // Windows NT/2000
finalCommand[0] = "C:\\windows\\system32\\cmd.exe"; // Windows XP/2003
//finalCommand[0] = "C:\\windows\\syswow64\\cmd.exe"; // Windows 64-bit
finalCommand[1] = "/y";
finalCommand[2] = "/c";
finalCommand[3] = command;
}
else {
finalCommand = new String[3];
finalCommand[0] = "/bin/sh";
finalCommand[1] = "-c";
finalCommand[2] = command;
}
final Process pr = Runtime.getRuntime().exec(finalCommand);
pr.waitFor();
new Thread(new Runnable(){
public void run() {
BufferedReader br_in = null;
try {
br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
String buff = null;
while ((buff = br_in.readLine()) != null) {
System.out.println("Process out :" + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_in.close();
}
catch (IOException ioe) {
System.out.println("Exception caught printing process output.");
ioe.printStackTrace();
}
finally {
try {
br_in.close();
} catch (Exception ex) {}
}
}
}).start();
new Thread(new Runnable(){
public void run() {
BufferedReader br_err = null;
try {
br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
String buff = null;
while ((buff = br_err.readLine()) != null) {
System.out.println("Process err :" + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_err.close();
}
catch (IOException ioe) {
System.out.println("Exception caught printing process error.");
ioe.printStackTrace();
}
finally {
try {
br_err.close();
} catch (Exception ex) {}
}
}
}).start();
}
catch (Exception ex) {
System.out.println(ex.getLocalizedMessage());
}
}
public static boolean isWindows() {
if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1)
return true;
else
return false;
}
};
Next, create a procedure to wrap this JAVA code so that we can call it from scheduler job
create or replace PROCEDURE host_command (p_command IN VARCHAR2) AS LANGUAGE JAVA NAME 'Host.executeCommand (java.lang.String)';
Now that we have all the supporting objects, let's create the necessary objects to create a database. First, we create another procedure specific to create database, and pass the create database shells script to the host_command procedure created earlier
CREATE OR REPLACE PROCEDURE createdbshell_prc (
pin_servername IN varchar2,
pin_dbname IN varchar2,
pin_dbpatchstring IN varchar2,
pin_dbdesc IN varchar2,
pin_dbsga IN number ) is
lv_dbhome varchar2(1000);
lv_dbversion number(10);
lv_dbhomeid number(10);
lv_serverid number(10);
begin
-- Find dbversion and dbhome from dbpatch
select dbh.dbhomepath, dbv.dbversion
into lv_dbhome,lv_dbversion
from dbpatchversion dbp, dbhome dbh, dbversion dbv
where dbp.dbpatchid = dbh.dbpatchid
and dbp.dbversionid = dbv.dbversionid
and dbp.dbpatchstring = pin_dbpatchstring;
host_command (p_command => '. /etc/profile ;. /home/oracle/.bash_profile;export ORAENV_ASK="NO";export ORACLE_SID=dbadm1;. /home/oracle/bin/oraenv ;
ssh -o StrictHostKeyChecking=no \
oracle@'||pin_servername||' <<EOF
sh /home/oracle/vm_scripts/dbadm/dbadm_create_database.sh '||lv_dbversion||' '||lv_dbhome||' '||pin_dbname||' '||pin_dbsga||'
EOF');
-- Do all the post-create steps here
end createdbshell_prc;
/
Next we will add the code to create the SCHEDULER JOB from APEX . This code has to be added to the process which is to be associated with the button to start the DB creation job
declare
lv_dbcount number(18,0) := NULL;
begin
select count(*)
into lv_dbcount
from db where lower(dbname) = lower(:P10_DBNAME);
IF lv_dbcount = 0
THEN
:P10_DBADMJOBNAME := 'createdb_'||:P10_DBNAME ;
dbms_scheduler.create_job
(
job_name => :P10_DBADMJOBNAME,
job_type => 'PLSQL_BLOCK',
job_action => 'begin createdbshell_prc ('''||:P10_SERVERNAME||''','''||:P10_DBNAME||''','''||:P10_PATCHSTRING||''','''||:P10_DESC||''','||:P10_DBSGA||'); end;',
start_date => sysdate,
enabled => TRUE,
auto_drop => TRUE,
comments => 'Create job for database '||:P10_DBNAME);
ELSE
raise_application_error(-20911,'Database with the provided name already exists');
end if;
end;
This should enable you to run OS scripts from the APEX UI. Please note that we need to set each and every env variables at OS before running each script.
In newer version of DBMS_SCHEDULER there is a way you can run OS shell scripts without creating any additional objects. All you have to do is create a credential object, and use it to create a scheduler job
begin
dbms_credential.create_credential(
credential_name => 'apex_jobs_cred',
username => 'apex',
password => 'password');
end;
/
begin
dbms_scheduler.create_job(
job_name => 'createdb_job',
job_type => 'EXECUTABLE',
job_action => '/home/oracle/bin/createdb.sh',
credential_name => 'apex_jobs_cred',
auto_drop => false,
enabled => true);
end;
/
begin
dbms_scheduler.run_job( 'createdb_job');
end;
/
