J2EE and Oracle Performance Musings

Review Of IBM WebSphere eXtreme Scale 6 by Packt Publishing

2009-12-20T12:49:00.000-08:00

This is a quick few notes on a new book I've started reading through, as per the title of this blog post. The general prognosis is that this is a very respectable piece of material, specifically I like:-

The fact that the book has been reviewed by the chief technical architect behind WebSphere eXtreme scale, Billy Newport, this would suggest that technical content is pretty water tight.
The author has chosen to write the book in such a way that it is highly accessible to its audience. By this I mean that Anthony Chaves explains WebSphere eXtreme scale from first principles and provides coding examples throughout the book which do not mandate the use of the WebSphere application server.
The book contains chapters that represent all the main use case areas for the product culminating in a final "Putting it all together" chapter.

All in all this is a very good book, and I would think that anyone wishing to cover the same subject material would struggle to write something that is more comprehensive than this.

JEE Batch Process Design Part 1

2009-11-30T12:19:00.000-08:00

A project I worked on recently used J2EE to implement batch processes. The architecture consisted of a WebSphere application server (version 6.1), Oracle database and a Java client to invoke the batch processes. On initiation, the client retrieves a list of jobs from the database via the application server. The list of jobs was then partitioned into equal sized 'Chunks', calls to a batch control session bean were invoked by threads fired off from the Java client, a chunk of jobs is attached to each thread. On receiving the jobs, the batch control bean invokes other beans to carry out the business logic of the application for each job within the chunk via a loop. In fact this architecture mirrored a batch process solution that can be found devx, to the extent I suspect that this is where it originated from in the first place.

Under this architecture scale out is achieved via IIOP load balancing, whereby the WebSphere workload manager distributes calls made to the batch control bean amongst the nodes in the cluster, or cell to use WebSphere parlance, in a round robin manner. If the CPU capacity of the servers that make up the cell are not uniform, a configurable load balance weighting can be used to redress this inbalance. Refer to this link from IBM for further details on load balance weighting. WebSphere extended deployment adds further intelligence into the process of distributing the workload via traffic shaping.

Whilst this design and architecture worked, it struck me that there were several ways in which this could be improved on:-

The client should be as light weight as possible, it should invoke the batch process and report on whether it suceeded or failed. Thus leveraging the qualities of the service the application server provides as much as possible. Specifically, the WebSphere launch client did not in itself provide the same qualities of service as the application server itself, namely workload management, fail over transaction support etc.
The application should only use transaction context within the application server.
The client prohibits end user application to Java.
Using RMI requires the application server RMI boot strap and RMI registry ports to be open.
A core design pattern should implement the business logic, such that a uniform design principle for followed for all batch jobs.
Threading should be handled within the application server.
Inversion of control should be used to decouple the business logic from the batch 'Framework'.
Although the software used JDBC batching, much better performance and scalability could be realised using oracle.sql.array for array processing.
Raw JDBC was used for persistance, a better design practise would be to externalise all SQL in the application and configuration.
For scale out, each node would acquire its configuration, storing the configuration in an LDAP server, a database, JDNI store etc would be much more cluster friendly.
An XML cache was used to store fixed domain configuration data, whilst this was more efficient than making frequent trips out to the database, a more light weight solution would have been preferable. When in full flight, 20% of each batch processes CPU usage was down to XPath calls associated with this cache.

If you are a sizeable WebSphere shop for which batch processes form a significant part of your IT infrastructure back bone, there is a compelling argument for looking at WebSphere Compute grid. If you are already a major Spring user, Spring batch may worth looking at. However, I'm not going to address the debate of which option is the best fit, suffice it to say both are options available for consideration. Whilst writing this posting, I couldn't help but feel that WebSphere Compute Grid answered a lot of the difficult questions that batch process design asked out of the box.

The salient features of my design are:-

Batch invocation via synchronous web service calls.
The web service would use JMS, by placing messages on a JMS queue, message driven beans on each node instigate the batch processing, this is the primary mechanism by which scale out is achieved.
In addition to the message driven beans facilitating scale out, they provide a means by which the batch process execution is detached from the client, therefore, anything adverse that happens to the client does not affect the batch, this was a conscious design decision.
Once the message driven had received a message to initiate the processing of the batch, high throughput on each node would be achieved through calls to methods annotated with the JEE6 @asynchronous annotation. I considered this to be the most light weight option available for this purpose, more light weight than a combination of using JMS and message driven beans.
I was considering using Spring, but as Glassfish 3 is fully JEE 6 compliant, and JEE 6 has virtually closed the gap with Spring in terms of Inversion of Control capabilities. I therefore elected to forego Spring in favour of using JEE 6. Also, at the time of writing this posting , Glassfish 3 was the only JEE 6 compliant application server available.
Implementation of the business logic using the strategy pattern.
Use of the command pattern in order to determine which strategy is required to implement the strategy.
Solidity and maturity of a JEE application for transaction management via JTA.
The use of JEE6 singleton beans annotated for initialisation on application startup for caching fixed domain data.
iBatis and oracle array processing using the oracle.sql.array class. To achieve raw performance, many people may consider a caching grid style solution at first. However, conventional caching wisdom does not apply to true batch processing, in that the same data item is rarely used more than once during the processing of a batch. Therefore any type of cache, regardless of where it is used in the technology stack will be of limited benefit. If any benefit at all is derived from caching, it will be through write behind and the caching of fixed domain data. iBatis is a "Half way house" solution, in that it is not a full blown object relational mapping framework nor is it as free form as using raw JDBC.

Firstly the strategy pattern. I will not steal anyone's thunder on this, as both Snehal Antani of IBM and a colleague and the Java technical architect of the team in which I work have both proposed this. Below is the essence of the strategy pattern in UML class form. By the way, if anyone want a free UML modelling tool, I can strongly recommend Eclipse Galileo with the modelling bundle, which I used to produce this. Double click on the image to obtain a larger version:-Here is a UML class diagram to represent the command pattern:-

The software architecture consists of the following components:-

1. A web service client and service generated using Axis 2 and Eclipse.

2. On the application server, the skeleton for the web service will instigate the BatchControl session bean, the batch delegate. A stand alone java client could invoke this directly.

3. It will be assumed that only one batch process of a given type will run at the same time, therefore the polling port type only needs the batch process name and not a handle.

4. The batch execution strategy will comprise of three steps:-

Get workload step, this will obtain a partition ('Chunk') of jobs to be processed.
Process workload step, this will use asynchronous method calls to process the jobs obtained from the get workload step.
Output the processed results step.

6. Whilst the batch is running, the client will poll the batch control bean at a frequency determined by a configurable polling period. The polling operation will return the number of jobs processed and the total number of jobs that make up the workload. Another as yet specified web service will allow the last execution of a batch process to be reported on in terms the number of individual jobs that failed and were processed successfully within the batch.

The concept of processing jobs in 'Chunks' will be retained, the three steps of get workload, process workload and output results will be iterated around in 'Chunk' size partitions of the the workload until the total workload is processed.

This is illustrated in the UML sequence diagram below.

1. Execute batch process.

2. Place the name of the batch process on the execute batch topic of the batch message queue.

3. Execute the get workload element of the batch strategy.

4. Execute the process workload element of the batch strategy.

5. Execute the output results element of the batch strategy.

6. Poll the web service for batch completion.

7. Call the batch delegate to establish whether or not the batch process is still running.8. Return the number of jobs processed and to process to the web service skeleton.

8. Return the number of jobs processed and to process to the web service skeleton.

