private List getSegmentServices(Statement stmt, String instance) throws SQLException {
List rtn = new ArrayList();
ResultSet rs = null;
try {
// Discover tables
rs = stmt.executeQuery(SEGMENT_QUERY);
int segment_col = rs.findColumn("SEGMENT_NAME");
int ts_col = rs.findColumn("TABLESPACE_NAME");
while (rs.next()) {
String segment = rs.getString(segment_col);
String tablespace = rs.getString(ts_col);
ServiceResource service = new ServiceResource();
service.setType(this, SEGMENT);
service.setServiceName(segment);
service.setDescription("Segment in the " + instance + " database instance");
ConfigResponse productConfig = new ConfigResponse();
ConfigResponse metricConfig = new ConfigResponse();
productConfig.setValue(OracleMeasurementPlugin.PROP_SEGMENT, segment);
productConfig.setValue(OracleMeasurementPlugin.PROP_TABLESPACE, tablespace);
service.setProductConfig(productConfig);
service.setMeasurementConfig(metricConfig);
service.setControlConfig();
rtn.add(service);
}
} finally {
DBUtil.closeResultSet(log, rs);
}
return rtn;
}
boolean targetDbIsValid(String targetDB) throws SQLException {
Connection c = getConnection();
if (targetDB != null && targetDB.trim().length() != 0) {
targetDB = targetDB.toLowerCase();
if (targetDB.equals("oracle")) {
if (!DBUtil.isOracle(c)) {
log("target was oracle, but this is not oracle, returning.");
return false;
} else {
log("target is oracle.");
return true;
}
} else if (-1 != targetDB.indexOf("postgres")) {
if (!DBUtil.isPostgreSQL(c)) {
log("target was postgresql, but this is not pgsql, returning.");
return false;
} else {
log("target is postgres.");
return true;
}
} else if (targetDB.equals("mysql")) {
if (!DBUtil.isMySQL(c)) {
log("target was mysql, but this is not mysql, returning.");
return false;
} else {
log("target is mysql.");
return true;
}
}
}
log("NOTE: No DB target was specified, allowing task to proceed.");
return true;
}
private List getTablespaceServices(Statement stmt, String instance) throws SQLException {
List rtn = new ArrayList();
ResultSet rs = null;
try {
// Discover tablespaces
rs = stmt.executeQuery(TABLESPACE_QUERY);
int ts_col = rs.findColumn("TABLESPACE_NAME");
while (rs.next()) {
String tablespace = rs.getString(ts_col);
ServiceResource service = new ServiceResource();
service.setType(this, TABLESPACE);
service.setServiceName(tablespace);
service.setDescription("Tablespace on the " + instance + " database instance");
ConfigResponse productConfig = new ConfigResponse();
ConfigResponse metricConfig = new ConfigResponse();
productConfig.setValue(OracleMeasurementPlugin.PROP_TABLESPACE, tablespace);
service.setProductConfig(productConfig);
service.setMeasurementConfig(metricConfig);
ConfigResponse svcProps = new ConfigResponse();
// 9i and 10g only
if (!getTypeInfo().getVersion().equals(VERSION_8i)) {
svcProps.setValue("block_size", rs.getString("BLOCK_SIZE"));
svcProps.setValue("allocation_type", rs.getString("ALLOCATION_TYPE"));
svcProps.setValue("space_management", rs.getString("SEGMENT_SPACE_MANAGEMENT"));
}
svcProps.setValue("contents", rs.getString("CONTENTS"));
svcProps.setValue("logging", rs.getString("LOGGING"));
service.setCustomProperties(svcProps);
rtn.add(service);
}
} finally {
DBUtil.closeResultSet(log, rs);
}
return rtn;
}
public void execute() throws BuildException {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = getConnection();
stmt = conn.createStatement();
MeasRangeObj ranges = MeasRangeObj.getInstance();
log(SCHEMA_MOD_IN_PROGRESS);
for (Iterator i = ranges.getRanges().iterator(); i.hasNext(); ) {
MeasRange range = (MeasRange) i.next();
long max = range.getMaxTimestamp(), min = range.getMinTimestamp();
String table = range.getTable();
String sql =
"INSERT into "
+ table
+ " (measurement_id, timestamp, value)"
+ " SELECT measurement_id, timestamp, value"
+ " FROM "
+ TAB_COMPAT
+ " WHERE timestamp BETWEEN "
+ min
+ " AND "
+ max;
int rows = stmt.executeUpdate(sql);
log(
"Moved "
+ rows
+ " rows from "
+ TAB_COMPAT
+ " to "
+ table
+ " min -> "
+ TimeUtil.toString(min)
+ " ("
+ min
+ ")"
+ " max -> "
+ TimeUtil.toString(max)
+ " ("
+ max
+ ")");
}
stmt.execute("TRUNCATE TABLE " + TAB_COMPAT);
} catch (SQLException e) {
throw new BuildException(logCtx + ": " + e.getMessage(), e);
} finally {
DBUtil.closeJDBCObjects(logCtx, null, stmt, rs);
}
}
private void setCustomProps(Statement stmt) throws SQLException {
ResultSet rs = null;
try {
// Query for server inventory properties
ConfigResponse props = new ConfigResponse();
rs = stmt.executeQuery(VERSION_QUERY);
if (rs != null && rs.next()) {
String version = rs.getString(1);
props.setValue("version", version);
}
setCustomProperties(props);
} finally {
DBUtil.closeResultSet(log, rs);
}
}
private List getUserServices(Statement stmt, String instance) throws SQLException {
// Discover the user instances, for user instances to be
// discovered, the user must be connected to the database.
List rtn = new ArrayList();
ResultSet rs = null;
try {
// Set server description
setDescription("Oracle " + instance + " database instance");
// Discover user instances
ArrayList users = new ArrayList();
rs = stmt.executeQuery(USER_QUERY);
while (rs.next()) {
String username = rs.getString(1);
users.add(username);
}
rs.close();
for (int i = 0; i < users.size(); i++) {
String username = (String) users.get(i);
ServiceResource service = new ServiceResource();
service.setType(this, USER_INSTANCE);
service.setServiceName(username);
service.setDescription("User of the " + instance + " database instance");
ConfigResponse productConfig = new ConfigResponse();
ConfigResponse metricConfig = new ConfigResponse();
productConfig.setValue(OracleMeasurementPlugin.PROP_USERNAME, username);
service.setProductConfig(productConfig);
service.setMeasurementConfig(metricConfig);
// Query for service inventory properties
rs = stmt.executeQuery(DBA_USER_QUERY + "'" + username + "'");
if (rs != null && rs.next()) {
ConfigResponse svcProps = new ConfigResponse();
svcProps.setValue("status", rs.getString("ACCOUNT_STATUS"));
svcProps.setValue("default_tablespace", rs.getString("DEFAULT_TABLESPACE"));
svcProps.setValue("temp_tablespace", rs.getString("TEMPORARY_TABLESPACE"));
service.setCustomProperties(svcProps);
}
rtn.add(service);
}
} finally {
DBUtil.closeResultSet(log, rs);
}
return rtn;
}
// Discover Oracle services
@Override
protected List discoverServices(ConfigResponse config) throws PluginException {
// HHQ-3577 allow listener names in tnsnames.ora to be used in the url
String tnsDir =
getTnsNamesDir(
config.getValue(ProductPlugin.PROP_INSTALLPATH), config.getValue(PROP_TNSNAMES));
if (log.isDebugEnabled()) log.debug("using tns dir as " + tnsDir);
System.setProperty("oracle.net.tns_admin", tnsDir);
String url = config.getValue(OracleMeasurementPlugin.PROP_URL);
if (url == null) {
log.warn(
"No value for config property "
+ OracleMeasurementPlugin.PROP_URL
+ ", no services will be discovered.");
return null;
}
String user = config.getValue(OracleMeasurementPlugin.PROP_USER);
if (user == null) {
log.info("No value for config property " + OracleMeasurementPlugin.PROP_USER);
}
String pass = config.getValue(OracleMeasurementPlugin.PROP_PASSWORD);
if (pass == null) {
log.info("No value for config property " + OracleMeasurementPlugin.PROP_PASSWORD);
}
ArrayList services = new ArrayList();
Connection conn = null;
Statement stmt = null;
try {
String instance = url.substring(url.lastIndexOf(':') + 1);
conn = DriverManager.getConnection(url, user, pass);
stmt = conn.createStatement();
services.addAll(getUserServices(stmt, instance));
services.addAll(getTablespaceServices(stmt, instance));
// turning this off by default.
// There are too many table that this will discover
// most of which the user probably won't care about.
// Also work needs to be done by the user to enable
// scheduled control actions to do an Anaylze per table
// so that the system info gets updated before the
// size is calc'd.
// services.addAll(getSegmentServices(stmt, instance));
services.addAll(getProcessServices(config));
// this requires extra config on the user side to set ORACLE_HOME
// env var, so to avoid confusion disabling
// services.addAll(getTnsServices(config));
setCustomProps(stmt);
} catch (SQLException e) {
// Try to do some investigation of what went wrong
if (e.getMessage().indexOf("table or view does not exist") != -1) {
log.error(
"System table does not exist, make sure that "
+ " the Oracle user specified has the correct "
+ " privileges. See the HQ server configuration "
+ " page for more information");
return services;
}
// Otherwise, dump the error.
throw new PluginException("Error querying for Oracle " + "services: " + e.getMessage(), e);
} finally {
DBUtil.closeJDBCObjects(log, conn, stmt, null);
}
return services;
}
