Plot the backup duration for RMAN jobs using R, ggplot2, rJava, RJDBC, and the Oracle JDBC thin client.

plot_rman_backup_duration.R

# Load rJava library
Sys.setenv(JAVA_HOME='C:\\Program Files (x86)\\Java\\jre7\\')
options(java.parameters="-Xmx1g")
library(rJava)

# Output Java version
.jinit()
print(.jcall("java/lang/System", "S", "getProperty", "java.version"))

# Load RJDBC library
library(RJDBC)

# Create connection driver and open connection
jdbcDriver <- JDBC(driverClass="oracle.jdbc.OracleDriver", classPath="lib/ojdbc6.jar")
dbHostname <- 'database.company.com'
dbPort <- 1521
dbService <- 'service.database.company.com'
dbUser <- 'scott'
dbPassword <- 'tiger'
jdbcConnection <- dbConnect(jdbcDriver, sprintf('jdbc:oracle:thin:@//%s:%d/%s', dbHostname, dbPort, dbService), dbUser, dbPassword)

# Query on the Oracle instance name.
backupJobDetails <- dbGetQuery(jdbcConnection, "SELECT start_time, end_time, input_type, input_bytes_per_sec, output_bytes_per_sec, output_bytes FROM v$rman_backup_job_details ORDER BY start_time ASC")
backupJobDetails$OUTPUT_BYTES_PER_SEC[which(is.na(backupJobDetails$OUTPUT_BYTES))] <- 0
backupJobDetails$INPUT_BYTES_PER_SEC[which(is.na(backupJobDetails$INPUT_BYTES))] <- 0
backupJobDetails$START_TIME <- as.POSIXct(backupJobDetails$START_TIME, tz="UTC")
backupJobDetails$END_TIME <- as.POSIXct(backupJobDetails$END_TIME, tz="UTC")

# Close connection
dbDisconnect(jdbcConnection)

# Now plot the details.
library(ggplot2)
library(scales)
ggplot(backupJobDetails) + 
  geom_point(aes(x=START_TIME, y=as.numeric(END_TIME - START_TIME)/60./60., color=factor(INPUT_TYPE))) +
  scale_x_datetime("Start Time", breaks = date_breaks("5 days")) +
  scale_y_continuous("Backup duration (hours)") + 
  ggtitle('Oracle RMAN backup duration')