9. Return the number of jobs processed and to process to the web service stub.

I am yet to established how to track the progress of the batch processes, the problem being around the ability to store state globally across the cluster representing the total number of jobs that the batch is going to process and the total number of jobs processed. The obvious way to do this is to store this in the database, however, for performance reasons I would like to avoid making trips out to the database unless absolutely necessary. A nice solution would be if Glassfish supported something similar to WebSphere's DistributedMap, then as each node in the cluster completes a 'Chunk' of jobs, it will put to this to the hash map, from which the batch delegate (batch control bean) can work out how much of the workload has been processed.

The UML class diagram below illustrates the fleshed out design, click on it to enlarge.
In future postings I will elaborate on the architecture and design with the intention of providing a code skeleton for the prototype.

Eclipse Findbugs Plugin & Double Checked Locking

2009-11-13T12:12:00.000-08:00

This code sample below is for the creation of a singleton object:-


 public static SqlConfiguration getSqlConfig(String fileName) {
       if (sqlConfigSingleton == null) {
           synchronized(SqlConfiguration.class) {
               if (sqlConfigSingleton == null) {
                   try {
                       parseXmlConfigFile(fileName);
                   } catch (JDOMException e) {
                       log.severe(e.getMessage());
                   } catch (IOException e) {
                       log.severe(e.getMessage());
                   }
                   sqlConfigSingleton = new SqlConfiguration();
               }
           }
       }

There are many reasons as to why the code is wrong, the most obvious one being that creation of the SqlConfiguration object and write to sqlConfigSingleton can be performed out of order. But this is not what impressed me, what impressed me was the fact that findbugs picked this up:-

Bug: Possible doublecheck on com.sqlstress.test.SqlConfiguration.sqlConfigSingleton
Pattern id: DC_DOUBLECHECK, type: DC, category: MT_CORRECTNESS

This method may contain an instance of double-checked locking. This idiom is not correct according to the semantics of the Java memory model. For more information, see the web page http://www.cs.umd.edu/~pugh/java/memoryModel/DoubleCheckedLocking.html.

Impressive right down to the fact a link is proivded to an artcile that discusses this in detail.

XML Based Database Stress Test Tool

2009-10-11T14:08:00.000-07:00

If you refer to this link, you will see an xml file containing example configuration information for a little tool I have been writing in Java. The aim of this is to stress test databases by executing SQL statements concurrently using Java threads. The xml file may seem vaguely reminiscent of an iBatis xml SQL maps, however my tool's configuration document contains information specific to tuning. Connections to the database are provided by a connection factory class, they can be: via a thin or thick JDBC driver (with or without use of the Oracle database resident connection pool) or use connection pooling, facilitated by the Oracle Universal Connection Pool. The code is largely complete, however, before I put it into the public domain, I want it to be pristine in terms of good coding practise, design and documentation. One of my colleagues at work has reviewed it , and its not in bad shape, the only problem being that my class model for storing SQL objects is not very OO, at present as I use one SQL statement class containing logic to work out how the execute method should be performed, i.e.:-

A stored procedure, function or package call will require the use of JDBC callable statement.
A select statement will return a result set, where as a DML statement will not.

The xml file allows the user to specify whether dml statements are to be run using JDBC batching or whether oracle.sql.ARRAY is to be used to perform array processing. Also, in order t to simulate the effect of hard parsing, the code can force this by putting randomly generated numbers in query hint text, dependant on whether value binding is set to soft or hard.

Hence, I'm in the process of modifying the code to use a Java interface for the SQL Statement class, with separate implementation classes for callable statements, DML statements and select statements, these will be created by a SQL statement factory class depending on what the xml file contains. Once this work is complete, I will update my blog and publish the source code via google doc links.

"Larry Ellison - What The Hell Is Cloud Computing?"

2009-10-11T10:29:00.000-07:00

I found this amusing posting on YouTube in which Larry Ellison states that IT is the only industry that is more fashion driver than women's fashion retail, when canvassed for his views on "The cloud". Not only do we work in an industry more fashion driven than women's fashion retail, we work in an industry in which staple technologies such as the RDBMS and J2EE are pronounced as being dead on a regular basis. Recently there was an article on the theserverside.com proclaiming that programming was dead, needless to say it was treated with great derision. This thing about a particular technology being pronounced as dead reminds me of a quote from the late comedian George Burns:-

"Every morning I read the orbituary column. If I don't see my name there I go ahead and have breakfast"

In summary Larry, you are not an idiot.

IBM and Oracle Performance Wars

2009-10-11T10:12:00.000-07:00

I see from www.spec.org that Oracle have posted some impressive SPECjAppServer2004 results for the second quarter of 2009. To summarise, 28463.30 JOPS on 17 nodes, 136 cores and 68 chips, putting into the shade IBM's result of 22643.13 JOPS on 16 nodes, 128 cores and 32 chips, posted here. I'm sure that this has not escaped the attention of Andrew Spyker and his team at IBM.

On the same theme, IBM are rumoured to be bringing out a competitor to Exadata 2 called DB2 Pure Scale as reported by The Register. On some of The Register articles, IBMers have jumped on the comments thread to extol the virtues of DB2, and whilst I'm not for a second criticising it as a product, who actually uses it ?. I live and work in the Uk, if I go to www.jobswatch.co.uk and look for DB2 dba jobs, there have been seventeen job adverts for DB2 DBAs in the last three months, versus three hundred and sixty nine for Oracle DBAs, if this web site is to be believed that is.

A DBA Utilities Package Using Java Stored Procedures

2009-09-25T13:54:00.000-07:00

I wanted to blog on Java stored procedures, the problem being that all I could come up with was something really contrived such as the infamous ATM machine example that always gets trotted out in training course material. Fortunately I came up with the requirement for a general purpose DBA utilities package, the requirements of which included:-

A "Poor mans" data guard using data pump
The ability to rotate alert logs etc
The ability to produce data pump exports, compress them and maintain an archive, i.e. if you wanted seven days worth of dmp files, when you reached seventh file, as soon as the newest one is produced, the oldest one would be purged.

My main motivation for writing this was to develop a utility package that was portable across Windows and Unix, hence saving the effort in having to maintain multiple scripts.

The second two requirements is where Java stored procedures came in. Although a full desemination of Java stored procedures is beyond the scope of a simple blog posting, I'll bullet point some of more salient points:-

The ability to run Java within the database was introduced with Oracle 8i.
10g supports code compiled with the J2SE 1.4 JDK and 11g supports code compiled with the J2SE 1.5 JDK.
All Java methods to be wrapped by PL/SQL need to be declared as public (obviously !) and static.
The embedded JVM run time uses a different security model compared to the conventional JVM outside the database. Permissions are granted via certain database roles such as JAVASYSPRIV, more fine grained permissions can be granted using the DBMS_JAVA PL/SQL package.
Java in the database scales using sessions rather than threads.
SQL is run via an embedded JDBC driver.
The Oracle 11g JVM furnishes a JIT compiler, prior to this, Java had to be compiled ahead of time using NCOMP.
System classes are shared between sessions so as to reduce memory and general resource overheads.

Here is my Java class, much of the code comes courtesy of the Java almanac. I've omitted the use of featues such as annotations, the string builder class and the Java foreach loop, so as to make the code portable between 10 and 11g. Also, note that I've had to remove the original dump file after it has been compressed as the Oracle JVM does not remove the compressed file after the zip stream is closed.


import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Comparator;
import java.util.Date;
import java.util.zip.GZIPInputStream;
import java.util.zip.GZIPOutputStream;

public class FileUtils {
/**
*
* This is utility code to be used by the dbutilspkg PL/SQL package, it will purge
* and files from the specified by directory that have:-
*
* 1) Are suffixed by bak.zip
* 2) If there are more than NumFilesToKeep files in the directory with the bak.zip
*    extension, it will purge the oldest files, such that only NumFilesToKeep #
*    of files remain.
*
* @param  Directory      directory containing the compressed dump
*                        files to scan.
* @param  NumFilesToKeep number of compressed dump files to keep
*
*/
public static void PurgeDmpFiles(String Directory, long  NumFilesToKeep) throws Exception {
int DumpFiles = 0;
int StrLen;

     File files[] = new File(Directory).listFiles();

Arrays.sort( files, new Comparator()
 {
  public int compare(final Object o1, final Object o2) {
   return new Long(((File)o1).lastModified()).compareTo
    (new Long(((File) o2).lastModified()));
  }
 } );

for (int i = 0; i < files.length; i++) {
 StrLen = files[i].getName().length();

 if (StrLen > 7) {
  if ( files[i].getName().substring(StrLen - 7, StrLen).equals("bak.zip") ) {
   DumpFiles++;
      System.out.println("Found a gzipped dump file");
                 System.out.println("<<" + files[i].getName() + ">>");

   if ( DumpFiles > NumFilesToKeep ) {
          System.out.println("Deleting a dump file");
    files[i].delete();
   }
  }
 }
}
}
/**
*
* Java code to gzip a file based on code from the Java Almanac, note that because
* the JVM embedded in the Oracle RDBMS does not remove the original file after the
* zip stream has been closed, this has had to be coded for.
*
* @param  fileName       absolue path of file to zip
*
*/
public static void GzipFile(String fileName) {
try {
       // Create the GZIP output stream
       String outFilename = fileName + ".Z";
       GZIPOutputStream out = new GZIPOutputStream(new FileOutputStream(outFilename));

       // Open the input file
       FileInputStream in = new FileInputStream(fileName);

        // Transfer bytes from the input file to the GZIP output stream
       byte[] buf = new byte[1024];
       int len;
       while ((len = in.read(buf)) > 0) {
            out.write(buf, 0, len);
       }
       in.close();

       // Complete the GZIP file
       out.finish();
       out.close();

              File finalFile = new File(fileName + "bak.zip");
              File tempFile  = new File(fileName + ".Z");

              tempFile.renameTo(finalFile);

       boolean success = (new File(fileName).delete());
 if (!success) {
  System.err.println("Unable to delete uncompressed dmp file: " + fileName);
 }
} catch (IOException e) {
 System.err.println(e.getMessage());
   }
}
/**
*
* Java code to un-gzip a file.
*
* @param  fileName       absolue path of file to zip
*
*/
public static void UnGzipFile(String fileName) {
try {
       // Open the compressed file
       GZIPInputStream in = new GZIPInputStream(new FileInputStream(fileName));

       // Open the output file
       String outFilename = "outfile";
       OutputStream out = new FileOutputStream(outFilename);

       // Transfer bytes from the compressed file to the output file
       byte[] buf = new byte[1024];
       int len;

 while ((len = in.read(buf)) > 0) {
            out.write(buf, 0, len);
       }

       // Close the file and stream
       in.close();
       out.close();
   } catch (IOException e) {
  System.err.println(e.getMessage());
   }
}
/**
*
* Java code to rotate a file if its length exceeded the specified
* rotation size (bytes).
*
* @param  fileName       absolue path of file to zip
* @param  RotateSize     size threshhold for file rotation
*
*/
public static void RotateFile(String FileName, long RotateSize) {
   File file = new File(FileName);
   if ( file.exists() ) {
 if ( file.length() >= RotateSize ) {
         SimpleDateFormat dateformatMMDDYYYYHHmm = new SimpleDateFormat("MMddyyyyHHmm");
         StringBuffer nowMMDDYYYYHHmm = new
      StringBuffer( dateformatMMDDYYYYHHmm.format( new Date() ) );
         File rotatedFile = new File(FileName + "." + nowMMDDYYYYHHmm);
         // Rename file (or directory)
         file.renameTo(rotatedFile);
         System.out.println("File rotated");
 }
 else {
         System.out.println("File below rotation size");
 }
   }
   else
   {
       System.out.println("File not rotated");
   }
 }
}

Below is my PL/SQL package that uses the FileUtils class, if you read the comment banner at the top of package, instruction is provided for compiling and loading the Java class. You can create the source in SQLPLUS via the CREATE JAVA SOURCE command, however my preference is to always write the source in Eclipse, compile it and then load it into the database via loadjava. My apologies for anyone reading this using Google Chrome, for some reason the formatting of the PL/SQL goes awry, Firefox, however, has no such problem.


CREATE OR REPLACE PACKAGE BODY DbUtilsPkg AS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
/*==========================================================================*/                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    Name:        DbUtilsPkg (BODY)                                        */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    Version:     1.0                                                      */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    Description: This package includes useful DBA utilities, namely:-     */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                 1) A procedure, pump_backup_db, to data pump export      */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                    full databases, gzip the dmp files on successful      */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                    completion and then remove any obsolete dmp files     */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                    , depending on the number of dump file to retain      */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                    parameter value specified.                            */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                 2) Rotate log files once they have exceeded the          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                    specified file rotation size.                         */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    Special Notes                                                         */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    ~~~~~~~~~~~~~                                                         */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    The "DpCopySchema" procedure is intended to function as a poor mans   */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    data guard, as such the destination schemas objects are dropped. I    */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    have elected against dropping the schema, as this would require       */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    the password of the destination schema to be reset afterwards, for    */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    security purposes this package does not handle or manage any          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    passwords.                                                            */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    The source code for this, with the exception of the procedures to     */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    drop schema objects comes coutesy of Dani Rey from Trivadis AG,       */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    refer to: http://danirey.wordpress.com .                              */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    Dependencies                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    ~~~~~~~~~~~~                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    This package is dependant upon the FileUtils java class, the source   */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    code for this should be compiled using the version 5 JDK as follows   */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    :-                                                                    */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    javac FileUtils.java                                                  */
/*                                                                          */
/*    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~     */
/*    !!! NOTE !!! Use the J2SE 1.4 JDK javac for Oracle 10g                */
/*                 Use the J2SE 1.5 JDK javac for Oracle 11g                *                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
/*    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~     */
/*                                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    This will result in to java classes being produced: FileUtils$.class  */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    and FileUtils$1.class, these then need to be loaded into the          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    database as follows:-                                                 */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    loadjava r -f username/password FileUtils.class FileUtils$1.class     */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    Should you wish to debug the java, messages displayed via             */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    System.out.println will appear on the SQPLUS command line, if the     */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    Java is wrapped with PL/SQL  after DBMS_JAVA.SET_OUTPUT(<bufferSize>) */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    and set serveroutput on are issued/                                   */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    Note that the security model for executing Java within the database   */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    differs slightly from that used by PL/SQL, therefore, in order to be  */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    able to write to files, DBMS_JAVA must be used to grant the pre-      */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    requisite permissions to the relevant users:-                         */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    EXEC DBMS_JAVA.grant_permission('SCHEMA-NAME',                        */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                                   'java.io.FilePermission',              */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                                   '<<ALL FILES>>',                       */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                                   'read ,write, execute, delete');       */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    Grant JAVASYSPRIVS to the relevant user will grant blanket file CRUD  */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    permissions.                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    Additionally, the following object permissions are required, unless   */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    this package is run as SYS or SYSTEM or a user with he DBA role       */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
/*    privilege:-                                                           */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    1) SELECT on V$DATABASE                                               */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    2) EXECUTE on DBMS_DATAPUMP                                           */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    3) SELECT privilege on DBA_USERS                                      */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    4) execute privilege on DBMS_FLASHBACK                                */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    5) EXP_FULL_DATABASE sys privilege                                    */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*       Not to be confused with EXPORT FULL DATABASE.                      */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    6) CREATE TABLE privilege                                             */
/*    7) SELECT privilege on DBA_DIRECTORIES.                               */
/*    8) SELECT privilege on V$PARAMETER.                                   */
/*    9) For the schema copy utility:-                                      */
/*       i)   A database link connecting the destination to the source      */
/*            schema must exist.                                            */
/*       ii)  The destination schema must have IMP_FULL_DATABASE and        */
/*            EXP_FULL_DATABASE privileges.                                 */
/*       iii) The source schema must have IMP_FULL_DATABASE and             */
/*            EXP_FULL_DATABASE privileges.                                 */
/*                                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    History                                                               */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    ~~~~~~~                                                               */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    Author       Date        Change Description                           */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    ===========  ==========  =========================================    */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*    C. J. Adkin  21/09/2009  Initial version created                      */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*                                                                          */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
/*==========================================================================*/                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
  PROCEDURE PumpBackupDb (PumpDirectory    IN VARCHAR2,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
                          DmpFilesToRetain IN NUMBER DEFAULT 7) IS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
      DirectoryExists     NUMBER;               
      DpHandle            NUMBER;               
      DoP                 NUMBER;
      DbName              VARCHAR2(32);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
      JobState            VARCHAR2(32);
      DmpFileAbsolutePath VARCHAR2(100);
      DmpFileName         VARCHAR2(100);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  BEGIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
      DBMS_OUTPUT.ENABLE(1000000);   
      DBMS_OUTPUT.PUT_LINE('Start time : '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
      --
      BEGIN
          SELECT 1
          INTO   DirectoryExists
          FROM   dba_directories
          WHERE  directory_name = PumpDirectory;
    
      EXCEPTION
          WHEN NO_DATA_FOUND THEN
              DBMS_OUTPUT.PUT_LINE('Data pump directory '||PumpDirectory||' does not exist');
              RAISE;
      END;
      --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
      SELECT name                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
      INTO   DbName                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
      FROM   v$database;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
      --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
      DmpFileName := DbName||'_FULL_'||TO_CHAR(SYSDATE, 'DDMMYYYYHH24MI');                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
      --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
      DBMS_OUTPUT.PUT_LINE('Openning data pump job');                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
      --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
      DpHandle := DBMS_DATAPUMP.OPEN (operation => 'EXPORT' ,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                                      job_mode  => 'FULL'   );                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
      --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
      DBMS_OUTPUT.PUT_LINE('Adding data pump dmp file');                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
      --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
      DBMS_DATAPUMP.ADD_FILE (handle    => DpHandle ,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                              filename  => DmpFileName||'.dmp',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
                              directory => PumpDirectory);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
      DBMS_OUTPUT.PUT_LINE('Adding data pump log file');                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
      --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
      DBMS_DATAPUMP.ADD_FILE (handle    => DpHandle ,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                              filename  => DmpFileName||'.log',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
                              directory => PumpDirectory,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                              filetype  => DBMS_DATAPUMP.ku$_file_type_log_file);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
      --
      SELECT value
      INTO   DoP
      FROM   v$parameter
      WHERE  name = 'cpu_count';
      --
      IF DoP > 1 THEN
          DBMS_DATAPUMP.SET_PARALLEL(handle => DpHandle,
                                     degree => DoP);
      END IF;
      --
      DBMS_DATAPUMP.START_JOB(DpHandle);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
      --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
      DBMS_DATAPUMP.WAIT_FOR_JOB(DpHandle, JobState);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
      --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
      DBMS_DATAPUMP.DETACH(DpHandle);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
      --                                      
      SELECT directory_path
      INTO   DmpFileAbsolutePath
      FROM   dba_directories
      WHERE  directory_name = PumpDirectory;
      --
      DBMS_OUTPUT.PUT_LINE(DmpFileAbsolutePath);
      --
      GzipFile(DmpFileAbsolutePath||'\'||DmpFileName||'.dmp');                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
      --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
      PurgeDumpFiles(DmpFileAbsolutePath, DmpFilesToRetain);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
      --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
      DBMS_OUTPUT.PUT_LINE('End time :   '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));

      EXCEPTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
          WHEN OTHERS THEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
             --dbms_datapump.stop_job(DpHandle);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
             RAISE;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
  END;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
  -------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  PROCEDURE PumpCopySchema(sourceSchema      IN VARCHAR2,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                           destinationSchema IN VARCHAR2,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                           databaseLink      IN VARCHAR2,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                           debug             IN BOOLEAN := FALSE) IS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
      handle                  NUMBER;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
      message                 VARCHAR2(2000);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
      flashbackSCN            NUMBER;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
      logfileName             VARCHAR2(200);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      destinationSchemaExists NUMBER;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
      jobState                user_datapump_jobs.state%TYPE;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  BEGIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
      DBMS_OUTPUT.PUT_LINE('Start time : '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
      dbms_output.put_line('verify whether destination schema '||destinationSchema||' already exists');                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
      SELECT COUNT(username)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      INTO   destinationSchemaExists                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
      FROM   dba_users                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
      WHERE  username = destinationSchema                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
      AND    username NOT IN ('SYS','SYSTEM','DBSNMP','DIP','EXFSYS','OUTLN','TSMSYS');                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
      --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
      IF ( destinationSchemaExists = 0 ) THEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
          DBMS_OUTPUT.PUT_LINE ('---> !!! Source schema does not exist !!!');                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
      ELSE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          DBMS_OUTPUT.PUT_LINE ('open datapump job over database link '||databaseLink);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
          --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          handle := dbms_datapump.open (operation =>    'IMPORT',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                                        job_mode =>     'SCHEMA',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                                        remote_link =>  databaseLink);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
          --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          IF debug = TRUE THEN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
              DBMS_OUTPUT.PUT_LINE('set logfile parameter');                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
              logfileName := 'datapump_'||to_char(SYSDATE,'YYYYMMDD')||'.log';                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
               --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
              DBMS_DATAPUMP.ADD_FILE (handle    =>   handle,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
                                      filename  =>   logfileName,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                                      directory =>  'DATA_PUMP_DIR',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                      filetype  =>   dbms_datapump.ku$_file_type_log_file);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
          END IF;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
          --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          DBMS_OUTPUT.PUT_LINE('set FLASHBACK_SCN parameter');                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
          --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          DBMS_DATAPUMP.SET_PARAMETER (handle =>   handle,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                                       name   =>   'FLASHBACK_SCN',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                                       value  =>   dbms_flashback.get_system_change_number);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          DBMS_DATAPUMP.SET_PARAMETER (handle =>   handle,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                                       name   =>   'TABLE_EXISTS_ACTION',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                                       value  =>   'TRUNCATE' );                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          DBMS_OUTPUT.PUT_LINE ('define SCHEMA_LIST');
          --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
          DBMS_DATAPUMP.METADATA_FILTER (handle => handle,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                                         name   => 'SCHEMA_LIST',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                         value  => ''''||sourceSchema||'''');  
          --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
          DBMS_OUTPUT.PUT_LINE ('define schema remapping');              
          --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
          DBMS_DATAPUMP.METADATA_REMAP (handle    => handle,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
                                        name      => 'REMAP_SCHEMA',                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
                                        old_value => sourceSchema,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                                        value     => destinationSchema);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          --
          DBMS_OUTPUT.PUT_LINE('start datapump job');                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
          --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          DBMS_DATAPUMP.START_JOB (handle);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
          --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          DBMS_OUTPUT.PUT_LINE ('wait for job to finish');                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
          DBMS_DATAPUMP.WAIT_FOR_JOB (handle, jobState);
      END IF;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
      --                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
      DBMS_OUTPUT.PUT_LINE('End time :   '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
      --
      EXCEPTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
          WHEN OTHERS THEN              
              IF sqlcode = 31631 THEN
                  DBMS_OUTPUT.PUT_LINE ('Caught ORA-31631, ensure that both source and destination ' ||
                                        'schemas have IMP_FULL_DATABASE and EXP_FULL_DATABASE '      ||
                                        'privileges');
              ELSE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
                  DBMS_OUTPUT.PUT_LINE (SQLERRM);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
              END IF;
  END; 
  ----------------------------------------------------------------------------                                                                                                                                                              
  PROCEDURE PurgeDmpFiles (Directory      VARCHAR2,                                                                                                                                                                                        
                           NumFilesToKeep NUMBER)
  IS LANGUAGE JAVA
  NAME 'FileUtils.PurgeDmpFiles(java.language.String, long)';                                                                                                                                                                                         
  ----------------------------------------------------------------------------                                                                                                                                                              
  PROCEDURE GzipFile       (FileName   VARCHAR2)
  IS LANGUAGE JAVA
  NAME 'FileUtils.GzipFile(java.language.String)';                                                                                                                                                                                                                                                                                                                                                                                
  ----------------------------------------------------------------------------                                                                                                                                                              
  PROCEDURE RotateFile     (FileName   VARCHAR2,                                                                                                                                                                                            
                            RotateSize NUMBER)             
  IS LANGUAGE JAVA
  NAME 'FileUtils.RotateFile(java.language.String, long)';                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
END DbUtilsPkg;

Use cases for Java stored procudures include:-

The need to integrate the database with other systems and middle ware components which have APIs accessible from Java.
"A better PL/SQL", I mainly use PL/SQL as a wrapper for SQL in order to leverage the flow control constructs that PL/SQL gives you. PL/SQL does have object extensions, however, I find these 'clunky' compared to those in Java. Java also supports some basic language features that PL/SQL does not, such as enumeration.
Moving some of the business logic out of the middle tier and closer to the data in order to improve performance and scalability by reducing network round trips between the middle tier and the database.
A way to leverage skills of Java programmers within the database.
Accessing operating system commands from within the database.

There is a wealth of material available on OTN regarding Java stored procedures which goes significantly beyond the simple example presentation here, including sample code and case studies, for further reading refer to:-

SQL Server Clustered Tables versus Oracle Index Only Tables

2009-07-15T14:42:00.000-07:00

A SQL Server clustered table and an Oracle index only table are essentially the same thing, a table that instead of being heap organised, is stored in sorted order. In the case of Oracle a B*Tree is used and a doubly linked list for SQL Server. But here is the interesting bit, in Oracle due to the expense of secondary index lookups, the use of index organised tables is the excpetion rather than the rule, however if you refer to this presentation from Kimberley Tripp, in SQL Server the use of clustered indexes is encouraged a lot more than their counterparts in Oracle. I can only assume that this is because SQL Server does not have an equivalent to Oracle's table lookup by rowid. In short this took me a bit by surprise.

Sane San . . . Sane Virtualization ?

2009-07-14T14:29:00.000-07:00

James Morle published an article some time ago entitled "Sane SAN", it discussed the practicalities allocating database storage from a SAN. One of the points it touched on was the danger of being given an amount of storage without knowing how many physical spindles it was spread across. This was before virtualization took off, the problem being that there may be several layers of virtualization before you get down to the physical disks. For example, Hewlett Packard produce something called the Enterprise Virtual Array, this aggregates trays of storage into virtual arrays. With another product called SVM, which, off the top of my head stands for something like storage virtual manager, these virtual arrays can be pooled into virtual SANs.

On the subject of virtualization, the "Oracle Storage Guy" compares the price performance of Oracle RAC versus Oracle standard edition on clustered VMWare, with some caveats, clustered VMWare comes out the winner. This provides serious food for thought as to where it is best to carry out the clustering in the technology stack.

Oracle 11.1.0.7.0 and cell physical IO interconnect byt-es

2009-03-03T13:42:00.000-08:00

I've been playing around with 11.1.0.7.0 and noticed the system statistic "cell physical IO interconnect byt" in a statspack report. There are no references to this statistic on metalink whatsoever, however there is a reference on Kevin Closson's blog stating that this is an exadata related statistic, not that I dispute Kevin's information, but I'm not using exadata, this is something I'll try to bottom out with Oracle support.

SQL Server Cross Training For Oracle Professionals

2009-02-16T14:21:00.000-08:00

This is a presentation I prepared for some of my colleagues at NSB Retail Systems before we became part of British Telecom. The aim of this was to provide cross training into SQL Server for Oracle professionals. The information was correct as of SQL Server version 2000, since then SQL Server has evolved to incorporate the following changes (to name but four), however the core concepts are still very much the same:-

there have been numerous transact SQL enhancements
The internals have been exposed somewhat via dynamic management views
Query Analyzer, Enterprise Manager and OLAP Analysis manager have been consolidated into "Management studio".
There is a new database tuning advisor

Sql Server Training

View more presentations from chris1adkin. (tags: sql_server)

Oracle, DB Time and Performance Baselines

2009-02-16T04:07:00.001-08:00

I've been tasked at work with investigating performance baselines. Fortunately, as we use 10g and have paid for the diagnostics and tuning packs, base lines (or preserved snapshots) can easily be created with dbms_workload_repository.create_baseline. However, there are couple of things that would be really nice to have that are not available, namely:-

1. The ability for Enterprise Manager to generate notifications when the db time of a database deviates from that of a specified baseline by a specified threshold. This can be worked around via the custom metrics that the enterprise manager framework allows you to create. I'm a tad surprised that this functionality does not exist as db time is the cornerstone of the Oracle performance infrastructure from 10g onwards.

2. Consider the scenario where you have an existing production database and you want to test the impact of adding new functionality and / or adding an increased load to production database. Obviously you don't want to do this without some prior testing, fortunately you have a test environment with hardware that is identical to production. You test the changes on test and after some tuning you deem the changes satisfactory to go into production, also you have used dbms_workload_repository to create a baseline on test. At this stage it would be really nice to have the ability to export the baseline from the test environment and import it into production. This ability does exist but it is not documented, here is the solution as proposed by Oracle support, the speed at which Oracle support came back with suggests the solution is something that other people have asked for before. Here it is:-

Though AWR data can be exported and imported for transporting it into other databases
this is not supported by oracle and this is to be used only for troubleshooting purposes,

This is implemented with an internal package which is created with the script awrextr.sql
which is under $ORACLE_HOME/rdbms/admin

The steps to follow are

To download the data into a data pump dumpfile, you would use the procedure AWR_EXTRACT:

begin
DBMS_SWRF_INTERNAL.AWR_EXTRACT (
dmpfile => 'awr_data.dmp',
dmpdir => 'TMP_DIR',
bid => 302,
eid => 305);
end;

dmpfile => The name of the target file for the data is mentioned here. This is a Data Pump export
file. If non filename is given, the default value awrdat.dmp is used.

dmpdir => The directory object where the dumpfile is written. In this case, you may have defined
a directory TMP_DIR as /tmp.

bid => The snapshot ID of the beginning snapshot of the period.

eid => The end snapshot ID.

Once you dump the AWR data to the dumpfile awr_data.dmp to the new location and load it using another procedure in
the same package,

First we need to load the AWR data to a temporary staging area before loading into the SYS schema. In this case, the data
will be loaded to AWR_TEST schema initially.

begin
DBMS_SWRF_INTERNAL.AWR_LOAD (
SCHNAME => 'AWR_TEST',
dmpfile => 'awr_data',
dmpdir => 'TMP_DIR');
end;

After staging, the data is moved into the SYS schema:

begin
DBMS_SWRF_INTERNAL.MOVE_TO_AWR (SCHNAME => 'TEST');
end;

Once this is completed, the AWR data can be used for any performance analysis or you can build a central repository of
AWR data collected from multiple databases

The Death Of ESBs

2009-02-02T04:47:00.001-08:00

I've just watched "Does My Bus Look Big In This" on infoq, this infers that by adopting Enterprise Service Buses we are effectively brushing our spaghetti under the carpet by putting it all in "one box". It also makes a compelling case for "Guerilla SOA", i.e. picking the small winable integration battles, rather than a monolithic campaign of putting an ESB in and all of its associated middleware. I wonder if many of the big ESB and middleware players are cogniscent of the fact there might be a major sea change coming in the world of SOA.

Efficient Oracle Database Usage By Java and J2EE Applications

2009-01-24T13:33:00.001-08:00

In case you haven't guessed, I like producing presentations. In the next two weeks or so, I intend to produce a new presentation on the the "Efficient Use of Oracle By J2EE and Java Applications". To whet the appetite this will include:-

1. Connection management best practices:-

Connection pool usage
Setting the connection pool min and max settings to be the same to avoid connection storms
Oracle 11g server side connection pooling
Using the 11g to throttle connection rates

2. Efficient cursor management

The statement Vs preparedStatement API.
Avoiding hard parsing
Avoiding soft parsing
Parameters that affect parsing: session_cached_cursors,
cursor_space_for_time and cursor_sharing

3. Minimizing network round trips and leveraging bulk operations

The JDBC fetch size
Statement batching in JDBC
The Oracle array interface
Leveraging Oracle bulk operations
Intelligent Oracle statement feature usage, specifically usage of: merge
statements, in line views, in line PL/SQL functions, sub query factoring.
The client side result cache
Programming and design styles for minimizing application 'Chatter'
Caching

4. Design and coding according to the putting the processing close to resource that requires it, specifically:-

Do not be precious about Java or the database; it is not necessarily a good
thing to do everything in the database.
Do not re-invent database functionality in Java

5. Best practices for logger buffer usage

JDBC auto commit.
Batch and asynchronous commits as introduced in Oracle 10g.

Watch this space . . .

J2EE Batch Processing Design and Tuning presentation here !!!

2009-01-23T08:49:00.001-08:00

As promised here is my presentation on J2EE batch processing. There may be material on the Internet and blogosphere that covers some of the individual areas I touch on in greater detail. However as a piece of material that aggregates this in one place, I don't think its bad.

| Get your Presentation Pack

Presentation On J2EE Batch Processing and Tuning

2009-01-21T11:16:00.000-08:00

I have spent much of my time recently on a project focusing on the volume and performance testing of J2EE (Websphere app server) batch processes using a Oracle 10g database for persistence. I will shortly be posting a presentation which will cover some salient points that emerged from this work via slideshare.net.

I wrote the presentation for several reasons:-

1. To capture key lessons learned on the project from a technical point of view.

2. To capture information on batch process design considerations as there is not much information in the public domain.

3. To produce some "joined up" information on tuning J2EE software using Oracle, mainly because most J2EE material stops short of the database at the JDBC level and most database tuning material stops short of application server at JDBC but from the other direction. There is material "out there" but not in one place presented in a manner that covers the whole software stack.

In this day and age most databases are 'fronted' by an application server and we have long since moved on from all the business logic residing within the database and the database being the main focal point of integration. Very few applications are islands and the 'glue' used to join applications up is almost as important as the applications themselves, therefore a more all encompassing approach needs to be taken when tuning and performance monitoring the software stack.

These are the main points I will cover:-

1. Batch process architecture and design considerations.

2. The batch process design used on my project

3. Available monitoring and tuning tools for tuning J2EE architectures using Oracle.

4. Tuning findings

5. Conclusions

As everyone always likes to skip to the end of a book before reading the rest, I will touch on some of the conclusions from this work, namely:-

1. Why Martin Fowler's first law on distributed Object architectures is well founded.

2. Dangers of swamping a J2EE application server with too many threads.

3. Avoiding 'Chatty' designs.

4. Although row by row processing is a concern, it is not always the end of the world in terms of performance and only a genuine concern if it prevents performance and scalability targets from being reached.

The Basics That Undermine RAC Scalability And Availability

2009-01-15T11:21:00.000-08:00

I'm writing this post as one of the things I hope to get into in the near future is Oracle RAC. There are two reasons for adopting Oracle RAC:-

1. Scalability

Cluster inter-connect traffic and performance is a major factor here. As such Infiband is being touted as the up and coming inter-connect fabric. Also if you google Joel Goodman Oracle, you will most probably find references to presentations and papers on how uncached sequences without noorder specified can degrade performance via the pinging of blocks across the inter-connect. Other than this, the same factors that influence performance and scalability for a single instance application also apply to RAC. Specifically, these are the "usual suspects" that the Oracle Real World performance include in most of the material they present at Oracle Open World, namely:-

1. Good schema design
2. Efficient connection management
3. Efficient cursor management

Therefore, my first question is this, how many RAC sites test their applications in single instance environments for scalability around these three factors. Also, how many sites test the scalability of their clusters as they add nodes. Interestingly according to Julian Dyke’s book on 10g RAC and Linux, Oracle actually turn cache fusion off in their RAC TCP bench marks.

A further recommendation is that your application has some intelligence when connecting to database services, such that the workload directed at specific nodes, leads to a minimal amount of cluster inter-connect traffic. Without any evidence to back it up, there are probably applications out there which use tables to implement things best described as application level semaphores or tables which occupy very few blocks with flag like columns, which if placed on a RAC cluster will slay the inter-connect.

2. High availability

It is recommended that there is adequate capacity in the cluster to soak up the workload created when one or more nodes fails. Technical issues aside, a cluster is not a panacea for high availability and this can be undermined by several factors:-

Poor testing of changes applied to a cluster.
Lack of a realistic test cluster.
A lack of tight change management procedures and processes.

In short, as I alluded to all of this, it begged the question, how many RAC sites carry out all of this work when implementing RAC, there is no way of knowing suffice it to say that the answer is probably less than 100%.

Nuances Of JDBC Connection Pools

2008-12-26T14:11:00.000-08:00

There was a particular piece of code I was tuning a while back which highlighted some of the quirks of JDBC connection pooling. A piece of PL/SQL created and executed an INSERT SELECT statement based upon the parameters it was called with from a WebSphere application server. The statement was bind friendly, but due to bind peeking a lot of on the fly re-optimizations were taking place which hit performance.

A common misconception with Oracle is that execution plans are only re-generated when a hard parse takes place or when an execution plan is aged out of the shared pool, this is not the case, v$sql_shared_cursor gives numerous reasons as to why re-optimizations and also the generation of child cursors take place. However, as per a previous posting I made, this view does not capture bind peeking activity.

As a quick fix, and yes I know this solution was only really a crutch, I placed an execute immediate statement at the top of the code to set cursor_sharing to force. I could have also fixed this using the hint opt_param('_optim_peek_user_binds', 'false'), with the benefit of hind sight and the rule of thumb that the scope of the fix should match the scope of the problem, this may have been a better way to have gone.

I did not realise at the time that this alter session would affect other SQL statements executed by the application server that reused this particular connection from the pool, with disastrous results on performance. In particular, a batch process which executed the same statement with different binds multiple times was not being re-optimized at execution time, when in fact Oracle had just cause to do this. Fortunately this particular scenario was constrained to one test environment I was working on. The resolution to the problem was to put another execute immediate statement at the bottom of the stored procedure to set cursor_sharing back to what is was set to before.

A popular technique to avoid database access and solve mutating trigger problems is to use a PL/SQL array declared in a PL/SQL package specfication. Indeed, the other nuance of JDBC connection pooling is that if an SQL call from the Java code intialises such a variable, and then the connection is released in the Java code, subsequent calls from the same Java thread, may not necessarily reuse the same connection from the JDBC connection pool and thus not see the data cached in the PL/SQL package.

Good Java programming practice dictates that you should always lazily grab resources and then release them as soon as they are no longer required, incresing the likelihood that threads may use many different connections from the JDBC connection pool. Also note that when obtaining a JDBC connection in Java code, what you are in fact doing is obtaining a handle to a connection from the pool . New connections to the database should only be made:-

1. After the application server starts up

2. In the minimum and maximum connection settings for the pool are not the same and the application server needs to grow the size of the pool up to the maximum setting.

A Presentation On Trouble Shooting Bad Execution Plans In Oracle

2008-12-26T13:27:00.001-08:00

Oracle Sql Tuning

View SlideShare presentation or Upload your own.

Performance Bottlenecks In The J2EE App Server That Can Throttle The Database

2008-12-23T02:38:00.000-08:00

I mentioned in my post about layered architectures that I would discuss factors that can throttle throughput from the J2EE application server to the database (or persistence layer). To start of with, I will focus of the ethos of designing distributed object architectures, in the words of Martin Fowler:-

". . . Hence, we get to my First Law of Distributed Object Design: Don't distribute your objects!"

Distributing components between J2EE application servers in the J2EE world, involves the use of remote method invocation (RMI) calls to remote interfaces, assuming we are talking about synchronous communication here. Remote method calls to beans deployed to remote servers incurs the following performance overheads:-

Copy by value

Network latency

Every incoming RMI call needs to be serviced by its own Java thread

Servicing incoming connections from the TCP-IP stack causes context switches

The original text behind this design ethos appears in Martin's book "Patterns Of Enterprise Architecture", there is also a very similar article that Martin has published in Dr Dobbs journal which can be found here.

If all the beans are deployed to the same application server, this will eliminate all the bottlenecks except for that of copy by value. However, as you will see, the overhead of pass by copy is still significant and will both increase heap utilisation and extend the part of major garbage collection that looks for dangling objects on the heap. On the last project I worked on at the time of writing this, we found that:-

The 10g database time (not to be confused with wall clock time) was low.
Average number of sessions from ADDM reports was low.
The CPU on the WebSphere server was maxed out.

Profiling the code with a Java profiler revealed that 50% of the CPU time was being expended on a method with a name like com.ibm.CORBA.util.copyObject. This was because the business logic or domain beans were calling the database access beans via remote interfaces, which uses pass by value.

For the benefit of anyone reading this who is not that familiar with the J2EE world, I will elaborate on pass by reference, pass by value and remote and local homes. One of the original design goals being the J2EE standard was to present a framework in which distributed applications could easily be produced, this was based on CORBA and the synchronous communications method was RMI. Prior to the EJB 2.1 standard, it was assumed that when one enterprise bean called another bean, the 'other' bean was always remote, hence a remote interface and copy by value was always used. However, as time went by people realised that despite this, a lot of applications used beans that were deployed to the same J2EE app server and hence suffered this performance penalty for no added benefit. Thus, in the EJB 2.1 standard 'local' interfaces came about. Beans talking to each other using local interfaces use the much more efficient pass by reference method of passing object references rather than whole objects by value.

Fortunately with WebSphere there is an option available on the Object Request Broker (ORB), to turn pass by reference 'On', this forces all the beans deployed to the same EJB container using the same class loader to use pass by reference, irrespective of whether they have been coded to use remote interfaces. In the case of the application that I was looking at, this had the rather large impact of:-

Reducing batch process run times by 50%.

Reducing CPU consumption on the server hosting the WebSphere application server by 50%.

Doubling the 10g db time that a batch process spent in the database.

Increasing the average number of sessions active whilst the batch process was running, again this is an Oracle 10g time metric which is the database time divided by the actual elapsed "walk clock" time.

Setting the class loader policy on for the application server to single also helps. In tuning WebSphere, I also looked at the:-

server JVM

client JVM

ORB with regard to the setting up a JNI reader thread pool and the fragment size

JDBC pool, both the size of the pool, pool utilization and cached statement discard rate.

EJB container, specifically the EJB cache size

Log4j logging level

number of batch process threads
turning off the diagnostic trace

To the best of my knowledge, the only thing I didn't look at was turning the PMI off completely. For both the client and server JVM, I found that using the JVM otpions as used by IBM in a Solaris SPECjAppserver2004 submission yielded the best results. Refer to this link for a blog on which some of the IBMers who carry out this work write, in particular Andrew Spyker, WebSphere performance architect.

However, out of all the things I looked at, I got the biggest bang for my buck from turning pass by reference to 'On' by a long chalk. This comes with a word of caution though, if your software is designed such that beans that receive RMI calls modify objects and the calling bean is not expecting this, wild and erratic application can result. It is for this very reason, or so I believe , that pass by reference is now turned 'Off' by default on WebSphere 6.1.

This brings me on to J2EE clustering and shared nothing architectures. WebSphere network deployment, the clustered version of the WebSphere uses a shared nothing architecture, similar to that discussed in this article here, which again re-iterates the point about distributed architectures and performance:-

"The most scalable architectures are sharing-nothing clusters. In such clusters, each node has the same functionalities, and knows nothing about the existence of other nodes. The load balancer will decide how to dispatch the requests to the back end server instances. It is rare that a load balancer will become a bottleneck, because a load balancer only need to do some very simple jobs, such as distributing requests, health checking, and session sticking. By adding more nodes, the computing ability of the cluster is increasing almost lineally, if the back end database or other information systems are powerful enough."

With WebSphere network deployment, the same ear file is deployed to each node in the cluster, or cell, to use IBM terminology, workload distribution is managed in a round robin fashion via the workload manager. WebSphere eXtreme scale adds some extra intelligence to this by distributing the work load based on the current available capacity of nodes in the cluster.

What exacerbated my original problem of the massive copy by object overhead was the batch processing design, which is fundamentally this. The problem being that because Oracle is called for every individual item in the workload, there is heavy traffic between the business logic layer and the persistence layer. Accessing a database item by item is really inefficient in terms of:-

1. Hard and soft parsing, in particular I found that setting the batch process thread count above 32 stressing the library cache pin mutex. Tanel Poder has written a good article on the library cache pin mutex and extended use of mutexes in 11g here.

2. Latching

3. Network round trips and network latency

4. Physical and logical reads

5. JDBC calls

Some people get very agitated about batch process designs that process the workload item by item and in principle this results in certain inefficiencies when accessing the database. However, the performance targets for the batch processes where the same as those for the system this new one was replacing, but with 200% of the workload and two years worth of "Database aging", i.e. the database had been artificially aged to give the appearance of the application having been in use for two years. In summary, all batch processes beat their counter parts in the original application by at least a factor of 100%.

The morals of this story are:-

1. Distributed object architectures are bad for performance

2. Your application needs to be profiled in order to work out where the time is going, in the example I have highlighted, the database was never the bottleneck.

3. A holistic approach to looking at the architecture needs to be taken with regard to achieving good performance and scalability.

I will get around to discussing approaches on alleviating the stresses that item by item oriented batch processing imposes on the Oracle database in a future post.

J2EE Layered Application Architectures and Performance

2008-12-01T13:12:00.000-08:00

I have spent most of my career as a DBA focusing on performance. With most applications there is an intimate relationship between the design and architecture of the application and that of its performance and scalability. Generally speaking, if the design and architecture is wrong, getting an application to perform and scale can be akin to applying elastoplasts over the biggest holes.

As an industry, IT has a patchy record for delivering projects on time and to budget, as such there are many projects out there that barely manage to cross the finishing line having met all of their functional requirements let alone performance requirements. Due to this, tuning can be reactive and end up becoming a battle against the architecture and design.

There was a time when most applications consisted of a client and business logic written directly in the database using stored procedures. Indeed the database software formed the largest part of the application stack. However, more recent architectural trends have been heavily influenced by:-

The internet, Web 2.0 has firmly established itself and Web 3.0 is around the corner

Service Oriented Architectures (SOA)

Software as a service (SAAS)

Event driver architectures
Extreme transaction processing
Grid architectures
Mash ups

Indeed, there was a time when the database was a focal point of integration efforts, now the industry is moving towards integration via XML based middleware. Here is one such article on this very subject from design and architecture luminary Martin Fowler. In the current climate "integration architects" are highly sort after people.

Cloud computing is on its way and just as during the old web 1.0 days, to quote a marketing tag line from Sun. "We were into intranets when most people thought this was a typo", there will also be internal clouds as well. Therefore if you have an interest in performance and tuning, it is highly likely that you will have some involvement in the areas outside the database and find that the database is being under utilised because of what is happening in the layers above it. Cary Millsap made a presentation at Oracle Open World 2007 highlighting the importance of establishing application profiles when tuning.

One of the most prevalent software architectures today uses a combination of layering and tiering, a tier being a layer that is physically deployed on a separate server as opposed to a layer. What is paramount about the tier-ing / layering design approach is that it provides a "clean separation of concerns". Layering can also be vertical, i.e. swathes of development for particular functional areas which cut through the horizontal layers. Classically, the 'Horizontal' layers are:-

Presentation

Business logic (or domain)

Integration

Architects in the finance world prefer tiers, in that each tier can reside in its own DMZ (de-militarised zone) and hence provide maximum security. My interest in application architecture has developed from the bottom up, i.e. from the database upwards. However, the integration layer is not always an RDBMS, it can be:-

1. A web service

2. A message queue

3. Distributed object cache (which may be transactional)

4. An enterprise service bus adapter or message broker

5. Java Connection Architecture connection

6. etc . . .

In fact with XA distributed transactions, a transaction can span multiple end points below the integration layer.

Some patterns are almost de facto in certain layers and tiers, such as model view controller in the presentation tier, session facade in the business logic or domain tier, to name but two. Also, there are frameworks and third party libraries that take a lot of the effort out of having to crank out boiler plate code and the code behind the application infrastructure, e.g.:-

1. Apache struts and Java Server Faces for the presentation layer

2. Spring for the whole application or specific layers

3. Log 4j for logging

4. Apache commons, which is a common utilities library

However, whatever you do you should never; code business logic into the presentation layer or explicitly code details of the integration layer into the business logic or domain layer.

When looking at the performance of such architectures one needs to establish a profile of where particular activities spend their time within the various layers or tiers and the networks involved in connecting any tiers. If a J2EE application server is involved in the application stack, something known as the "carrot model" should be understood. All J2EE application servers use the following set of components that form a pipe line from the 'front' of the application server to the 'back', these are:-

1. Object request broker used for J2EE clients.

2. Web container

3. Enterprise Java Bean Container

4. JDBC Pool or JCA connectors.

The WebSphere application server has a http server built into the web container, however, this is only really recommended for testing purposes so you will probably have an http server outside of the J2EE application server. What the "carrot model" represents is that component utilisation is usually quite high at the 'front' of the application server and gradually dwindles off as you get towards the JDBC connection pool or JCA connectors, hence the analogy of a carrot, thick at one end and thin at the other. Note that ORB is only used for J2EE clients, web clients will utilise the web container http server or external http server directly.

A lot of texts refer to the effort and careful consideration that needs to go into designing the layers of the applications architecture. I would add that the same level of consideration needs to be given to where processing should be carried out. In the simplest of architectures with a J2EE application server on one machine and a database on another, there is still scope for the network to become the bottleneck by excessive calls being made to the database to carry out the processing of business logic and also standing data lookup. In one example, a stateless bean used to access fixed domain data was the most utilised bean in the application until a standing data cache in the application server was implemented. In another example, a stored procedure was called to validate individual items within individual records, using fixed domain data cached in the application server. By carrying the validation entirely within the application server, the process concerned ran faster, required fewer threads than before with lower CPU consumption on both of the servers hosting the application server and the database.

There is equal scope for poor performance and scalability to be present by design with vertical layering also. One particular design trend is to develop specific functional areas with an API implemented using a session facade. Tools such as Apache Axis and also the J2EE application server itself make exposing stateless session beans as a web service easy and straight forward. Consider two separate beans used to implement the APIs for two different functional areas, if these areas are tightly coupled and they are to be used in a service oriented architecture, there is going to be a significant amount of XML to Java serialisation that takes place involving the two respective web services, that might be avoided, if the API was provided by a single bean.

I strongly advise people to read the IBM red book "Designing and Coding Applications for Performance and Scalability in WebSphere Application Server". This is one of the best texts I have come across in the area of designing and writing scalable and well performing J2EE applications. If I had my way, this would be mandatory reading material for all developers, designers and architects on J2EE development projects.

In the not too distant future I will post an article on my experiences of the specific things that can stifle utilisation of the database server when a J2EE application is used.

The Rise Of MySql From The Low End

2008-11-04T13:49:00.000-08:00

At the time of writing this, the penultimate bog entry from Jonathan Schwartz on the Sun web site refers to innovation loving a crisis, the crisis being the turmoil in the global economy. From this, it is not too difficult to make the mental leap and arrive at the inference that software with no license costs is going to be very attractive at present. Now, this comes from Jonathan's latest blog entry (again latest at the time of writing this):-

"The third is the need for change - one executive to whom I spoke recently said her entire discretionary budget was consumed by one proprietary vendor's price increase. So she's out looking for an alternative, and MySQL fits the bill. Which is to say, necessity's the mother of invention - and there's a lot of necessity going around right now."

Makes you wonder if MySql is going to start biting Oracle at it's ankles around the lower end of the market more than ever before.