Pat Shuff

Subscribe to Pat Shuff feed
Oracle Blogs
Updated: 3 hours 18 min ago

last blog for a while

Wed, 2016-06-29 17:02
This will be my last blog entry for a while. We have been going through the database options and looking at the different database options for Platform as a Service. My intent was to go through all of the options and look at each of them individually. Unfortunately, I am in process of changing groups. My hopes are to restart this blog on blogs.oracle.com/iaas and blogs.oracle.com/paas. I am in process of changing jobs within Oracle and and the process is taking longer than expected. I have had fun on this journey that started back in April. Hopefully everyone has enjoyed the trip as well. My Oracle email will probably be turned off after June 30th so if you need to get in touch with me use the same format for email but at gmail.com. My hope is to resume this blog at the other two locations starting in August. pat

database option - Spatial and Graphics

Tue, 2016-06-28 02:07
Today we are going to focus on the Spatial and Graphics option of the Oracle Database. Most business information has a location component, such as customer addresses, sales territories and physical assets. Businesses can take advantage of their geographic information by incorporating location analysis and intelligence into their information systems. The geospatial data features of Oracle Spatial and Graph option support complex geographic information systems (GIS) applications, enterprise applications and location services applications. Oracle Spatial and Graph option extends the spatial query and analysis features included in every edition of Oracle Database with the Oracle Locator feature, and provides a robust foundation for applications that require advanced spatial analysis and processing in the Oracle Database. It supports all major spatial data types and models, addressing challenging business-critical requirements from various industries, including transportation, utilities, energy, public sector, defense and commercial location intelligence.

The Spatial home page is a good starting point to learn more about the technology. Books that cover this topic are

Note that most of these books are three years old or older. Spatial has not changed much between 11g and 12c so the older books are still relevant. The key to the Spatial component is being able to define objects using geospatial tags. To achieve this, Oracle extended the database with the SDO_GEOMETRY data type. This is used just like an INTEGER or CHAR declaration for a variable but it contains a latitude and longitude element to define where something is located. Some sample code that we can lift from the Pro Oracle Spatial book looks like
SQL> CREATE TABLE  us_restaurants_new
(
  id                    NUMBER,
  poi_name       VARCHAR2(32),
  location         SDO_GEOMETRY    -- New column to store locations
);
This creates a table that defines an entry that helps us find where the restaurant is located. We can populate this entry with
SQL> INSERT INTO  us_restaurants_new  VALUES
(
  1,
  'PIZZA HUT',
  SDO_GEOMETRY
  (
    2001,  -- SDO_GTYPE attribute: "2" in 2001 specifies dimensionality is 2.
    NULL,    -- other fields are set to NULL.
    SDO_POINT_TYPE  -- Specifies the coordinates of the point
    (
      -87,  -- first ordinate, i.e., value in longitude dimension
      38,  -- second ordinate, i.e., value in latitude dimension
      NULL  -- third ordinate, if any
    ),
    NULL,
    NULL
  )
);
This inserts and entry for restaurant number 1, labeled PIZZA_HUT, and the location is defined by a point located at -87, 38. Note that these are relative locations defined in relation to a map. We use the SDO_GTYPE to define what type of mapping that we are using and how we are describing the location for this store.

The key benefit to this is that we can define restaurants and things like interstates. We can query the database by asking for any reference that is half a mile from the interstate. This is done with the following query

SQL> SELECT poi_name
FROM
  (
    SELECT poi_name,
      SDO_GEOM.SDO_DISTANCE(P.location, I.geom, 0.5) distance
    FROM us_interstates  I, us_restaurants  P
    WHERE I.interstate = 'I795'
      ORDER BY distance
  )
WHERE ROWNUM <= 5;

POI_NAME
-----------------------------------
PIZZA BOLI'S
BLAIR MANSION INN DINNER THEATER
KFC
CHINA HUT
PIZZA HUT
The select statement does a distance calculation looking at the distance between the interstate labeled I795 and any restaurant in the database. Note that we could have selected an address on the interstate and found something that is less than a specified distance. This is typically how something like Google Maps works. It uses your current location which is read from your phone as a latitude, longitude, and elevation and shows you the search term close to you. This allows you to easily find banks, places to eat, places to get gas, or an address that you are trying to get to.

We can not only look for distances relative to a point or a line (as we did with the interstate) but we can draw shapes around an object and look for things that fall into or out of the shape. For example, if we get the GSP points for a park, we can draw a shape that defines the park using latitude and longitude points. We can then look for related objects inside the park, outside the park, or within a few feet of the park. This helps police look for crimes that happen surrounding a park and react appropriately. In the database we define an object with spatial data and draw a shape around the object. A simple way of doing this is a simple box. The code to do this would look like

SQL> INSERT INTO  USER_SDO_GEOM_METADATA  VALUES
(
  'CUSTOMERS',      -- TABLE_NAME
  'LOCATION',       -- COLUMN_NAME
  SDO_DIM_ARRAY     -- DIMINFO attribute for storing dimension bounds, tolerance
  (
    SDO_DIM_ELEMENT
    (
      'LONGITUDE',  -- DIMENSION NAME for first dimension
      -180,         -- SDO_LB for the dimension
      180,          -- SDO_UB for the dimension
      0.5           -- Tolerance of 0.5 meters
    ),
    SDO_DIM_ELEMENT
    (
      'LATITUDE',   -- DIMENSION NAME for second dimension
      -90,          -- SDO_LB for the dimension
      90,           -- SDO_UB for the dimension
      0.5           -- Tolerance of 0.5 meters
    )
  ),
  8307              -- SRID value for specifying a geodetic coordinate system
);
You can define a data type as
  • Point
  • Line string
  • Polygon
  • Polygon with a hole
  • Collection (a combination of all of the above)
  • Compound line string
  • Compound polygon
  • 3d Composite surface
  • 3d Simple solid
  • 3d Compound solid
  • 3d Collection (a combination of all 3d objects)
When you define a spatial object it uses the SDO_GEOMETRY structure. This structure contains an SDO_GTYPE that defines if the object is 2d or 3d as well as the data type (0 = Uninterpreted type, 1 = Point, 5 = Multipoint, 2 = Line, 6 = Multiline, 3 = Polygon/surface, 7 = Multipolygon/multisurface, 4 = Collection, 8 = Solid, 9 = Multisolid). An entry of 2001 would be a 2d object, designated by the 2, that is a single points, designated by the 1. If this entry were 2002 it would be a 2d object that is a series of points to create a line, designated by the second 2. The SDO_SRID defines specifies the spatial reference system, or coordinate system, for the geometry. We can have a relative coordinate system or use latitude and longitude for coordinates. The SDO_POINT attribute specifies the location of a point geometry, such as the location of a customer. This gives us a reference point to work from and the rest of the data is the relative information based on the SDO_SRID. For example, we can draw a polygon defining a park starting at the northwest corner of the park. The SDO_POINT will provide the northwest corner of the park. The SDO_ELEM_INFO and SDO_ORDINATES attributes describe the polygon around the park. For more detailed examples, look at Chapter 3 of Pro Oracle Spatial for Oracle Database 11g.

We are not going to go into deep detail on how to program Spatial. It is recommended that you look at

It is important to note that Spatial is an optional package that runs on the Enterprise Edition database. If you are going to run this in the cloud you need to use the High Performance Edition or Extreme Performance Edition. If you are going to run this on IaaS you need to purchase the option on top of your database license and the processor metrics need to match. For example, if you run on a 2 virtual core system in the cloud, you need a 2 virtual core license for Spatial as well. You can not run Spatial on Amazon RDS because they disable this feature.

In summary, Spatial and Graphics are optional packages that help you do locational queries against a database. Spatial is not unique to Oracle but the structures and select statements typically do not cross database types but does work with products like Golden Gate to replicate data to other database spatial structures and queries. Spatial is a very powerful package that simplifies select statements that would be very complex otherwise. Finding distance between objects or distances from a line (highway for example) or distances from a polygon (park for example). If your application needs Spatial you need to select the High Performance or Extreme Performance editions.

Safari Books Diversion

Mon, 2016-06-27 02:07
Today I am going to step back and look at something relatively simple but very powerful. One thing that my company provides for employees is a subscription to technology books online. I tend to reference these books in my blog entries mainly because I find them to be good reference material. When I write a blog entry I try to first address the topic from the mindset of a sales rep. I address the simple questions around what is the technology, why is it relevant, and how much will it cost me. If possible the discussion also blends in a compare and contrast with another solution from another vendor. The second post is a technology how to targeted at the pre sales engineer or consultant. To dive deeper typically I use books, whitepapers, hands on tutorials, and demos to pull material from. Safari Books OnLine is my virtual library. Years ago I would go to Barnes and Noble, Fry's, or Bookstop and purchase a book. Safari Pricing starts at $400/year for individuals or teams and is flexible for corporations. If you break this down this means that you need to read about 8-10 books a year to break even. If you read fewer than that, purchase them from Amazon. If you read more than that or just want to read a chapter or two, subscribe to Safari.

Two of the features that I like about this interface is the search engine and the index engine. With the search engine, it looks inside of books and allows you to sort by relevance, date, and allows you to search inside a search. For example, if I do a search for jumpstart I get 3070 references. If I add solaris to the search I get 101 results. Note on the left there are three books written in 2016 and two books written in 2015. We can narrow our search and look for recent books that talk about jumpstart technology provided with Solaris. True, this might not be a relevant topic to you but it is an example of how to find a difficult to find topic in your virtual library.

We can add this search index to our favorites by clicking on the Add to Favorites button and selecting a topic list to add to. In this example we add a JumpStart book from 2001 to our Solaris book list.

We can look at more relevant publications and find something related to Solaris 11.2. We see the relevant information in the search index and when we click on the book it takes us to the relevant chapter. Note the highlighted text from our search. I find this is a good way of researching a topic that I need to learn more about or finding tutorials or examples of how to do something.

One of the nice things about search indexes or lists is that you can share this list with other people and look at other peoples lists. This is done by looking at your Favorites and Folders you can look at the topics that interest you with the books you have saved on that effective shelf.

One of the nice things is that you can look at shelves of other users. If you click on Shared List and search for your shelf title, you get a list of other users shelves. In this example we searched for Solaris and got five shelves that other users are maintaining.

We can subscribe to these shelves and add it to our favorites. This is done by clicking on the Following "+" sign. It adds the shelf to your Favorites list on the left. Note that we are following the "Solaris stuff" folder.

We can also add this as an RSS feed to our mail reader and get updates when the shelf is updated. We can then copy the rss feed html and add it to our news reader or Thunderbird email interface.

If we add this to our Thunderbird reader we get an email interface showing updates and new books added to the shelf. We don't need to go check the list on a regular basis but look at the newsfeed section of our mail browser

I hope this simple diversion was a good break from our dive into DBaaS and PaaS. Being able to do more than just a simple Google search is typically required to find examples and tutorials. Books historically have been a good place to find this and having access to not only my virtual bookshelf but other people's bookshelves where they sort and index things is a good thing. The $400 cost might be a bit prohibitive but the freedom is a good thing. Given that my company provides this subscription at no cost to me, I will continue to use this and read technology books on an airplane in offline mode and search as I am creating blog entries.

database option - Data Guard part 2

Fri, 2016-06-24 02:07
Normally the part two of the option pack has been a hands on tutorial or examples liberally lifted from Oracle by Example, OpenWorld hands on labs, or the GSE Demo environment. I even went to an internal repository site and the database product manager site and all of the tutorials were for an existing database or labs on a virtual machine. None of these were easy to replicate and all of them assumed a virtual machine with a pre installed instance. None of the examples were form 2015 or 2016. If anyone knows of a hands on tutorial that uses the public cloud as at least one half of the example, let me know. There is a really good DR to the Cloud whitepaper that talks about how to setup Data Guard to the cloud but it is more of a discussion than a hands on tutorial. I typically steal screen shots and scripts from demo.oracle.com but the examples that exist in the GSE demo pool use Enterprise Manager 10g, servers inside of Oracle running an early version of 11g, or require a very large virtual image download. The closest thing that I could find as a hands on tutorial is Oracle by Example - Creating a Physical Standby. For this blog we will go through this tutorial and follow along with the Oracle Public Cloud as much as possible.

Step One is to create an 11g database. We could do this on 12c but the tutorial uses 11g. If anyone wants to create a 12c tutorial, contact me and we can work on a workshop together. We might even be able to get it into the hands on labs at OpenWorld or Collaborate next year. Rather than going through all of the steps to create an 11g instance I suggest that you look at the May 4th blog entry - Database as a Service. Select 11g and High Performance Edition. We will call this database instance PRIM rather than ORCL. Your final creation screen should look like

We want to create a second database instance. We will call this one ORCL and select High Performance Edition and 11g. The name does not matter as long as it is different from the first one. I am actually cheating on the second one and using a database instance that I created weeks ago.

It is important to note while we are waiting on the database to finish that we can repeat this in Amazon but need to use EC2 and S3. We can also do this in Azure but in Azure Compute. We will need to provide a perpetual license along with Advanced Security and potentially Compression if we want to compress the change logs when we transmit them across the internet. It is also important to remember that there will be an outbound charge when going from one EC2 or Azure Compute instance to the other. If we assume that we have a 1 TB database and it changes 10% per day, we will ship 100 GB daily or being conservative and saying that we only get updates during the week and not the weekend we would expect 2 TB of outbound charges a month. Our cost for this EC2 service comes in at $320/month. If we use our calculations from our Database Options Blog post we see that the perpetual license amortized over 4 years is $2620/month. This brings the cost of the database and only Advanced Security to $2940. If we amortize this over 3 years the price jumps to $3,813/month. When we compare this to the Oracle High Performance Edition at $4K/month it is comparable but with High Performance Edition we also get eight other features like partitioning, compression, diagnostics, tuning, and others. Note in the calculator that the bulk of the processor cost is outbound data transfer. It would be cheaper to run this with un-metered compute services in the Oracle cloud at $75/month.

If we follow the instructions in DR to Oracle Cloud whitepaper we see that the steps are

  1. Subscribe to Oracle Database Cloud Service
  2. Create an Oracle instance
  3. Configure Network
  4. Encrypt Primary Database (Optional)
  5. Instantiate Data Guard Standby
  6. Perform Data Guard health check
  7. Enable Runtime Monitoring
  8. Enable Redo Transport Compression (Optional)
So far we have done steps one and two. When the database creation has finished we perform step 3 by going into the compute console and opening up port 1521 or the dblistener service. We do this by going to the compute service and looking for our database instance name. In our example we hover over the service and find the dblistener service for prs11gPRIM. We select update and enable the port. Given that these are demo accounts we really can't whitelist the ip addresses and can only open it up to the public internet or nothing. We do this for the primary and the standby database

Once we have this configured we need to look at the ip addresses for prs11gPRIM and prs11gHP. With these ip addresses we can ssh into the compute instances and create a directory for the standby log files. We can create these files with the /u02 partition with the data or the /u03 partition with the backups. I suggest that you put them in the /u04 partition with the archive and redo logs. Once we have created these directories we can follow along with the Oracle By Example Physical Data Guard example starting at step 3. The network configuration is shown on page 12 of DR to Oracle Cloud whitepaper. We can also follow along with this using prs11gPRIM as the primary and prs11gHP as the standby. Unfortunately, after step 5 the instructions stop showing commands and screen shots to finish the configuration. We are forced to go back to the OBE tutorial and modify the scripts that they give and execute the configurations with the new names.

Again, I am going to ask if anyone has a full tutorial on this using cloud services. It seems like every example goes half way and I am not going to finish it in this blog. It would be nice to see a 12c example and see how a pluggable database automatically replicates to the standby when it is plugged in. This would be a good exercise and workshop to run. My guess is this would be a half day workshop and could all be done in the cloud.

database option - RMAN

Thu, 2016-06-23 02:07
Technically, database backup is not an option with database cloud services, it is bundled into the service as it is with on premise systems. Previously, we talked about backup and restore through the database cloud console. Unfortunately, before we an talk about Data Guard and how to set it up we need to dive a little deeper into RMAN. The first step in setting up Data Guard is to replicate data between two database instances. The recommended way of doing this is with RMAN. You can do this with a backup and recover option or duplicate option. We will look primarily at the duplicate option.

The topic of RMAN is a complex and challenging subject to tackle. There are many configuration options and ways to set up backups and data sets as well as many ways to recover rows, tables, or instances. Some books on RMAN include

Fortunately, to setup Data Guard, we don't need to read all of this material but just need to know the basics. Unfortunately, we can't just click a button to make Data Guard work and automatically setup the relationships, replicate the data, and start log shipping. The key command that we need to get the backup from the primary to the standby is the RMAN command. We can execute this from the primary or the standby because RMAN provides a mechanism to remotely call the other system assuming that port 1521 is open between the two database instances
$ rman target user1/password1@system1 auxiliary user2/password2@system2
In this example user1 on system1 is going to backup the instance that it default connects to and replicates to system2 using the user2 credentials. This command can be executed on either system because we are specifically stating what the source is with the name target and what the standby is with the name auxiliary. Once we connect we can then execute
rman> duplicate target database for standby from active database;
What this will do is replicate the database on system1 and push it to system2. The command will also setup a barrier point in time so that changes to system1 are shipped from this point forward when you enable Data Guard. According to Oracle Data Guard 11gR2 Administration Beginner's Guide (Chapter 2) the output of this command should look something like
Starting Duplicate Db at 26-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
...
contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u02/app/oracle/oradata/orcl/control01.ctl';
   restore clone controlfile to  '/u02/app/oracle/flash_recovery_area/orcl/control02.ctl' from
 '/u02/app/oracle/oradata/orcl/control01.ctl';
}
executing Memory Script
....
sql statement: alter database mount standby database
...
Starting backup at 26-JUL-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
output file name=/u02/app/oracle/oradata/orcl/system01.dbf tag=TAG20120726T160751
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:04
channel ORA_DISK_1: starting datafile copy
...
sql statement: alter system archive log current
contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=789667774 file name=/u02/app/oracle/oradata/orcl/system01.dbf
...
Finished Duplicate Db at 26-JUL-12
In this example we copied the system01.dbf file from system1 across the network connection and wrote it to /u02/app/oracle/oradata/orcl/system01.dbf on system2.

Let's take a step back and talk about RMAN a little bit to understand what is going on here. If we look at Oracle RMAN Pocket Reference it details some of the benefits of using RMAN over file system copy or disk cloning to backup a database. These include

  • Incremental backups that only copy data blocks that have changed since the last backup.
  • Tablespaces are not put in backup mode, thus there is no extra redo log generation during online backups.
  • Detection of corrupt blocks during backups.
  • Parallelization of I/O operations.
  • Automatic logging of all backup and recovery operations.
  • Built-in reporting and listing commands.
I would add
  • Compression of data as it is written
  • Encryption of data as it is written
  • Tiered storage of backups to disk and secondary target (cheaper disk, cloud, tape, etc)
When RMAN executes it creates a recovery catalog database which is basically a table in the sys area that records the schema within the catalog database and the tables (and supporting objects) within the schema that contain data pertaining to RMAN backup and recovery operations performed on the target. It also stores details about the physical structure of the target database, a log of backup operations performed on the target database’s datafiles, control files, and archived redo log files as well as stored scripts containing frequently used sequences of RMAN commands

When we execute a backup command we create a backup set that is written to the recovery catalog. The backup set is given a tag that we can reference and restore from. If we do daily incrementals we might want to use a part of the date to create the tag. We can restore to a specific point or date in time from our incremental backups.

If we are worried about having usernames and passwords being passed in via the command line or embedded in scripts we could store this password in the database with the orapwd command. This creates a username/password pair and stores it where RMAN can easily pull it from the database. We do need to give the rmanadmin user rights to execute as SYSDBA but this is easily done with a grant command. Once we do this we can drop the username and password from the rman command and only pass in the username@system parameter. The key reason that you might want to do this is invoking the command from the command line with the password exposes the password through the ps command which can be executed by any user. Embedding the password with the orapwd command helps hide this password.

The nice thing about RMAN is that you can backup and restore parts rather than all of a database. You can execute

RMAN> backup tablespace system, user;
RMAN> backup '/u01/app/oracle/oradata/ORCL/system01.dbf';
RMAN> backup incremental level 4 cumulative database skip readonly;
which will backup the user and system tables, backup the system01.dbf file and all of the tables that it includes, and do a backup of the data that has changed since the last level 4 backup and user previous lower level backups to aggregate changes into the current backup. Note that these three commands do significantly different things. We can look at what we have backed up using the
RMAN> list backups;
to see our backup set and where they are stored. When we looked at the database backup cloud service we went through a backup and recovery.

If we had done a list backups after this backup we would have noticed that the data written to SBT_TAPE was really written to cloud storage and potentially to local disk. We can then point our standby system to this backup set and restore into our database instance. This is done by importing the catalog or registering the target when we do the backup. The registration is done with a command like

$ rman target / catalog rman_901/rman_901@rman_catalog
where we are backing up a local database signified by the "/" and adding the host rman_catalog with username rman_901 and password rman_901.

My recommendation is to look at chapter 12 of Oracle Database 12c Oracle RMAN Backup & Recovery because it details how to use the duplicate option for rman. This is key to setting up Data Guard because it replicates a table from a primary system onto a standby system just prior to starting the Data Guard services. The command could be as simple as

RMAN> duplicate target database to standby1;
This will duplicate your existing instance from your on premise to a cloud or other on premise instance identified by the label standby1. This typically correlates to an ip address of a secondary system and could be a short name like this or a fully qualified domain name. We could get more complex with something like
RMAN> duplicate target database to standby1 
pfile=/u02/app/oracle/oradata/ORCL/init.ora
log_file_name_convert=('primary','standby');
This will do the same thing that the previous command did but read the init.ora file for the ORCL instance and convert anything in the /u02/app/oracle/oradata/ORCL/primary on our existing system to /u02/app/oracle/oradata/ORCL/standby on our target standby1 system. This is an easy way to replicate data from a PDB called primary to a PDB called standby prior to setting up a Data Guard relationship. The steps recommended to create and configure an RMAN copy are
  1. On your standby server, build your auxiliary database directory structures (aka your target directory)
  2. On your primary server, make a copy of the target init.ora file so that it can be moved to the standby server.
  3. Move the target init.ora file to the auxiliary site with scp or other software to copy files.
  4. Start or restart the standby instance in NOMOUNT mode
  5. Configure the listener.ora at the standby site
  6. Configure the tnsnames.ora file at the primary site
  7. Create a password file at the standby server
  8. Move the FRA files from primary to standby
  9. From the primary system, run your duplicate command within RMAN
You can add parameters to allow for parallel copies of the data. You probably should not compress or encrypt the data since we will be pulling it from the backup and writing it into a database. We could potentially compress the data but it will not compress the data on the target system, only compress it for transmission across the internet or local network.

In summary, we needed to dive a little deeper into RMAN than we did before. RMAN is needed to duplicate data from our primary to the target prior to log shipping. There are some complexities associated with RMAN that we exposed and the steps needed to get a secondary site ready with rman are not trivial and need an experienced operating system admin and DBA to get this working. One of the new features of provisioning a cloud database service is a checkbox to create a Data Guard replica in another data center. One of the new features of installing a 12.2.2 database instance is also rumored to have a clone to cloud with Data Guard checkbox. As you install a new on premise database or in cloud database these complex steps are done behind the scenes for you as you would expect from a platform as a service model. Amazon claims to do this with site to site replication and restarting the database in another zone if something happens but this solution requires a reconnection from your application server and forcing your users to reauthenticate and reissue commands in flight. Using Data Guard allows your application server to connect to your primary and standby databases. If the primary fails or times out, the application server automatically connects to the standby for completion of the request. All of this is dependent upon RMAN working and replicating data between two live databases so that log shipping can assume that both servers are in a known state with consistent data on both systems.

database option - Data Guard

Wed, 2016-06-22 11:17
To steal liberally from Larry Carpenter's book on Data Guard, Data Guard is a product of more than 15 years of continuous development. We can trace the roots of today’s Data Guard as far back as Oracle7 in the early 1990s. Media recovery was used to apply archived redo logs to a remote standby database, but none of the automation that exists today was present in the product.

Today we are going to look at the material on Data Guard and discuss the differences between Data Guard, Active Data Guard, and Golden Gate. We are going to look at what it takes to replicate from an on premise system to the cloud and from the cloud to an on premise system. It is important to know that you can also synchronize between two cloud instances but we will not cover this today.

If we look at the books that cover this topic they include

Note that there are not any 12c specific books written on Data Guard. This is primarily due to the technology not changing significantly between the 11g and 12c releases. The key new release in 12c is far sync support. We will cover that more later. There are also books written on Active Data Guard and Golden Gate as well If we take a step back and look at high availability, Data Guard is used to provide this functionality between systems. Oracle Data Guard provides the management, monitoring, and automation software to create and maintain one or more standby databases to protect Oracle data from failures, disasters, human error, and data corruptions while providing high availability for mission critical applications. Data Guard is included with Oracle Database Enterprise Edition and in the cloud the High Performance Edition. Oracle Active Data Guard is an option for Oracle Database Enterprise Edition and included in the Extreme Performance Edition in the cloud.

The home page for Data Guard provides links to white papers

There are also a significant number of blogs covering high availability and Data Guard. My recommendation would be to attend the Oracle Education Class or follow one of the two tutorials that cover Basic Data Guard Features and Active Data Guard Features. In both of these tutorials you learn how to use command line features to configure and setup an active - standby relationship between two databases. Larry Carpenter has done a really good job of detailing what is needed to setup and configure two database instances with these tutorials. The labs are a bit long (50+ pages) but cover the material very well and work with on premise systems or cloud systems if you want to play.

The key concepts around Data Guard are the mechanisms for replication and how logs are shipped between systems. The basic foundation of Data Guard centers around replication of changes. When an insert or update is made to a table, this change is captured by the log writer and replicated to the standby system. If the replication mechanism is physical replication the data blocks changed are copied to the standby system. If the replication mechanism is logical replication the sql command is copied to the standby system and executed. Note that the select or read statements are not recorded and copied, only the commands that write to storage or update information in the database. By capturing the changes and shipping them to the standby system we can keep the two systems in synchronization. If a client is trying to execute a select statement on the primary database and the primary fails or goes offline, the select statement can be redirected to the standby for the answer. This results in seconds of delay rather than minutes to hours as is done with disk replication or recovery from a backup. How the replication is communicated to the standby system is also configurable. You can configure a write and release mechanism or a wait for commit mechanism. With the write and release mechanism, the logs are copied to the standby system and the primary system continues operation. With the wait for commit mechanism the primary stalls until the standby system commits the updates.

Significant improvements were made in 11g with the log writer service (LNS) and the redo apply service (RNS). The LNS has the ability to delay the shipping of the logs in the asynchronous update mode and can compress the logs. The RNS knows how the LNS is configured and can get decompress the logs and apply them as was done before. This delay allows for the LNS to look for network congestion and ship the logs when the network is not so overloaded. The compression allows the packet size to be smaller to reduce contention on the network and make the replication more efficient. It is important to note that you can have a single LNS writing to multiple RNS targets to allow for replication not in a one to one configuration but in a one to many configuration. It is also important to note that this technology is different from table cloning or data masking and redaction that we talked about earlier. The assumption is that there is a master copy of the data on the target system and we only ship changes between the systems when an update occurs on the primary.

The key difference between Data Guard and Active Data Guard is the state of the target database. With Data Guard, the database can not have any active sessions other than the RNS agent. You can not open the database for read only to do backups or analytics. Having an active sessions blocks the RNS agent from committing the changes into the database. Active Data Guard solves this problem. The RNS agent understands that there are active connections and can communicate changes to the active sessions if they are reading data from updated areas. A typical SQL connection uses buffering to minimize reads from the disk. Reads are done from an in memory buffer to speed up requests. The problem with reading data on a standby system is invalidation of these buffers. With Data Guard, there is no mechanism to invalidate buffers of sessions on other connections. With Active Data Guard, these mechanisms exist and updates are not only written to the disk but the cache for the other connections are updated.

Golden Gate is a more generic case of Active Data Guard. One of the limitations of Data Guard is that you must have the same chip set, operating system, and database version for replication. Translations are not done when changes are shipped from primary to standby. You can't for example replicate from a Sparc Server to an X86 server running the same version of the Oracle database. One uses little endian while the other uses big endian to store the bits on disk. Physical replication between these two systems would require a byte translation of every change. Data Guard does not support this but Golden Gate does. Golden Gate allows you to no only ship changes from one database instance to a different chip architecture but a different chip architecture on a different operating system running a different database. Golden Gate was originally crated to replicate between database engines so that you could collect data with SQL Server and replicate the data to an Oracle database or MySQL database so that you could do analytics on a different database engine than your data collection engine. With Golden Gate there is a concept similar to the LNS and RNS but the agents are more intelligent and promote the data type to a master view that can be translated into the target type. When we define an integer it might mean 32 bits on one system but 64 bits on another system. Golden Gate is configured to lead fill from 32 to 64 and truncate from 64 to 32 appropriately based on your use cases and configurations.

To replicate between two systems we basically need an open port from the primary system and the standby system to ship the logs. We also need a landing area to drop the change logs so that the RNS can pick up the changes and apply them. This prohibits Amazon RDS from enabling Data Guard, Active Data Guard, or Golden Gate since you do not have file system access. To run Data Guard in Amazon or Azure you need to deploy the Oracle database on a compute or IaaS instance and purchase the perpetual license with all of the options associated with the configuration. The beautiful thing about Data Guard is that it uses the standard port 1521 to communicate between the servers. There are special commands developed to configure and setup Data Guard that bridge between the two systems. As data is transmitted it is done over port 1521 and redirected to the RNS agent. We can either open up a network port in the cloud or create an ssh tunnel to communicate to our standby in the cloud. The communication works in both directions so we can flip which is primary and which is standby with a command or a push of a button with Enterprise Manager.

The important conversation to have about data protection is not necessarily do I have a copy of it somewhere else. We can do that with RMAN backups or file backups to replicate our data in a safe and secure location. The important conversation to have is how long can we survive without access to the data. If an outage will cost us thousands per minute, we need to look at more than file replication and go with parallel database availability. Data Guard provides this mechanism to keep an active database in another location (on premise or in the cloud) and provides for not only a disaster recovery solution but a way or offloading services from our primary production system. We can break the replication for a few hours and stop the redo apply on the standby while we do a backup. The logs will continue to be shipped just not applied. When the backup is finished we grind through the logs and apply the changes to the standby. We have a window of vulnerability but we have this while we are running backups on our primary system as well. We can now offload the backups to our standby system and let the primary continue to run as needed without interruption. In effect what this does is take all of the small changes that happen throughout the day and ship them to a secondary system so there is a trickle effect on performance. If we do an incremental backup at night we basically block the system while we ship all these changes all at once.

In summary, Data Guard is included with the High Performance Edition of the database and a free part of any on premise Enterprise Edition database. Active Data Guard is included with Extreme Performance Edition of the database and can be matched to synchronize an on premise or in cloud database that is also licensed to run Active Data Guard. There is a ton of reference material available on how Data Guard, Active Data Guard, and Golden Gate works. There are numerous tutorials and examples on how to configure and setup the service. It is important to know that you can use the cloud for this replication and a Dr to the Cloud whitepaper is available detailing how to do this.

database option - multi tenant part 2

Tue, 2016-06-21 02:07
Yesterday we looked at the concept behind multi-tenant and talked about the economics and operational benefits of using the option. Today we are going to look at examples and technical details. Fortunately, this is a hot topic and there are a ton of interviews, tutorials, and videos showing you how to do this. The best place to start is Oracle Technology Network - multitenant. This site lists seven offerings from Oracle Education, six online tutorials, and four video demos. Unfortunately, most books are a little light on this topic and cover it lightly in a chapter buried in the high number chapters. The most recent books cover this topic directly Two of these books are pre-order and the third is only a few months old. The other books talk about it as an abstract term with little or no examples. Safari Books does not have many that cover this subject because the topic is so new and few books have been published on the topic.

The Oracle Base Blog has a series of postings about multitenant and does a really good job of showing diagrams and sample code. There is a significant amount of information at this site (24 posts) looking at the subject in depth. I normally provide a variety of links to other bloggers but I think that this work is good enough to deserve top billing by itself.

Internal to Oracle the GSE Demo pages have a few demos relating to multi-tenant.

  • PaaS - Data Management (Solutions Demo) has a hands on tutorial in the cloud
  • DB12c Multi-Tenant Workshop by William Summerhill is on retriever.us.oracle.com
  • Oracle Database 12c multitenant and in-memory workshop using OPC by Ramulu Posham on retriever.us.oracle.com

For the rest of this blog I am going to go through the workshop by Ramulu Posham because it is the most complete and does everything 100% in the cloud. We could do this on the Oracle Public Cloud using DBaaS, or a database installed in IaaS on Oracle, Amazon, or Azure. We can not do this on Amazon RDS because they disable multi-tenant and prohibit it from working.

The schedule for the workshop is

  • 9:00 - 9:15 intro
  • 9:15 -10:15 cloud intro
  • 10:30 - 2:00 multi-tenant workshop
The workshop consists of creating two pluggable database instances in the cloud and look at pluggable creation, cloning, and snap cloning. The assumption is that you have a public cloud account and can create two 12c databases in the cloud with less than 100 GB of disk space. You can do this on two 1 OCPU 7.5 GB instance but require High Performance or Extreme Performance Edition to get multi-tenant to work. The only software that we will need for our Windows 2012 IaaS instance will be Swing Bench which helps put a load on the database and allows us to look at utilization of resources for a container database and our pluggable instances.

The flow of the workshop is shown in the following slide. We are going to create a database with data in the container and another database and put both instances in a pluggable database on one instance.

Some of the more interesting slides from the presentation are shown below. The file location slide helped me understand where resources get allocated. The redo logs, for example, are part of the container database and not each pluggable. You setup Data Guard for all pluggables by configuring the container and replication happens automatically. The discussion on cloning a database is interesting because you don't need to copy all of the data. You only copy the header information and reference the same data between the original and the clone. Changes are tracked with file links as they are updated on both sides. The managing slide helped me understand that there is still a DBA for each pluggable as well as a master DBA for the container. Seeing that in a picture helped me understand it better. There are also multiple slides on resource management and shares. I pulled a representative slide as well as the summary benefits slide. This is what is covered in the first hour prior to the hands on labs starting.

To start the lab, we create a 12c High Performance instance called salessvc$GC where $GC is a substitute for each lab participant. We will use 01 as our example so we will create salessvc01.

Note that we call the database instance salessvc01, the ORACLE_SID salesc01, and the pluggable container sales01. We can not have the ORACLE_SID and the pluggable instance the same because it will confuse the listener so those names must be different. The creation takes between 30 minutes and an hour on our demo accounts. While we are waiting we will create a second instance with the name cmrsvc01 with similar parameters using the SID of crms01 and a pluggable container of crm01.

Once we have the ip address of the two instances we can create an ssh tunnel for ports 443, 5500, 80, and 1521. This is done by creating an ssh tunnel in our putty client. The presentation material for the labs go through with very good screen shots for all of these steps. We have covered all of this before and are just summarizing the steps rather than detailing each step. Refer to previous blog entries or the workshop notes on how to do this.

The sales instance looks like the screen shots below. We configure the ports and look at the directory structure in the /u02/app/oracle/oradata directory to verify that the sales01 pluggable database was created under the container database salesc01.

Once we have the database created and ports configured we download and launch SwingBench to load data into the database and drive loads to test response time and sql performance.

We need to download SwingBench and Java 8 to execute the code properly. Once we download SwingBench we unzip it and install it with a java command.

The only true trick in the install is that we must execute lsnrctl status on the database to figure out what the listener is looking for in the connection string. We do this then type in localhost:1521 and the connection string to populate the database with SwingBench.

We repeat this process for the cmrc01 instance, repeat the SwingBench install, and unplug the soe database from the crmc01 pluggable database to the salessvc01 database service and plug it in as a pluggable. The big issue here is having to unplug and copy the xml file. It requires uploading the private key and allowing ssh between the two instances. Once this is done the SwingBench is run against both instances to see if performance improves or decreases with two pluggables on the same instance. The instructions do a good job of walking you through all of this.

Overall, this is a good workshop to go through. It describes how to create pluggable containers. It describes how to unplug and clone PDBs. It is a good hands on introduction and even brings in performance and a sample program to generate a synthetic load.

database option - multi tenant

Mon, 2016-06-20 02:07
Before we dive into what multi-tenant databases are, let's take a step back and define a few terms. With an on premise system we can have a computer loaded with a database series of databases. Historically the way that this was done was by booting the hardware with an operating system and loading the database onto the operating system. We load the OS onto the root file system or "/" in Unix/Solaris/Linux. We create a /u01 directory to hold the ORACLE_HOME or binaries for the database. Traditionally we load the data into /u02 or keep everything in /u01. Best practices have shown us that splitting the database installation into four parts is probably a good idea. Keeping everything in the root partition is not a good idea because your can fill up your database and lock the operating system at the same time. We can put the binaries into /u01 and do a RAID-5 or RAID-10 stripe for these binaries. We can then put all of our data into /u02 and name the /u02 file system a flash disk or high speed disk to improve performance since this has a high read and write performance requirements. We can RAID-5 or RAID-10 this data to ensure that we don't loose data or will use a more advanced striping technology provided by a hardware disk vendor. We then put our backups into /u03 and do a simple mirror for this partition. We can go with a lower performing disk to save money on the installation and only keep data for a few days/weeks/months then delete it as we get multiple copies of this data. We might replicate it to another data center or copy the data to tape and put it into cold storage for compliance requirements as well as disaster recovery fall backs. If we are going to replicate the data to another data center we will create a /u04 area for change logs and redo logs that will be shipped to our secondary system and applied to the second system to reduce recovery time. Backups give us recovery to the last backup. A live system running Data Guard or Active Data Guard gives us failure back to a few seconds or a transaction or two back rather than hours or days back.

The biggest problem with this solution is that purchasing a single system to run a single database is costly and difficult to manage. We might be running at 10% processor utilization the majority of time but run at 90% utilization for a few hours a week or few days a month. The system is idle most of the time and we are paying for the high water mark rather than the average usage. Many administrators overload a system that have different peak usage times and run multiple database instances on the same box. If, for example, our accounting system peaks on the 25th through the 30th and our sales system peaks on the 5th through the 10th, we can run these two systems on the same box and resource limit each instance during the peak periods and let them run at 20% the rest of the month. This is typically done by installing two ORACLE_HOMEs in the /u01 directory. The accounting system goes into /u01/app/oracle/production/12.1.0/accounting and the sales system goes into /u01/app/oracle/production/12.1.0/sales. Both share the /u02 file system as well and put their data into /u02/app/oracle/oradata/12.1.0/accounting and /u02/app/oracle/oradata/12.1.0/sales. Backups are done to two different locations and the replication and redo logs are similarly replicated to different locations.

Having multiple ORACLE_HOMEs has been a way of solving this problem historically for years. The key drawback is that patching can get troublesome if specific options are used or installed. If, for example, both use ASM (automated storage management) you can't patch one database without patching ASM for both. This makes patch testing difficult on production systems because suddenly sales and accounting are tied together and upgrades have to be done at the same time.

Virtualization introduced a solution to this by allowing you to install different operating systems on the same computer and sublicense the software based on the virtual processors assigned to the application. You suddenly are able to separate the storage interfaces and operating system patches and treat these two systems as two separate systems running on the same box. Unfortunately, the way that the Oracle database is licensed has caused problems and tension with customers. The software does not contain a license key or hardware limit and will run on what is available. Virtualization engines like VMWare and HyperV allow you to soft partition the hardware and dynamically grow with demand. This is both good and bad. It is good because it makes it simpler to respond to increase workloads. It is bad because licensing is suddenly flexible and Oracle treats the maximum number of cores in the cluster as the high water mark that needs to be licensed. This is called soft partitioning. Operating systems like Solaris and AIX have hard partitions and virtualization engines like OracleVM and ZEN provide hard partitions. Customers have traditionally solved this by running an Oracle instance on a single socket or dual socket system to limit the core count. This typically means that the most critical data is running on the oldest and slowest hardware to limit price. Alternatively they run the database on a full blade and license all cores in this blade. This typically causes a system to be overlicensed and underutilized. The admin might limit the core count to 8 cores but there could be 32 cores in the blade and all 32 cores must be licensed. Using a virtualization engine to limit the resources between database instances is not necessarily practical and not fine enough resolution. Going with multiple ORACLE_HOME locations has been a growing trend since you have to license all of the cores based on current licensing policies.

Another big problem with the multiple ORACLE_HOME or multiple operating system approach is that you have multiple systems to manage and patch. If we use the 32 core system to run four instances of application databases we have four patches to make for the virtualization engine, the operating systems, and the databases. An optimum solution would be to run one operating system on all 32 cores and spread the four databases with one ORACLE_HOME across each and resource limit each instance so that they don't become a noisy neighbor for the other three. We can then use resource manager to assign shares to each instance and limit the processor, memory, and network bandwidth based on rules so that noisy neighbors don't stop us from getting our job done. We get our shares and can be the noisy neighbor if no one else is using resources.

With the 12c instance of the database, Oracle introduced an option called multi-tenant. Let's think of a company like SalesForce.com. They don't spin up a new instance for each company that they do business with. They don't install a new ORACLE_HOME for each company. They don't spin up a new operating system and install a new database instance for each company. This would not make economic sense. A five person company would have to spend about $3K/month with SalesForce to cover just the cost of the database license. On the flip side, custom code must be written to isolate user from company A from reading customer contact information from company B. A much simpler way would be to spin up a pluggable database for company A and another for company B. No custom code is required since the records for the two companies are stored in different directories and potentially different disk locations. If we go back and look at our partitioning blog entry we notice that we have our data stored in /u02/app/oracle/oradata/ORCL/PDB1. The ORCL directory is the location of our container database. This contains all of the configuration information for our database. We define our listener at this location. We create our RMAN backup scripts here. We define our security and do auditing at this level. Note that we have a PDB1 subdirectory under this. This is our pluggable database for company A. We would have a PDB2 for company B and the system01.dbf file in that directory is different from the system01.dbf file located in the PDB1 directory. This allows us to create unique users in both directories and not have a global name issue. With SalesForce all usernames must be unique because users are stored in a master database and must be unique. I can not, for example, create a user called backupadmin that allows users to log in to company A and backup the data set if there is a user defined by that same name for any other company world wide. This creates script issues and problems. We can't create a single backup script that works across all companies and must create a unique user and script for each company.

The main concept behind the multi-tenant option is to allow you to run more databases on the same box and reduce the amount of work required to support them. By putting common tasks like backup and restore at the container level, all pluggables on this system are backed up in a central location but separated by the pluggable container so that there is no data mingling. Data can be replicated quickly and easily without having to resort to backup and restore onto a new instance. The system global area (SGA) is common for the container database. Each pluggable container gets their own personal global area (PGA) that manages I/O buffers, compiled sql statements, and cached data.

Note that we have one redo log and undo log area. As changes are made they are copied to a secondary system. We don't have to configure Data Guard for each pluggable instance but for the container database. When we plug a instance into a container it inherits the properties of the container. If we had a container configured to be RAC enabled, all pluggables in the database instance would be RAC enabled. We can use the resource manager in the container database to limit the shares that each pluggable instance gets and reduce the noisy neighbor overlap that happens on a virtual machine configuration. We also reduce the patching, backup, and overall maintenance required to administer the database instance.

To create a pluggable instance we need to make sure that we have requested the High Performance or Extreme Performance Edition of the database. The Standard Edition and Enterprise Edition do not support multi-tenant. It is important to note that to get this same feature on Amazon you can not use RDS because they prohibit you from using this option. You must use IaaS and go with Amazon EC2 to get this feature to work. Microsoft Azure does not offer the Oracle database at the platform level so your only option is Azure Compute.

The pluggable creation is simple and can be done from the command line through sqlplus. The 12c Database Documentation details this process.

CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password;

or

CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password ROLES=(DBA);

or more complex

CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password
  STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
  DEFAULT TABLESPACE sales 
    DATAFILE '/disk1/oracle/dbs/salespdb/sales01.dbf' SIZE 250M AUTOEXTEND ON
  PATH_PREFIX = '/disk1/oracle/dbs/salespdb/'
  FILE_NAME_CONVERT = ('/disk1/oracle/dbs/pdbseed/', '/disk1/oracle/dbs/salespdb/');
Note that we can make the creation simple or define all of the options and file locations. In the last example we create the pluggable instance by cloning the existing pdbseed. In our example this would be located in /u02/app/oracle/oradata/ORCL. We would pull from the pdbseed directory and push into the salespdb directory. All three examples would do this but the third details all options and configurations.

When we create the instance from the sql plus command line, it could assume a PDB name for the file system. We might want to use the more complex configuration. When we executed this from the command line we got a long string of numbers for the directory name of our new pluggable instance called salespdb.

We could do the same thing through sql developer and have it guide us through the renaming steps. It prompts us for the new file name showing where the seed is coming from. We could have just as easily have cloned the salespdb and used it as our foundation rather than creating one from the pdbseed. We right click on the container database header and it prompts us to create, clone, or unplug a pluggable. If we select create we see the following sequence.

One thing that we did not talk about was economics. If you wanted to run multi-tenant on premise you need to purchase a database license at $47.5K per two processors and the multi-tenant option at $23K per two processors as well. This comes in at $60.5K for the license and $13,310 per year for support. Using our four year cost of ownership this comes in at $2,495 per month for the database license. The High Performance edition comes in at $4K per month. Along with this you get about $5K in additional features like diagnostics, tuning, partitioning, compression, and a few other features that we have not covered yet. If you are going to run these options on Amazon or Azure you will need to budget the $2.5K for the database license and more if you want the other features on top of the processor and storage costs for those cloud services. You should also budget the outgoing data charges that you do not have to pay for with the non-metered database service in the Oracle Cloud. Going with the multi-tenant option is cheaper than running the database on two servers and easier than running two ORACLE_HOME instances on the same machine. Going with the High Performance Edition gets you all of these options and offloads things like scale up, backup, initial configuration, and restart of services if a process fails.

In summary, multi-tenant is a good way of overloading services on a single server. The resource management features of the container allow us to dynamically change the allocation to a pluggable database and give more resources to instances that need it and limit noisy neighbors. With the High Performance edition and Extreme Performance Edition we get multi-tenant as a foundation for the service. Our primary interface to create a pluggable instance is either SQL Developer, Enterprise Manager, or sqlplus. We can easily clone an existing instance for a dev/test replica or export an instance and plug it into another system. We will look at this more in depth tomorrow.

database options - review and looking forward

Fri, 2016-06-17 02:07
For the past two weeks we have been looking at database as a service (DBaaS) offered as platform as a service (PaaS) on the Oracle Public Cloud. We started this journey on the 2nd of this month by looking at the differences between Standard Edition, Enterprise Edition, High Performance Edition, and Extreme Performance Edition. To quickly review, Standard Edition is the basic database with table encryption as the only option. This is a full feature database without the ability to replicate data with any tool other than copying files and RMAN backup. You can't do things like transportable table spaces, streams, Data Guard, or any other replication technologies to make a system more highly available. Enterprise Edition is a more full featured database that allows for data replication from the installation and comes with Advanced Security (TDE) as a basis for the installation. This edition does not come with Data Guard but does have the option for transportable tablespaces, external references, and ways of replicating data manually from the database that Standard Edition does not contain. We then looked at the High Performance Edition which comes with
  • Transparent Data Enctyption
  • Diagnostics
  • Tuning
  • Partitioning
  • Advanced Compression
  • Advanced Security
  • Data Guard
  • Label Security
  • Multitenant
  • Audit Vault
  • Database Vault
  • Real Application Testing
  • OLAP
  • Spatial and Graphics
We then looked at Extreme Performance Edition that contains all of the above plus
  • Active Data Guard
  • In Memory
  • Real Application Clusters (RAC)
  • RAC One
We then went into a simple description of each option and prepared for the following blogs that will go into more detail and code samples of not only what the options are but look at how to use them and try to tie them back to business benefits. Part of our description was a financial analysis of running a database in infrastructure as a service (IaaS) vs PaaS and the time and efficiency benefits that we get from PaaS over IaaS.

We wrapped up the week on the 3rd with a blog detailing what it takes to get a Windows desktop prepared to use a database in the cloud. The list of software is relatively generic and is not unique to Windows. We could just as easily have selected MacOSX or Linux but selected a Windows 2012 Server running in the Oracle Public Compute Cloud as IaaS. We did this primarily so that we would have a teaching platform that can be saved with a snapshot, reloaded for hands on classes, and accessible from a customer site to demonstrate cloud services. The software that we loaded on the Windows platform includes

  • To access cloud storage
    • Mozilla Firefox
    • RestClient extension for Firefox
    • Google Chrome
    • Postman extension for Chrome
    • CloudBerry for OpenStack (Windows only right now)
  • To access files in our instance
    • Putty
    • Filezilla
    • Cygwin (only needed on Windows)
  • To access our database instance
    • SQL Developer
    • Microsoft Visual C++ libraries (only needed on Windows)
We installed this on the Oracle Public Cloud because we have free accounts on this platform and we can keep them persistent. It would normally cost $150/month to keep this instance active if we purchased it as IaaS. We could just as easily have done this on Amazon EC2 or Microsoft Azure at a similar cost. We provisioned 30 GB of disk for the operating system and 10 GB for the binaries. We requested a 2 vCPU with 30 GB of RAM. If we were doing this on Amazon or Azure we probably would have gone for a smaller memory footprint but this is the base configuration for IaaS and Windows with 2 vCPUs in the Oracle Public Cloud. The idea is that a class of 15-30 people can log into this system with different user names and do minimal configuration to get started on workshops. We can refresh the users but not refresh the system for classes the following day or week. To provision this instance we went to the Oracle Cloud Marketplace to get a pre-configured Windows 2012 Server instance. We then downloaded the list of software and install them on the desktop.

On the 6th we dove into database partitioning to figure out that we can reduce storage costs and improve performance by fitting active data into memory rather than years or months of data that we typically throw away with a select statement. We talked about using partitioning to tier storage on premise and how this makes sense in the cloud but does not have as much impact as it does on premise. We talked about different partitioning strategies and how it can be beneficial to use tools like Enterprise Manager and the partition advisor to look at how you are accessing the data and how you might partition it to improve performance. On the 7th we looked at code samples for partitioning and talked about tableextents and file system storage. We talked about the drawbacks to Amazon RDS and how not having file system access, having to use custom system calls, and not having sys access to the database causes potential issues with partitioning. We walked through a range partition example where we segmented the data into dates and stored the different dates into different tablespaces.

On the 8th we focused on database compression. This in conjunction with partitioning allows us to take older data that we typically don't access and compress for query or historical storage. We talked about the different compression methodologies

  • using historic access patterns (heat map and ADO options)
  • using row compression (by analyzing update and insert operations as they occur)
  • file compression (duplicate file links and file compression of LOBS, BLOGS, and CLOBS)
  • backup data compression
  • Data Guard compression of redo logs before transmission
  • index compressions
  • network transmission compression of results to client systems
  • hybrid columnar compression (Exadata and ZFS only)
  • storage snapshot optimization (ZFS only)
We did not really dive into the code for compression but referred to a variety of books and blogs that have good code samples. We did look at the compression advisor and talked about how to use it to estimate how your mileage could potentially vary. On the 9th we dove into an Oracle by Example tutorial on compression and followed the example using DBaaS. The examples that we followed were for an 11g instance but could have been done in a 12c instance if we had the demo tables installed on the 12c instance.

On the 10th we focused on database tuning options and dove into how to use SQL Tuning Advisor. In the example that we used we referenced an external table that was not part of the select statement which caused an unnecessary table index and full table scan. The example we used was again for 11g to utilize the sample database that we have installed but could just as easily have worked with 12c. On the 13th we dove a little deeper into tuning with a focus on Enterprise Manager and the Performance Advisor utilities. We followed the Oracle by Example Tuning Tutorial to diagnose a performance problem with a sql statement.

On the 14th we looked at transparent data encryption (TDE) and how to enable and disable table compression in the cloud. We talked about the risks of not encrypting by default and tried to draw lesions from Target Corporate and how failure to protect credit card data with encryption led to job losses across the company.

On the 15th we looked at the backup and restore utilities in the cloud and how they differ from traditional RMAN utilities. You can use RMAN just like you do today and replicate your backup and restore as you do today but there are automation tools that monitor RMAN and kick off alternate processes if the backup fails. There are also tools to help restore for those unfamiliar with RMAN and the depths and details of this powerful package.

Today we are reviewing what we have done in the last week and a half and are looking forward to the next week or two. We are going to finish out the database options. On Monday we are going to dive into multi tenant and talk about pluggable databases. This discussion will probably spill over into Tuesday with an overview happening on Monday and code samples and demos on Tuesday. We will need to use a 12c database since this is a new feature that is specific to 12c only. We might split our code samples into using SQL Developer to clone and manage PDBs on Tuesday and cover the same functionality with Enterprise Manager on Wednesday. Following this discussion we will do a high level discussion on Data Guard and look at the change log and log replication strategies that can be used for physical and logical replication. The following days we will look at code samples and configurations from the command line, enterprise manager, and sql developer. We will look at what it will take to setup a primary on premise and standby in the cloud. We will also look at what is required to have both in the cloud and what it takes to flip primary and standby to emulate a failure or maintenance action then flip the two back.

Once we cover Data Guard we will be in a position to talk about real application testing. In essence Data Guard copies all of the writes that happen on the primary and replay them on the standby. Real Applicaiton Testing records the reads as well and replays the reads and writes to help measure performance differences between configurations. This is good for code change testing, patch testing, configuration change testing, and other compare/contrast changes to your production system in a safe environment.

Once we finish the high availability and data replication options we will dive into OLAP and Spatial options. OLAP reorganizes the data for data warehouse analysis and spatial allows you to run geographical select statements like show me all crimes that happened within a mile of this address. Both are optimizations on select statements to help optimize usage of the database in specific instances.

We will wrap up our coverage by looking at Audit Vault and Database Vault. Both of these options are additional levels of security that not only help us protect data but restrict and track access to data. Many financial and healthcare institutions require interfaces like this to show separation of duty as well as traceability to see who accessed what when.

Once we finish the High Performance Edition options we will dive into the Extreme Performance Edition options looking at Active Data Guard, In Memory, RAC and RAC One. Going through all of the options will probably take us through the month of June. We will probably look at the other PaaS options listed in cloud.oracle.com starting some time in July and see how they relate or differ from the DBaaS services that we are currently covering.

database option - backup and restore

Thu, 2016-06-16 02:07
Backup and recovery abilities are arguably the most critical skills required of a database administrator. Recovery Manager (RMAN) is Oracle’s standard backup and recovery tool; every Oracle DBA should be familiar with utilizing RMAN. Some DBAs use alternate tools since RMAN is an Oracle specific tool to backup data in a database. Alternatives include Veritas Backup Exec, Comvault Sympana, Legato Networker, EMC and NetApp tools, and other packages. I am not going to list books and software packages in this blog. When I did a search on Safari Books search for rman we get 9 books published in 2016, 16 in 2015, and 20 in 2014. There are also a ton of blogs so I suggest that you go with your favorite blog and search for rman or backup. There are hundreds of different ways to backup a database and restore the database as well as optimize how much space the database takes up in cold storage.

The important things to consider when you look at backup and recovery are

  • full or incremental backups
  • backing up to disk, tape, or cloud
  • replicating data to another site with disk mirroring, Data Guard, or Golden Gate
  • hot backup or cold backup along with middleware and file system backup synchronization
  • recovery point objective and recovery time objective
  • compression, deduplication, and encryption of data at rest
  • backup windows and restore windows

It is important to note that when you purchase DBaaS, independent of any edition, you get backup done for you based on the options you select at creation. When you create a database you can opt for no backup, local backup, and full backups. The no backup can be used for development and test instances. We might just want a sandbox to play in and don't care about keeping this data so that we can restore. If we loose the data for any reason we can recreate it from our production system. When you select local backups you get incremental backups daily at 2am and a full backup Sunday morning at 2am. This gives you a seven day window for recovery so that you can restore data back to your last full backup with daily incrementals. These backups go to the /u03 file system on the instance that you create. Nothing is copied off the instance that you create so a complete failure of the system could result in potential data loss. The full backup does an incremental to /u03 daily and a full backup Sunday morning at 2am to /u03 as well. Prior to the full backup, the backup file is copied to the Cloud Object Storage Container that you created prior to creating the database. When you created the database you specify the days that you want to retain backups. If, for example, you ask for a 90 day backup you get 13 full backups copied to the object storage. If you have a Java Cloud Service connected to this database, the Java configuration and war files are also copied to this same area. The backup is automatically done for you and can be reconfigured and done manually using the cloud specific commands. Refer to the Using Oracle Database Cloud - Database as a Service Documentation (chapter 6) to understand how to backup using the cloud commands to keep backups in sync with the automated utilities rather than doing an rman manually.

You can generate a backup before you make a change to the database with the following command

sudo /var/opt/oracle/bkup_api/bkup_api bkup_start
This command must be executed from the opc account and not the oracle account because it needs root roles to store data and update the logs associated with cron jobs. To restore the database from the last backup you execute the following command
sudo dbaascli orec --args -latest
You can list the database backups that exist with
sudo dbaascli orec --args -list
To restore from a specific backup you execute
sudo dbaascli orec --args -pitr backup-tag
where backup-tag is the name listed with the -list command.

The dbaascli command shuts down the database, extracts and restores configuration files, prepares for recovery, performs the recovery, and restarts the database instance after recovery. You can use the rman utility to restore individual tables or tablespaces as usual but tools exist to manage the rman repository that are accessible to do scheduled backups, full backups, and restores.

One of the nice features of RMAN is that you can duplicate a database to an alternate location and restore from an alternate location. For example, you can take a backup of your on premise database and write to the Oracle Object Cloud Service. You can then create a database in the cloud using the DBaaS and load the data into this database from your backup in cloud object storage. You can also provision an on premise database, execute replication commands to configure the second database as a Data Guard replica, initiate the backup of your on premise instance, initiate the restore on your cloud instance, and configure Data Guard to maintain the log shipping, and kick off the Data Guard service on both instances. We will cover this in an upcoming blog.

Using DBaaS does get rid of the discussion of snap mirror backups. This is where you take the tablespace.dbf files, shutdown the database, copy these files to another database, and ingest the dbf file into the second database. Many users like to use this method for backup and recovery because it works for MySQL and Postgress. It is not a recommended backup and restore mechanism for an Oracle database. Synchronization issues can result if changes are being made and data is partitioned across multiple dbf files. Using tools like EMC or NetApp mirroring software work well for VMWare to clone data between data centers. This technology does not work to the cloud and data replication is more difficult if the source and target are from different hardware vendors. The Oracle database also has problems ingesting this data at times and fails to startup from mirror instances if file consistency is not maintained between the system.dbf, user.dbf, and other tablespace files.

Data compression is also a tricky issue. The Oracle database has a compression option that it uses to compress data to minimize disk usage in the tablespace files. If you take this compressed dbf file and then try to again compress it, it typically just consumes excess processor cycles and takes upto eight times as long to restore the data once it is doubly compressed. It is important to not only look at your backup window but your restore window. It might take an hour to backup and compress your data but it might take upto 8 hours to restore it. One of the key performance benefits that Oracle brings to the table is using ZFS storage as a back end for a database. Since the database knows what the ZFS storage appliance can and can't do, it can offload the compression processing and algorithms to the storage appliance. Unfortunately, this does not work for other storage vendors. The cloud database instance uses this to it's advantage so data is automatically compressed when copies are done to the object storage. The compression does not take processing power from the database engine but is offloaded to the storage engine.

You can customize the backup definition and parameters. All of the files are located in /var/opt/oracle/ocde/assistants/bkup/ and the bkup.cfg file defines how backup operates. It is important to note that there is a different command line set of tools if your database is a RAC configuration since you have to reconfigure files on both systems that comprise your cluster but they both use this directory and file to configure the backup process, timing, and procedures.

In summary, database backup is part of the database as a service on all levels. The foundation tool that is used is RMAN. There are command line api tools that allow you to backup and restore without having to learn RMAN. If you want to reconfigure and drill down into the depths and bowels of RMAN you can do this. The backups are automatically encrypted using your wallet that we talked about a couple of days ago. You can also use Enterprise Manager to run RMAN for your cloud service just like you do with your on premise database instances. The important thing to remember is that you don't need to learn RMAN unless you want to. If you deploy a database on infrastructure as a service you have to learn RMAN because it is not automated for you or configured. If you use Amazon RDS, backups are done by doing a disk clone to another zone. You can not setup Data Guard replication to the RDS instance because you don't have file system access to copy the change logs from your on premise database to the RDS storage. You can do this with EC2 and S3 but you loose the automated configurations and services that you get with database as a service. You will also need to purchase Advanced Security to do secure backups to IaaS instances from all cloud vendors but not with DBaaS or PaaS from Oracle.

database option - diagnostics

Wed, 2016-06-15 02:07
Keeping a database tuned is a full time job. Automating some of these tasks helps a DBA support more databases and reduce the time required to generate the data. Automatic report generation is a good way of getting these reports. One of the questions I constantly get asked is what is included with PaaS or DBaaS and what is done for me. With DBaaS, database tuning and diagnostics are not part of the services provided. The DBA still needs to look for processes that are holding locks. The DBA still needs to look for run away sql statements. The DBA still needs to look for alternate execution plans and sql tuning to make the database run faster. The Diagnostics Pack is a key tool to help with this. The services that are included with DBaaS include database and operating system patching, making sure that the database is restarted if it stops (unless you issue a shutdown command), and performs automated backups that you can tweak and tune in frequency and amount of data stored. Tools like ADDM, ASH, and AWR are still required by the DBA and can be accessed from the command line through sqlplus, using SQL Developer, or Enterprise Manager. In 10g, many diagnostic tools like ASH and AWR were embedded into the database. In 11g they were automated to collect the data into a central location. In 12c reports were automated so that DBAs did not need to schedule the jobs and generate reports late at night to look at in the morning. Many of these features started in Enterprise Manager but got migrated into the database. There was some controversy with the 10g release because it did impact performance compared to the 9i release but that seems to have gone away with the 11g and 12c releases. An overall architecture of the performance collection can be seen in the diagram below.

The key features to the Diagnostic Pack for the database include

  • Active Session History (ASH)
  • Automated Workload Repository (AWR)
  • Automatic Database Diagnostic Monitor (ADDM)
  • Enterprise Manager Performance reporting
  • SQL Developer Performance reporting

More information on all of these topics can be found in a variety of locations. Most of the information in this blog can be found at

ASH

ASH statistics are enhanced to provide row-level activity information for each captured SQL statement. This information can be used to identify which part of the SQL execution contributed most significantly to the SQL elapsed time. The ASH views, accessible to DBAs, provide historical information about the active sessions in the database.

AWR

The Automated Workload Repository (AWR) reports provide baseline statistics for the database and show the database performance during specified intervals. A baseline contains performance data from a specific time period that is preserved for comparison with other similar workload periods when performance problems occur. Several types of baselines are available in Oracle Database: fixed baselines, moving window baselines, and baseline templates.

ADDM

DBAs and developers can use ADDM to analyze the database and generate recommendations for performance tuning. In Oracle 11g, ADDM was enhanced to perform analysis on database clusters on various levels of granularity (such as database cluster, database instance, or specific targets) over any specified time period.

You can access ADDM through SQL Developer or Enterprise Manager. To access these functions you must first enable the Diagnostics Pack which allows you access to the reports.

You can manually run the ADDM report with a command line

@?rdbms/admin/addmrpt.sql
If you look at SQL Developer and go to the DBA navigation link and expand the database for Performance you can see the AWR and ADDM reports. Expanding on these links shows you the various reports. For the ADDM, for example, you can quickly see if there is a recommendation or not and drill down into the recommendation.

If we click on one of the finding with a Yes in the recommendation column we can look at the report and recommendations that it has. For the example we found it had two suggestions for tuning. Below are samples of this report and the two recommendations.

We can look at similar information from Enterprise Manager by navigating to the Performance page and selecting the report that we want.

Typical AWR report output usually contains an incredible amount of information about an Oracle database’s application workload behavior. When a database instance is suffering from a gc buffer busy wait event during the time period chosen for the AWR report, however, that event will usually surface as one of the Top 5 Timed Events

With AWR you can create a baseline and look at spot performance or variation from a baseline. Page 21 of Diagnostic Pack Presentation does a good job of describing how to read and understand an AWR Report. The blog Bash DBA does a good job of walking through an AWR and looking for issues and problems in a system.

In summary, we are not going to dive deep into AWR and ADDM diagnostics. Knowing how to do this differentiates a high paid DBA from a junior DBA. There are classes through Oracle Education - 12c Performance Management and Tuning and other vendors that teach you how to understand this option as well as the books we mentioned above and certification exams to help show that you know this material. It is important to note that all of these tools work with platform as a service (Oracle and Amazon RDS) as well as infrastructure as a service and on-premise installations. The key difference is that the diagnostic and tuning tools are bundled with the High Performance and Extreme Performance Editions. For IaaS and on-premise you need to purchase a perpetual license that we discussed a few blogs ago.

database options - advanced security

Tue, 2016-06-14 02:07
Advanced Security and Transparent Data Encryption (TDE) stops would-be attackers from bypassing the database and reading sensitive information from storage by enforcing data-at-rest encryption in the database layer. Earlier when we talked about partitioning and compression we talked about tablespace files and how to manage them. If these files are stored in clear text, anyone who can access our file system could theoretically read the dbf file and do a clear text search. If, for example, we have a credit card number "1234 5678 9012 3456". We could find this string with a strings, grep, or od statement (octal dump to show text data). If we don't know the credit card number we can just do a dump on the data and see the values stored in the database. With enough trial and error we can figure out what the structure of the database might be and correlate names to credit card information. By default, all data stored in the cloud has TDE enabled. This is done in the $ORACLE_HOME/network/admin/sqlnet.ora file. If you look at this this file, you will see that the ENCRYPTION_WALLET_LOCATION is defined as well as the SQLNET.ENCRYPTION_TYPES_SERVER is defined. When the database is created, a wallet is generated based on your ssh keys allowing you to access your data. In the database that we created we have the wallet file location in /u01/app/oracle/admin/ORCL/tde_wallet. There is a file called cwallet.sso that is enabled anytime someone connects to the database through port 1521 or through the sqlplus command. If we rename this file to something else, we can connect to the database and create clear text files. Note that it is not recommended that you do this but we are doing this to highlight first the differences between Iaas and PaaS as well as the need for data encryption in the cloud. With a database installation on top of compute as is done with EC2, Azure Compute, and Oracle Compute, we have to enable TDE, configure the wallet, configure the cwallet.sso. With PaaS, this is all done for you and you can manage the keystore and rotate key access.

Note that it is not recommended that you execute these commands. They will desecure your database and allow for clear text storage and transmission of data across the internet. We are doing this as an example.

cat $ORACLE_HOME/network/admin/sqlnet.ora
cd /u01/app/oracle/admin/ORCL/tde_wallet
ls
This should allow you to see the cwallet.so file which enables automatic wallet connection upon login. If we want to change encryption we can first look at the parameter encrypt_new_tablespaces and see that it is set to CLOUD_ONLY which encrypts everything. We want to change this to DDL which says that we only encrypt if we tell it to. We first want to create a tablespace and a banking user with encryption turned on. This is done with
sqlplus / as sysdba
alter session set container=PDB1;
drop tablespace banking including contents and datafiles;
create tablespace banking datafile '/u02/app/oracle/oradata/ORCL/PDB1/banking.dbf' size 20m;
We then create a banking user as well as a paas_dba user. One is used to create an encrypted table and the other is to create a clear text table
drop user banking;
create user banking identified by "PaasBundle_1" default tablespace banking; 
grant create session to banking;
grant unlimited tablespace to banking;
drop user paas_dba;
create user paas_dba identified by "PaasBundle_1";
grant create session to paas_dba;
grant dba to paas_dba;

We now connect to the PDB1 as the banking user and create a table in our encrypted tablespace

connect banking/PaasBundle_1@PDB1
create table banking_customers (first_name varchar(20), last_name varchar(20), ccn varchar(20)) tablespace banking;
insert into banking_customers values('Mike','Anderson','5421-5424-1451-5340');
insert into banking_customers values('Jon','Hewell','5325-8942-5653-0031');
commit;
alter system flush bufffer_cache;
select ccn from banking_customers;
This should create a table with two entries. The table will be encrypted and stored in the banking.dbf file. If we do a string search from this file we will not find the credit card number starting with 5421. Now that we have an encrypted table created we need to reconnect to the database and disable encryption on new tables. To do this we change the parameter from CLOUD_ONLY to DDL as sysdba.
sqlplus / as sysdba
show parameter encrypt_new_tablespaces;
alter system set encrypt_new_tablespaces=DDL SCOPE=BOTH;
We can now create a new tablespace and the contents are only encrypted if we pass in the encrypt statement with the create statement. The tablespace will not be encrypted by default. We do this operation as paas_dba who has dba rights to create a tablespace and table.
connect paas_dba/PaasBundle_1@PDB1;
drop tablespace bankingCLEAR including contents and datafiles;
create tablespace bankingCLEAR datafile '/u02/app/oracle/oradata/ORCL/PDB1/bankingCLEAR.dbf' size 20m;
create table banking_Clearcustomers tablespace bankingCLEAR as select * from banking_customers;
select ccn from banking_Clearcustomers;
We should get back two entries from both select statements and see two credit card numbers. We can then exit sqlplus and look at the banking.dbf and bankingCLEAR.dbf files to see if we can lift credit cards. By executing the
strings bankingCLEAR.dbf | grep 5421
strings banking.dbf | grep 5421
we see that we get the credit card number from the bankingCLEAR.dbf file. The data is inserted clear text. It is important to remember that all data should be encrypted in motion and at rest. We need to change the parameter back to CLOUD_ONLY for the encrypt_new_tablespaces moving forward. By default we connect to the data using the encryption wallet. We can disable this as well as turning off default encryption.

In summary, we have looked at what it takes to encrypt data at rest in the cloud. By default it is turned on and we don't have to do anything with platform as a service. If we are using infrastructure as a service we need to purchase the Advanced Security option, turn on encrypting tablespaces bu changing a parameter, enable the wallet to our login, and install keys for the wallet. Platform as a service provides levels typically above and beyond what most customers have in their data center. The recent credit card loss that happened at Target a few years ago happened because they owned the Advanced Security option for the database but did not turn on the feature. An outside consultant (working on something non-it related) got access to a shared storage and pulled the dbf files onto a USB drive. They were able to get thousands of credit cards from the data center costing the CIO and IT staff their jobs. Today we learned how to turn off TDE in the cloud to illustrate what it takes to turn it on in your data center and we looked at the simplicity of pulling data from a dbf file if we know the data we are looking for. We could just as easily have just looked for number patterns and peoples names and correlated the two as valid credit card numbers.

I would like to thank the GSE team at Oracle for doing 90% of the work on this blog. I liberally hijacked the demo scripts and most of the content from demo.oracle.com, PaaS - Data Management (Solutions Demo) by Brijesh Karmakar. The GSE team creates demo environments and scripts for specific products. This demo is an Oracle internal demo and can be requested from your pre-sales consultant in your area. I took the demo code from the Database Vault and Encryption_demo_script written on 08-Jun-2016. I have to admit that looking for demo scripts on demo.oracle.com and searching for TDE was very opportune given that I wrote this blog on the 9th and it was published on the 8th.

database option - tuning part 2

Mon, 2016-06-13 02:07
In our last entry we looked at the results of a sql tuning advisor. We used SQL Developer to execute our code and create a tuning advisory for the code that we executed. We could have gone through Enterprise Manager and done the same thing but done this historically rather on live data. In this blog entry we will analyze the same results using the Enterprise Manager Express that comes with the database as a service in the Oracle Cloud. To connect to this service we need to first open up the network ports to enable connection to port 1158. This is done through the database console or we could do this with ssh tunneling of port 1158 to our database target ip address.

Once we have access to port 1158 we can connect to the Enterprise Manager Express by going to the ip address of our server, in this instance 129.152.134.189 which we got from the database console, and connect to https://129.152.134.189:1158/em. Note that we might get a security exception since the certificate is self signed. We need to add an exception and connect to this service. When we are prompted for a login we connect as sys with sysdba rights. Note that we can not do this on Amazon RDS since we do not have sys access to the database in this service.

When we click on the Performance link at the top of the screen we can look at the overall performance of our system and drill down in to reports to get more information.

If we scroll down to the bottom we see a link called Advisor Central. We can follow this link and look at all of the tuning advisors that have been run and examine the results.

We can select a previously run tuning advisor and look at the recommendations that we did from SQL Developer. When we dive into the report we get a little different style report.

Note that the SQL profile recommends a 19.8% savings if we change the profile. If we click on the Recommendations and expand the information as a table rather than a graph we see that the pk_dept reference takes up a good amount of time and if we could get rid of it since it is not referenced it will speed up the select statements. If we click on the compare explain plans we can see how much of a speed up we will get if we implement the new plan. What I don't see from this is the recommendation to drop the dept d reference that we got from SQL Developer.

Note that the recommendation does state "Consider removing the disconnected table or view from this statement or add a join condition which refers to it" but does not specifically recommend removing dept d from the select statement as is done in SQL Developer.

If we wanted to expand upon use of the tuning advisor we could follow along the Oracle by Example Tuning Tutorial and look at how to initiate tuning advisories through Enterprise Manager. The first thing done in this tutorial is to initiate nightly tuning tasks by going into the server and enabling the Automated Maintenance Tasks. You first click the Configure button then click Configure the Automatic SQL Tuning button. After you change the Automatic Configuration of SQL Profiles to yes and click Apply you have a checkbox window to select the dates to run the tuning tasks.

Once we have this defined we can execute the following code as sys

set echo on

drop user ast cascade;
create user ast identified by ast;
grant dba to ast;
alter system flush shared_pool;
--
-- Turn off AUTOTASK
--
alter system set "_enable_automatic_maintenance"=0;
--
-- Clear out old executions of auto-sqltune
--
exec dbms_sqltune.reset_tuning_task('SYS_AUTO_SQL_TUNING_TASK');
--
-- Drop any profiles on AST queries
--
declare
  cursor prof_names is
    select name from dba_sql_profiles where sql_text like '%AST%';
begin
  for prof_rec in prof_names loop
    dbms_sqltune.drop_sql_profile(prof_rec.name);
  end loop;
end;
/
This creates a user ast. I recommend changing the password to something more complex.

We can then run a series of malformed select statements to generate some synthetic load to report upon and correct. Note that we do this as the ast user and not sys.

set echo off

select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;
select /*+ USE_NL(s c) FULL(s) FULL(c) AST */ c.cust_id, sum(s.quantity_sold) from sh.sales s, sh.customers c where s.cust_id = c.cust_id and c.cust_id < 2 group by c.cust_id;

Once we have the workload created we can kick off the sql tuning advisor with some different code.

set echo on

exec dbms_workload_repository.create_snapshot;

variable window varchar2(20);

begin
 select upper(to_char(sysdate,'fmday'))||'_WINDOW' into :window from dual;
end;
/

print window;

--
-- Open the corresponding maintenance window, but with other clients disabled
--

alter system set "_enable_automatic_maintenance"=1
/

exec dbms_auto_task_admin.disable( -
  'auto optimizer stats collection', null, :window);

exec dbms_auto_task_admin.disable( -
  'auto space advisor', null, :window);

exec dbms_scheduler.open_window(:window, null, true);

--
-- Close the maintenance window when sqltune is done
--

exec dbms_lock.sleep(60);

declare
  running number;
begin

  loop
    select count(*)
    into   running
    from   dba_advisor_executions
    where  task_name = 'SYS_AUTO_SQL_TUNING_TASK' and
           status = 'EXECUTING';

    if (running = 0) then
      exit;
    end if;

    dbms_lock.sleep(60);
  end loop;

  dbms_scheduler.close_window(:window);

end;
/

alter system set "_enable_automatic_maintenance"=0
/

--
-- Re-enable the other guys so they look like they are enabled in EM.
-- Still they will be disabled because we have set the underscore.
--

exec dbms_auto_task_admin.enable( -
  'auto optimizer stats collection', null, :window);

exec dbms_auto_task_admin.enable( -
  'auto space advisor', null, :window);

Note that this executes with some errors but still generates a good sql tuning advisor report. If we look back at the Advisor Central we can dive into the report and see what happened.

We can get an 8 second speedup by reformatting the sql select statements. This might or might not be worthy of tuning based on how many times we execute the code.

In summary, we have alternate ways of looking at sql tuning as well as a say of looking at historic data. We turned on automatic tuning reports which does consume more resources but if we have extra cpu cycles we can benefit from the reports. The Enterprise Manager Express that comes with database as a service is a very powerful tool. It is not a centralized utility like a centralized Enterprise Manager but can be used to automate and record reports for a single database. This service is only installed with the platform as a service and must be manually added and configured if you install your own database manually on top of infrastructure as a service. Having this common management interface is a huge benefit to DBAs who are asked to manage and maintain instances in the cloud. The Enterprise Manager used in the cloud is the exact same version that is used for an on-premise system. If you choose to install and configure a central Enterprise Manager server you can attach to instances in your data center as well as instances in the cloud. The only requirement is that you have file level access and sys/root access to install the agent.

database option - tuning

Fri, 2016-06-10 02:07
Today we are going to look at using the diagnostics and tuning package that comes with the High Performance and Extreme Performance Editions of the database. We do not get these options with the Standard Edition or Enterprise Edition and if we use Amazon RDS, EC2, Oracle IaaS, or Microsoft Azure Compute to use the tuning option we must bring along a license for the options. Diagnostics are licensed at $150 per named user or $7,500 per processor. This correlates to $294 per processor per month. Options like the SQL Tuning Advisor and Automatic SQL Tuning are part of the Tuning pack option. Tuning pack is $100 per named user or $5,000 per processor. This comes in at $196 per processor per month if we use the four year amortization that we talked about last week.

There are three ways to look at the SQL Tuning Advisor. We can use Enterprise Manager in a central site and analyze historic data from days, weeks, and months back. Unfortunately, we can not use this in conjunction with Amazon RDS. We can use the Enterprise Manager Express which is part of the database and gives you three hours of history of database performance. Again, we can not use this in conjunction with Amazon RDS. These features are disabled and turned off as part of the Amazon installation. We can use SQL Developer to connect to the database on all platforms. This allows us to pull down real time diagnostics and look at live database performance. We will go through an Oracle by Example SQL Tuning Advisor Tutorial that details how to enable and use the tuning advisor packs. The database version that we will be using is the 11g version of the database. These same steps should work with 12c because the features have not changed and SQL Developer knows what to do between the two versions of the database and present a common user interface to do SQL Tuning.

The first step that we have to do is find out the ip address of our 11g database. We do this by going to the database console and looking at our instance detail.

We then create a connection to the database with SQL Developer. This is done first as the sys user as sysdba connecting to the ORCL instance at the ip address of the database. We can verify that we are connected to a High Performance Edition by issuing a select statement against the v$version table.

select * from v$version;

Before we can execute step 8 in the Tuning Advisor Tutorial we must enable the user scott and set a password for the account. To do this we expand the Other Users selection at the bottom left of the screen, find the user scott, and enable the account while setting the password.

We can now connect to the 11g instance and give user scott permission to attach to the sql resources with the commands

grant advisor to scott;
grant administer sql tuning set to scott;

We then clear the existing statistics to make sure we are not looking at old artifacts but what we are going to execute. This is done by exeucting

exec DBMS_STATS.DELETE_SCHEMA_STATS ('scott');

At this point we switch over to the user scott and execute a select statement

select sum(e.sal), avg(e.sal), count(1), e.deptno from dept d, emp e group by e.deptno order by e.deptno;

We can launch the SQL Tuning Advisor from the icon at the top of the screen. This opens a new tab next to the resulting output from the select statement.

The output from the tuning advisor has four parts. We can look at the statistics that were gathered, look at suggested indexes, sql profile, and restructuring statement recommendations. The index output did not say anything but the other three had recommendations.

The restructuring statement suggests that we remove the dept d definition since we really are not using it in the select statement. We then execute the following modified command

select sum(e.sal), avg(e.sal), count(1), e.deptno from emp e group by e.deptno order by e.deptno;

When we rerun the command without the dept d in the select statement we get a clean output from the SQL Advisor.

In summary, we can use Enterprise Manager, Enterprise Manager Express, or SQL Developer to run the tuning advisor. We walked through a simple example of how to do this with SQL Developer on a single select statement. We walked through the SQL Developer because it works on all cloud platforms and the Enterprise Manager solutions do not work well with Amazon RDS. With these tools we can dive into SQL performance issues, tune the database, and optimize the cloud system to utilize fewer resources and cost us less money. If we can reduce the processor count by a couple of processors that more than pays for the cost of the High Performance Edition incremental cost over the Enterprise Edition.

database option - compression part 2

Thu, 2016-06-09 02:07
Yesterday we looked at the different compression options that are available for a database. Today we are going to walk through an example. The example comes from Oracle by Example - Compression. This is a hands on tutorial that has you execute code in an 11g database. Note that you must create this database as a High Performance or Extreme Performance database. If you create a Standard Edition or Enterprise Edition the execution will fail with an option not available error as we saw with partitioning a couple of days ago.

To start, we create an 11g database in the Oracle Public Cloud. We create the instance, wait an hour or so, change the network configuration to open port 1521 to the public, and connect using sys as sysdba to the instance. We are going to use SQL Developer in our Windows 2012 instance to make the connection. To get the connection information, we the database console and get the ip address of the instance.

We then go to our sqldeveloper tool and add this database connection. We can use ssh tunneling or open port 1521 to the world to make the connection.

The first step that we are told to do is to execute the setup.sql file available via the tutorial. We are not going to execute this program but do everything by hand through sql developer. The purpose of this script is to enable the user sh, set a password, and grant privileges to the user. We can do this from SQL Developer. The code that it recommends using is

connect / as sysdba
set echo on
alter user sh identified by sh account unlock;
grant create tablespace to sh;
grant drop tablespace to sh;
First, we don't want to use such a simple password. We change this and set it to something a little more secure. We select the database instance, in our example it is prs11gHP where we are connected as the sys user. We select other Users..., the user sh, and edit the entry. When the screen comes up to edit the user, we enable the account, set the password, grant create tablespace and drop tablespace rights to the user and apply. This effectively executes the script shown above.

At this point, we have a user that can create and drop tables. We now want to load the create_sales_tbls.sql code from the tutorial.

The create script first, drops the existing tables. This might generate an error because the table does not exist. This error is not significant and won't stop everything from executing. We then create a non-compressed and a compressed table by selecting from the demo sales table that exists if you installed the demo database during your install.

drop table sales_nocompress purge
/
drop table sales_compress purge
/
set echo on
set timing on
create table sales_nocompress
as select * from sales
/

create table sales_compress compress for all operations
as select * from sales where 1=0
/

select count(*)
from sales_compress
/
Note that the two create statements should create a table of the same size. What we see is that the creation of the first table takes just over 4 seconds because we pull in the sales table information. The second creation does not take as long because the data is in cache and the where clause fails for all select operations.

When we do the select, the table size should be zero based on the where clause. We then to an insert into the table to create a table of the same size. This is done by executing

@oltp_insert

set timing off
select count(*) from sales_compress
/
select count(*) from sales_nocompress
/
This executes the oltp_insert.sql code then compares the counts of the two tables to make sure they contain the same number of records. The code that is executed in the insert script is
SQL> set timing on
SQL> declare

  commit_after integer := 0 ;
  loop_variable integer ;

  cursor c_sales is
  select prod_id
  , cust_id
  , time_id
  , channel_id
  , promo_id
  , quantity_sold
  , amount_sold
  from sales ;

begin

  for r_sales in c_sales
  loop

    if commit_after = 0
    then

      loop_variable := 0 ;

      commit_after := round(dbms_random.value(1,1)) ;

    end if ;

    insert into sales_compress
    (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold)
    values
    ( r_sales.prod_id
    , r_sales.cust_id
    , r_sales.time_id
    , r_sales.channel_id
    , r_sales.promo_id
    , r_sales.quantity_sold
    , r_sales.amount_sold
    ) ;

    if loop_variable = commit_after
    then
      commit ;
      commit_after := 0 ;
    end if ;

    loop_variable := loop_variable + 1 ;

  end loop ;

end ;
/
We are not going to go through this code but it does return the same amount of entries as the uncompressed table. The values that are inserted are pulled from the sales table and inserted into the compressed table. Note that we are using the basic compression since we did not state any compress methodology when we created the table.

We can execute the examine_storage.sql script to see that the compressed storage takes up about half the storage as the uncompressed table. We can also see that the table is enabled for oltp compression by looking at the parameters of the table from a select statement.

We can also look at the select time differences by reading all of the data from the compressed and uncompressed tables. Note that the compressed table takes about 3/4 of the time that the uncompressed takes to execute.

In summary, we were able to create an 11g database, create a table that is compressed and non-compressed and look at the relative size and timing on retrieving data from the table. We can experiment with this data and grow the table size to see if we still get the same improvements as the table gets larger. We can try different compression algorithms to see if it effects performance or compression ratios. We have done all of this in a database as a service public cloud instance. The only tools that we needed was a SQL Developer connection and an Oracle Cloud account. We could have done with with Amazon RDS as well as EC2 and Microsoft Azure Compute. The key difference is that this experiment took about two hours to execute and we only consumed about $15 to learn and play with compression on 11g (or 12c) given that a low memory option for the database is only $6.720 per OCPU per hour. With the pay as you go option we burn less than $15 and turn off the service. We could have uploaded our own data sets into the database instance and played with the compression advisor in a sandbox and not effected our production environment. If we were using database backup as a service we could have restored a single table from our backup and play with the compression variations and compression advisor.

database options - compression

Wed, 2016-06-08 02:07
A natural follow on to database partitioning is database compression. With partitioning we wanted to split everything into buckets based on how frequently it is used and minimize the more used stuff so that it would fit into memory. The older stuff that we don't access that frequently can be put on slower and lower cost storage. In this blog we are going to look at different techniques to use the cheaper storage event more. Since we don't access this data very frequently and most of the time when we access it we only need to read it and not write to it, we should be able to take advantages of common data and compress the information to consume less storage. If, for example, we have the census data that we are storing and we want to store city and state information we can take advantage of not having Punxsutawney, Pennsylvania stored 5900 times based on the current population. If we stored a copy of this roughly 6000 time it would take up 6000 times 12 bytes for the city and 6000 times 12 bytes for the state. We would also store 15767 as the zip code roughly 6000 times consuming 6000 times 9 bytes. If we could create a secondary table that contains Punxsutawney, Pennsylvania 15767 and correlate it to the hexadecimal value 2e, we could store 2e for the city, state, and zip code thus consuming one byte each rather than 12, 12, and 9 bytes. We effectively save 180,000 bytes by doing a replacement value rather than storing the long strings multiple times. This is effectively the way that hybrid columnar compression works.

Compression can be done at a variety of levels and locations. Disk vendors for years have touted compression in place on storage to consume less space. Compression has been used in a variety of industries. Audio compression, for example, takes recorded audio and under samples the changes in volume and pitch and only records only 8,000 samples per second since the ear can not really hear changes faster than that. These changes are then compressed and stored in an mp3 or avi format. Programs know how to take the mp3 format and rebuild the 8k sample and drive a speaker to estimate the sound that was originally created. Some people can hear the differences and still want to listen to music recorded on reel to reel tape or vinyl because the fidelity is better than CD-ROM or DVD. Videos do the same thing by compressing a large number of bits on a screen and break it into squares on the screen. Only the squares that are changing are transmitted rather than sending all of the data across the whole screen and the blocks that did not change are redisplayed rather than being retransmitted thirty times a second. This allows for video distribution of movies and video recordings across the internet and storage on a DVD rather than recording all of the data all of the time.

Generically compressing data for a database can be complex and if done properly works well. It can also be done very poorly and cause performance problems and issues when reading back the data. Let's take the census data that we talked about earlier. If we store the data as bytes it will consume 198K of space on the disk. If we use the compression ratio that we talked about we will consume roughly 20K of data. This gives us a 10x compression ratio and saves us a significant amount of space on the disk. If the disk sub-system does this compression for us we write 198K of data to the disk, it consumes 20K of storage on the spindles, but when we read it back it has to be rehydrated and we transfer 198K back to the processor and consume 198K of memory to hold the rehydrated data. If the database knew what the compression algorithm and compressed the data initially in memory it would only transmit 20K to the disk, store 20K on the spindles, read 20K back from the disk, and consume 20K of memory to hold the data. This might not seem significant but if we are reading the data across a 2.5 G/second SCSI connection it takes 80ms to read the data rather than 8ms. This 72ms difference can be significant if we have to repeat this a few thousand times. It can also be significant if we have a 1 GigE network connection rather than a direct attached disk. The transfer time jumps to 200ms by moving the data from an attached disk to an nfs or smb mounted disk. We see performance problems like this with database backups to third party storage solutions like Data Domain. If you take a database backup and copy it to a Data Domain solution you get the 10x compression and the backup takes roughly an hour. You have to estimate that it will take seven to eight times the time to rehydrate the data so a restore will take 7-8 hours to recover your database.

The recommended solution is to use compression inside the database rather than third party compression solutions that are designed to compress backups, home directories, and email attachments. Oracle offers Advanced Compressions options for information stored in the database. If you look at the 12c Advanced Compression Data Sheet you will notice that there are a variety options available for compression. You can compress

  • using historic access patterns (heat map and ADO options)
  • using row compression (by analyzing update and insert operations as they occur)
  • file compression (duplicate file links and file compression of LOBS, BLOGS, and CLOBS)
  • backup data compression
  • Data Guard compression of redo logs before transmission
  • index compressions
  • network transmission compression of results to client systems
  • hybrid columnar compression (Exadata and ZFS only)
  • storage snapshot optimization (ZFS only)

Heat Map Compression

At the segment level, Heat Map tracks the timestamps of the most recent modification and query of each table and partition in the database. At the block level, Heat Map tracks the most recent modification timestamp. These timestamps are used by Automatic Data Optimization to define compression and storage policies which will be automatically maintained throughout the lifecycle of the data. Heat Map skips internal operations done for system tasks -- automatically excluding Stats Gathering, DDLs, Table Redefinitions and similar operations. In addition, Heat Map can be disabled at the session level, allowing DBA’s to exclude manual maintenance, avoiding pollution of Heat Map data.

With the data collected by Heat Map, Oracle Database can automatically compress each partition of a table independently based on Heat Map data, implementing compression tiering. This compression tiering can use all forms of Oracle table compression, including: Advanced Row Compression and all levels of Hybrid Columnar Compression (HCC) if the underlying storage supports HCC. Oracle Database can also compress individual database blocks with Advanced Row Compression based on Heat Map data.

Row Compression

a segment-level ADO policy is created to automatically compress the entire table after there have been no modifications for at least 30 days, using Advanced Row Compression:

ALTER TABLE employee ILM ADD POLICY ROW STORE COMPRESS ADVANCED SEGMENT AFTER 30 DAYS OF NO MODIFICATION;
In this next example, a row-level ADO policy is created to automatically compress blocks in the table, after no rows in the block have been modified for at least 3 days, using Advanced Row Compression:
ALTER TABLE employee ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 3 DAYS OF NO MODIFICATION;
In addition to Smart Compression, other ADO policy actions can include data movement to other storage tiers, including lower cost storage tiers or storage tiers with other compression capabilities such as Hybrid Columnar Compression (HCC). HCC requires the use of Oracle Storage – Exadata, Pillar Axiom or Sun ZFS Storage Appliance (ZFSSA).

In this example, a tablespace-level ADO policy automatically moves the table to a different tablespace when the tablespace currently containing the object meets a pre-defined tablespace fullness threshold:

ALTER TABLE employee ILM ADD POLICY tier to ilmtbs;
Another option when moving a segment to another tablespace is to set the target tablespace to READ ONLY after the object is moved. This is useful for historical data during database backups, since subsequent full database backups will skip READ ONLY tablespaces.

Advanced Row Compression uses a unique compression algorithm specifically designed to work with OLTP applications. The algorithm works by eliminating duplicate values within a database block, even across multiple columns. Compressed blocks contain a structure called a symbol table that maintains compression metadata. When a block is compressed, duplicate values are eliminated by first adding a single copy of the duplicate value to the symbol table. Each duplicate value is then replaced by a short reference to the appropriate entry in the symbol table.

File Compression

Consider an email application where 10 users receive an email with the same 1MB attachment. Without Advanced LOB Deduplication, the system would store one copy of the file for each of the 10 users – requiring 10MB of storage. If the email application in our example uses Advanced LOB Deduplication, it will store the 1MB attachment just once. That’s a 90% savings in storage requirements.

In addition to the storage savings, Advanced LOB Deduplication also increases application performance. Specifically, write and copy operations are much more efficient since only references to the SecureFiles data are written. Further, read operations may improve if duplicate SecureFiles data already exists in the buffer cache.

Backup data compression

RMAN makes a block-by-block backup of the database data, also known as a “physical” backup, which can be used to perform database, tablespace or block level recovery. Data Pump is used to perform a “logical” backup by offloading data from one or more tables into a flat file.

Due to RMAN’s tight integration with Oracle Database, backup data is compressed before it is written to disk or tape and doesn’t need to be uncompressed before recovery – providing an enormous reduction in storage costs and a potentially large reduction in backup and restore times. There are three levels of RMAN Compression: LOW, MEDIUM, and HIGH. The amount of storage savings increases from LOW to HIGH, while potentially consuming more CPU resources.

Data Pump compression is an inline operation, so the reduced dump file size means a significant savings in disk space. Unlike operating system or file system compression utilities, Data Pump compression is fully inline on the import side as well, so there is no need to decompress a dump file before importing it. The compressed dump file sets are automatically decompressed during import without any additional steps by the Database Administrator.

Data Guard redo log compression

Data Guard Redo Transport Services are used to transfer this redo data to the standby site(s). With Advanced Compression, redo data may be transmitted in a compressed format to reduce network bandwidth consumption and in some cases reduce transmission time of redo data. Redo data can be transmitted in a compressed format when the Oracle Data Guard configuration uses either synchronous redo transport (SYNC) or asynchronous redo transport (ASYNC).

Index Compression

Advanced Index compression is a new form of index block compression. Creating an index using Advanced Index Compression reduces the size of all supported unique and non-unique indexes -- while still providing efficient access to the indexes. Advanced Index Compression works well on all supported indexes, including those indexes that are not good candidates (indexes with no duplicate values, or few duplicate values, for given number of leading columns of the index) with the existing index Prefix Compression feature.

Network Compression

Advanced Network Compression, also referred to as SQL Network Data Compression, can be used to compress the network data to be transmitted at the sending side and then uncompress it at the receiving side to reduce the network traffic. Advanced Network Compression reduces the size of the session data unit (SDU) transmitted over a data connection. Reducing the size of data reduces the time required to transmit the SDU.

Advanced Network Compression not only makes SQL query responses faster but also saves bandwidth. On narrow bandwidth connections, with faster CPU, it could significantly improve performance. The compression is transparent to client applications.

We won't cover the last two options since they don't apply to database services in the cloud unless you purchase the Exadata as a Service option. There is a Compression Estimation Tool to help you estimate the benefits of compression. A sample of this looking at 100 TB of database data shows a significant cost savings in the millions of dollars.

There is also a Compression Advisor that can be downloaded and installed in your database to look at your tables and estimate how much storage you can save based on your data and your usage patterns. You can watch a Four minute marketing video on the tool and how to use it. I recommend Tyler Mouth's blog entry on customizing the output of the compression advisor to be a little more user friendly. I would also look at Mike Haas's Blog on compression and the DBAORA blog that provides a good overview of 11g compressions. Mike Messin's blog is a good blog on installing and executing the compression advisor.

In summary, compression can be used with a variety of mechanisms based on your usage patterns and objectives. This option is not one size fits all and requires a DBA with knowledge of the usage patterns and familiarity of the data and applications. Letting a non-DBA decide on the compression mechanism can lead to poor performance, missing recovery objective times, increased network throughput, and higher processor utilization than necessary. The Database 12c Compression Documentation details how to create tables that are compressed, how to look and see if tables are compressed, and how to update tables for compression. Compression is a mechanism that can directly reduce your storage costs by consuming significantly less amounts of storage to store the same data. In the cloud this correlates directly to storage cost savings. You get compression as an option for High Performance Edition and Extreme Performance Edition but not the Standard Edition or Enterprise Edition versions of the database.

database option - partitioning part 2

Tue, 2016-06-07 02:07
Yesterday we looked at partitioning. Today we are going to continue this evaluation but actually execute code rather than talk in abstracts. If we want to create a partition, this is easily done by appending partitioning to a table create. It is important to remember that this option cost money when done on-premise and is typically done either to improve performance by having a smaller table to bring into memory or done to split storage so that higher speed disk can be assigned to more relevant data and lower speed and lower cost disk can be assigned to data we typically don't need to read regularly. If we are looking at using partitioning in the cloud, tiering storage is not an option. We get one disk, one type of disk, and can't assign higher speed storage to that disk partition with PaaS or DBaaS. We pay $50/TB/month to attach a disk to a compute engine and that stores our data. The tablespaces are stored in either the USER tablespace or the SYSTEM tablespace based on who creates the tablespace. To quickly review we have tables that contain our data. This data is stored in a tablespace. The tablespace might contain multiple tables or parts of tables if partitioning is used. We can assign tablespaces to different directories and typically do with on-premise systems. This allows us to put data that we need fast access to in flash memory and historic data that we might read once a year in lower cost network storage and not have to backup the historic data on a daily basis. With DBaaS we get a /u02 directory that contains the oradata folder. All tablespaces are created in this area by default. Theoretically we could mount an nfs file share if we ran the storage cloud appliance on a compute instance and pay $30/TB/month for this storage. We would have to install the nfs client on our database instance, install OSCSA on a compute instance and share the nfs directory, create a cloud storage container to hold our historic tablespaces, and point our historic partitions to our nfs mounted directories. We are not going to do this in this blog but it is an interesting thought on how to reduce the cost of storage as well as expand the amount of data that you can support with a DBaaS instance.

Let's create a few tablespaces and a partitioned table to see how it works. Most of these examples are liberally hijacked from other blogs and tutorials on the internet.

We need to note that the DBaaS that we provisioned needs to be High Performance Edition or Extreme Performance Edition. This option does not work with Standard Edition or Enterprise Edition and will fail when you try to create the table. We begin by creating a few tablespaces as well as a partitioned table that stores data into these tablespaces. It is important to note that we can easily do this because consuming storage only happens when we insert data and not create a table. We can play with creation all we want at very little cost. First, let's look at our layout using SQL Developer. If we connect to our database as a sys user we can see that by default we have the following tablespaces defined in our PDB1 pluggable container. The same is true for an 11g instance or container database. We are going to look at pluggable because it is easy to make sure that what we are creating is for this instance and not someone else playing with the system. If we add our database instance to the DBA view in SQL Developer we notice that Tablespaces appears as one of the line entries under our database. We can click on this and look at the tablespaces and files associated with them provisioned in our instance. To see the file allocation and which file system the tablespace is allocated in we need to scroll across the screen to see the information on the right.

We are going to create a few tablespaces then create a table and allocate provisions into these tablespaces. Note that these commands might not work on Amazon RDS because you need to have system level access to the database to create a tablespace and assign the file name. If we let the system use the default oradata area the create works fine. If we want to create the tablespace in /nfs/historic_oradata then the create will fail and is not allowed with RDS. Let's look at a simple example

CREATE TABLESPACE T1;
CREATE TABLESPACE T2;
CREATE TABLESPACE T3;
CREATE TABLESPACE T4;
CREATE TABLE credential_evaluations
( eval_id VARCHAR2(16) primary key
, grad_id VARCHAR2(12)
, grad_date DATE
, degree_granted VARCHAR2(12)
, degree_major VARCHAR2(64)
, school_id VARCHAR2(32)
, final_gpa NUMBER(4,2))
PARTITION BY RANGE (grad_date)
( PARTITION grad_date_70s
VALUES LESS THAN (TO_DATE('01-JAN-1980','DD-MON-YYYY')) TABLESPACE T1
, PARTITION grad_date_80s
VALUES LESS THAN (TO_DATE('01-JAN-1990','DD-MON-YYYY')) TABLESPACE T2
, PARTITION grad_date_90s
VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')) TABLESPACE T3
, PARTITION grad_date_00s
VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY')) TABLESPACE T4 )
ENABLE ROW MOVEMENT;
The create tablespace t1 is needed prior to creating the partition that stores data in the tablespace t1 or the create table command will fail. We have to have the tablespace created before we allocate a partition into it. After we create the tablespace, we can look at the tablespace allocation with SQL Developer by going to the DBA view and looking at PDB1, tablespaces.

Note that the file /u02/app/oracle/oradata/ORCL/339C06AF452F1EB6E0531635C40AD41B/datafile/o1_mf_t1_co5fjnr3_.dbf was created for us. If we change our tablespace create command to

CREATE TABLESPACE T1 datafile '/u02/app/oracle/oradata/ORCL/PDB1/t1.dbf' size 2G;
CREATE TABLESPACE T2 datafile '/u02/app/oracle/oradata/ORCL/PDB1/t2.dbf' size 2G;
CREATE TABLESPACE T3 datafile '/u02/app/oracle/oradata/ORCL/PDB1/t3.dbf' size 2G;
CREATE TABLESPACE T4 datafile '/u02/app/oracle/oradata/ORCL/PDB1/t4.dbf' size 2G;
we drop the files into the directory that we want and have control over the file name and location. It is important to note that this will fail on Amazon RDS because we do not have access to the filesystem and can't specify the filename or location.

When we execute this command it takes significantly longer than our first execution because the system creates a 2 GB file before creating our tablespace and table. We would typically want to add other options like how to grow our partitions, limits on the size, and other dynamic commands. We are primarily concerned with where the file is created and not post maintenance at this point.

We need to make sure that we are running on High Performance Edition or Extreme Performance Edition because Standard Edition and Enterprise Edition fail during the create table command.

In summary, we looked a little deeper at partitioning by looking at the create tablespace and where it creates the files in the file system. We also looked at how we can control the naming as well as location with the create statement options. We briefly touch on two of the advantages that partitioning brings, speed and cost and talked about how to reduce cost by using an nfs share to store more data than a typical DBaaS provides as well as using $30/TB/month storage rather than $50/TB/month storage in the cloud. Hopefully this code example will allow you to play with partitioning and speed up select statements using the High Performance Edition of DBaaS.

database option - partitioning

Mon, 2016-06-06 02:07
Database partitioning has been around since 8i version of the database over ten years ago. The initial features of partitioning were manual processes that allowed you to split data by range like dates or sequences like zip codes. Tablespaces were able to be split into multiple files and indexes applied to each file. If a select statement were executed with a where clause that met the partition boundary, a full table scan was not necessary. Splitting the data into different tablespaces allows us not only to read only the relevant data into memory but we can split our database into storage tiers. We can keep the most used data in high speed disk and historic data in slower lower cost storage. Not only can we use lower cost storage but we can compress the data that is not changing and take up less space. We keep our frequently used data in a high speed disk (or memory if we are lucky) and our older data in lower cost storage. This is only available with partitioning and the reason why many customer purchase this as an option. The return on the software investment significantly reduces the cost of our database storage. We can use flash drives for our current quarter/month data, 10K rpm drives for last quarter/month data, 7.5K rpm drives the rest of the years data, and nfs mounts for data greater than a year old. The cost savings on storage more than pays for the cost of partitioning. Unfortunately, this does not correlate into cloud services since you really don't get into tiered storage behind a database when you consume DBaaS or PaaS. We need to focus on improving performance by helping subpartitions into the available memory to speed up select statements.

Some places to learn more about partitioning include

Before we go down the rabbit hole and dive deep into partitioning, let's review how a select statement works and how data is stored. Say for example we have a database that contains addresses for customers. The table contains an id number, a first name, last name, address, phone number, city, state, zip code, credit card number, credit card expiration, and email address. We have a second table for our on-line catalog that contains part numbers, a title, a description, and a file link for photos. We have a third table for our orders and it contains a customer id number, a part number, an order quantity, and order date. We would create our tables with the following commands

create table customers (
  customer_id number(8),,
  first_name varchar2(32),
  last_name varchar2(32),
  address varchar2(64),
  phone_number varchar2(10),
  city varchar2(32),
  state varchar2(16),
  zip_code varchar2(16),
  credit_card_number varchar2(16),
  credit_card_expiration varchar2(8)
  email_address varchar2(64)
);
create table catalog (
  part_number number(8),
  title varchar2(32),
  description varchar2(128),
  part_image blob
);
create order_entry(
  order_number number(8),
  customer_id number(8),
  part_number number(8),
  part_quantity number(8),
  order_date date
);

If we have ten million items in our catalog we potentially consume 128 + 32 + 8 + 16 bytes times 10,000,000. This makes our table roughly 2 TB in size. If we have two million orders we have about 0.5 TB for the order_entry table. When we create a database we have the option of defining not only the storage type that we want our table to reside in but we can define how and where to store the data associated with this table. By default all tables that we create as a user are stored in the SYSTEM tablespace. All three of these tables will be stored in the DATA area under the SYSTEM tablespace since we did not specify a storage area or tablespace to hold the tables. For the database that we created in previous blog entries using Oracle DBaaS, these files are stored in /u02. We can dive down the /u02/app/oracle/oradata/ORCL/PDB1 and see that there is a system01.dbf file. This correlates to the SYSTEM tablespace in the PDB1 pluggable database. As tables are added, they are added to the system01.dbf file. If we are in the container database ORCL the files are stored in /u02/app/oracle/oradata/ORCL/system01.dbf file.

To help with database performance, index are created into tables so that a reference to a table knows where in the system01.dbf file the table customers and catalog are located. We can also create an index on the table. This index is also stored in the system01.dbf file so that we can look up common queries as they are executed. For example, if we are looking for all orders that happened in February we can select this data quicker with an index by presorting all of the data related to order_date. The index allows us to directly access the table entries in the system01.dbf table by creating an index link to the entries. This index is also stored in the system01.dbf file and re-created when we enter new data into the order_entry table. Hopefully our indexes are small enough to stay resident in memory and we don't need to go to storage to reload and reindex our indexes. Partitioning helps keep indexes smaller as well and unused indexes can be aged out to disk to free up memory. If we never look at data that is two years old, we don't need to keep an index on our two year old data in memory but pull it in from disk when needed.

To reduce the access time and select time we can pre-sort the data in a different way. We can partition the data and store the table information in different files. Rather than storing everything in system01.dbf, we can store February order data in february.dbf. When an update to a table is done the insert is done into the system01.dbf file or the january.dbf, february.dbf, or march.dbf file. When we transition into April an april.dbf file is created and the january.dbf data is moved into q1_2016.dbf file. The key advantage to this is when we perform a select statement and look for data in March and April, we only look in the march.dbf and april.dbf files. The rest of the data is not loaded because we know that the data is not in the other table extents. This reduces the amount of data that is loaded into memory and reduces the amount of disk operations that are performed for every select statement. If everything was stored in the system01.dbf file, we would need to load all two million orders just to find the one or two hundred that happened in April. We basically read then throw away 97% of the data read because it does not match our request. True, the index would help but this requires multiple writes to the disk when an insert happens. With partitioning enabled for the order_date column, all order entries are stored pre-sorted by date in the different table extents. Since 11g interval partitioning automatically creates new partition tables. As we cross from February to March, the march.dbf is created and all writes corresponding to march orders are written to the new partition.

There are a variety of partition types that you can use to divide data

  • Range partitioning - typically based on date columns, months, quarters, a range of numberic or character values. You can specify a value less than or value greater than when defining the partition. The value that you compare can be relative or specific to a current date or number.
  • List partitioning - this describes a discrete value and assigns them to their own tablespace. We might split our catalog into plumbing products, lawn and garden products, or appliances. This helps searches into our catalog for a specific item. Note when you do a search at HomeDepot or Amazon you have the option of search in department. They are using list partitions on the back end.
  • Hash partitioning - this is good if you don't have a specific range that will split the data. If you want to sort by address for example, it is difficult to list all of the addresses or sort them into a range. The hash partition allows you to split your data into 16 different partitions and the database will split the data with a best effort to spread all of the data between the number of partitions you define.
  • Composite partitioning - this is a combination of two of the types described above. Composite partitioning is accomplished with the subpartition command where we first sort by one method then sub-sort by another. We could use a list-list or a list-range. We can use two of any of the above to help manage a large tablespace into smaller chunks.
  • Reference partitioning - this allows you to partition data based on referential constraints. If, for example, you want to create a constraint in a table creation and sort on that constraint, you can do this with partition by reference. If we create a table and add a constraint that an order_id must be tied to a customer_id in our customers table we can partition by this constraint which effectively splits the orders table into orders by customer_id which is not defined in this table.
  • Virtual column-based partitioning - virtual column partitioning allows us to split a table based on part of a column value. If, for example, we index our parts in our catalog by sequence numbers with 1-3 representing plumbing and 4-6 representing appliances, we can partition based on the first number in our part_id and effectively split the catalog based on departments without having to define the department as a column in our table. We just need to make sure that all part numbers that are inserted into our catalog follow our numbering convention and not put a gas range into the catalog staring with a 2 as the part number.

If we change the customers table described and append a partition by range statement with the command

create table customers ( .... ) partition by range (state);
we divide the table into potentially fifty different tables. As a new customer is added, they are added to the new state table. Inserts happen quicker, selects happen quicker, backups happen quicker unless all of our customers are located in one state.

If we group our customers into regions and want to store data not in fifty states but in three regions we could do this wist a list range. Note that we can define the table name when we define the partition.

create table customers (....) partition by list (state)
(partition part1 values ('Texas', 'Louisiana', 'Oklahoma', 'Arkansas') tablespace tola_ts ,
 partition part2 values ('California', 'Oregon', 'Washington', 'Hawaii') tablespace pac_ts,
 partition category_other values (default));
In this example we create the tola_ts, pac_ts, and default tablespace. We split seven of the states into three buckets and store customers into the three areas. This make reporting simpler and optimizes for select statements looking for customers in or around Texas or along the Pacific Ocean. Note that we could also subpartition this data to separate the big cities from rural areas
create table customers (.....) partition by list (state)
(partition part1 values ('Texas") tablespace texas_ts
 subpartition big_cities 
  (partition texas_cities values('Houston', 'Dallas', 'San Antonio', 'Austin', 'Fort Worth', 'El Paso') tablespace big_texas_ts,
   partition category_other values(default) tablespace small_texas_ts),
 partition part2 values ('California', 'Oregon', 'Washington', 'Hawaii') tablespace pac_ts,
 partition category_other values (default));
);
This will create four tablespaces. One for Texas big cities, one for Texas small cities, one for Pacific rim states, and one for all other states.

Database 12c added a few new commands to help manage and maintain partitions. We can now alter partitions and add, truncate, drop, split, and merge. The add and merge are very valuable functions that allow us to update ranges. If, for example, we paid a consultant two years ago to define a partition by range and they went out four years with the following

create table sales (.....) partition by range (salesdate)
(
 partition part_2015 values less than (TO_DATE('01-Jan-2016', 'DD-MON-YYYY')),
 partition part_2016 values less than (TO_DATE('01-Jan-2017', 'DD-MON-YYYY'))
)
ENABLE ROW MOVEMENT;
But we want to start saving data by quarter rather than by year we could execute the following
alter table sales add
 partition p_q1_2016 values less than (TO_DATE('01-Apr-2016', 'DD-MON-YYYY')),
 partition p_q2_2016 values less than (TO_DATE('01-Jul-2016', 'DD-MON-YYYY'));
This would slide in quarterly reporting and allow us to handle a larger volume than was created before. If at the end of the year we want to aggregate everything back into a year rather than a quarter basis we can do this with a merge command
alter table sales
merge partitions p_q1_2016, p_q2_2016, p_q3_2016, p_q45_2016
into partition part_2016;

Fortunately, Enterprise Manager has a partition advisor that looks at the history of your select statements and suggests how you should divide your tables into partitions. It notices that you do a lot of select by state or select by zip code and recommends partitioning by list or by hash based on your usage patterns. This was a new feature added with Enterprise Manager 11 and has gotten more robust and reliable with 13c. We should see a significant speed up if we get the right combination of partitions and indexes and could potentially take a select statement from 45 seconds to sub seconds as shown in the Enterprise Manager screen shots below.

In summary, partitioning is very powerful. It helps you split up your larger tables so that they fit into the memory that you have allocated. The return on investment is difficult to do because the cost for partitioning vs the cost of memory and resulting speed up for queries is hard to measure. Enterprise Manager has tools to help you with this analysis but it is difficult to put into future dollars and what if analysis. It would be nice if you could say that splitting your table into partitions would reduce your buffer cache and allow you to shrink your SGA size by 25%. The tools are not quite there. They do tell you that you can reduce your select times by partitioning the data and predict relatively accurately how much faster a select statement will be with partitioning based on your current hardware configuration. All of these functions should work on Amazon RDS with the exception of manipulating a tablespace. This requires a different command syntax since manipulation of a tablespace requires system access. Typically the command would be alter database default tablespace users2 but with Amazon RDS you have to execute exec rdsadmin.rdsadmin_util.alter_default_tablespace('users2') instead. Given that this is not done very often, it is up to you to decide how and where you deploy your large table database.

preparing a desktop for PaaS

Fri, 2016-06-03 02:07
Before we can start looking at the different options of a database, we need to get a desktop ready to do database development. It sounds a little strange that we need to download software to get access to a cloud database. We could do everything from the command line but it is much simpler and easier if we can do this using desktop tools. The software that we are going to download and install are
  • To access cloud storage
    • Mozilla Firefox
    • RestClient extension for Firefox
    • Google Chrome
    • Postman extension for Chrome
    • CloudBerry for OpenStack
  • To access files in our instance
    • Putty
    • Filezilla
    • Cygwin
  • To access our database instance
    • SQL Developer
    • Microsoft Visual C++ libraries

The eventual target that we are looking to get to is

To do this we need to go to the Oracle Cloud Marketplace and look for the Windows 2012 Server instance. What we want to do is provision a Windows instance and use it as a remote desktop for connecting to the cloud. We could do this on our desktop but the benefit of using a Windows Server is that we can create more users and use this instance for a hands on workshop. We don't need to have anyone load any virtual machines, fight the differences between Mac and Windows, or wait for the binaries to download and install. We can do most of this on a virtual machine in the cloud and just add and delete users for workshops. To provision the Windows server, we go to the cloud marketplace, select Infrastructure, and Compute. We can the search for Windows and get a bootable image to use as our foundation.

Once we agree to the legal terms we can select an instance to provision this into. The way it works is that we copy a bootable image into a cloud instance. We can then create compute instances from this bootable image and customize it to our liking. Once we agree to the terms the marketplace connects to the Oracle Cloud and uses your authentication credentials to connect to the instance. From this is gets a list of instances associated with this account, checks to see if you have agreed to terms of marketplace use for this instance by setting your profile settings for the instance. Once the bootable image is ready, a splash screen is presented stating that you are ready to provision a compute instance.

The screen dumps you into a compute creation wizard that walks you through the compute provisioning. Rather than going through that interface we decided to start from scratch and log into the instance and provision a compute engine from scratch. We first select the boot image from our private images, select the shape to boot, define the instance name, configure ssh connectivity as well as set the Admininstrator password (not shown). Once we get the confirmation screen it takes a few minutes to create the boot disk then boot the compute instance on this newly formatted disk.

We can check the progress by looking at the storage and compute instance. When everything is done we should see a public ip address for our instance. If we don't see our instance it is either still building or we should see an error in the history. Unfortunately, the history is hidden and a failed provisioning is now shown unless you look for it by expanding the history.

Before we can connect to our instance with remote desktop, we need to define a security list to allow for rdp, associate this rule with our instance, and define the security rule for rdp and associate it with the security list and instance.

Once we have rdp enabled to our instance, we look up the public ip address and connect as the Administrator user with the password that we passed in with a json header at the bottom of the creation screen (not shown). When we log in we see the server tools splash screen pop up.

We want to create a secondary user, give this user admin rights as well as rights to remote desktop connect to the server. We might want to add more users not as admins but with remote desktop rights for hands on labs. We can add and delete users using this method and it refreshes the workshop for the next class.

At this point we can create a staging directory and install the software that we listed above. The only product that causes a problem with the install is the SQL Developer because it requires a Microsoft package that is not installed by default. We need to download the library and all of the packages that we downloaded are ready to install. I did not go through customization of the desktop or downloading the public and private keys used for the workshop. These are obvious steps using filezilla from a shared network storage on a server in the cloud. We downloaded Firefox and Chrome primarily because Internet Explorer does not support REST Api protocols and we will need a way to create and list storage containers. We could have skipped this installation and done everything through CloudBerry but we can do everything similarly on a Mac (no need for putty or cygwin). With Firefox you need to install the REST Client api extension and Chrome requires the Postman Extension.

In summary, we created a compute Windows 2012 Server instance in the Oracle Compute IaaS. We added a new user as a backup to our Administrator user. We enabled remote desktop and configured a Mac to connect to this service remotely. We then downloaded a set of binaries to our Windows desktop to allow us to manage and manipulate storage containers and database instances. We also downloaded some utilities to help us use command line tools to access our database and customize our instances. We technically could do all of this with a Windows desktop, Internet Explorer, and SQL Developer. We went to the extra steps so that we can do the same from a Mac or Windows desktop using the same tools.

database options

Thu, 2016-06-02 12:16
Before we dive into features and functions of database as a service, we need to look at the options that you have with the Oracle Database. We have discussed the differences between Standard Edition and Enterprise Edition but we really have not talked about the database options. When we select a database in the Oracle Cloud we are given the choice of Enterprise Edition, High Performance Edition, and Extreme Performance Edition. Today we are going to dive into the different Editions and talk about the options that you get with each option. It is important to note that all of the options are extra cost options that are licensed on a per processor or per user basis. If you go with Amazon RDS, EC2, or Azure Compute you need to purchase these options to match your processor deployment.

One of the standard slides that I use to explain the differences in the editions is shown below.

The options are cumulative when you look at them. The Enterprise Edition, for example, comes with Transparent Data Encryption (TDE). TDE is also included in the High Performance and Extreme Performance Editions. We are going to pull the pricing for all of these options from the Technology Price List. Below is a list of the options.

  • Enterprise Edition
    • Transparent Data Encryption
  • High Performance Edition
    • Diagnostics
    • Tuning
    • Partitioning
    • Advanced Compression
    • Advanced Security
    • Data Guard
    • Label Security
    • Multitenant
    • Audit Vault
    • Database Vault
    • Real Application Testing
    • OLAP
    • Spatial and Graphics
  • Extreme Performance Edition
    • Active Data Guard
    • In Memory
    • Real Application Clusters (RAC)
    • RAC One

Transparent Data Encryption

TDE is a subset of the Advanced Security option. TDE stops would-be attackers from bypassing the database and reading sensitive information from storage by enforcing data-at-rest encryption in the database layer. Data is stored in the table extents encrypted and read into the database encrypted. The Oracle Wallet is needed to read the data back and perform operations on the data. Advanced Security and Security Inside Out are blogs to dive deeper into TDE features, functions, and tutorials. There is also a Community Security Discussion Forum. The Advanced Security option is priced at $300 per named user or $15,000 per processor. If we assume a four year amortization the cost of this option is $587.50 per month per processor. The database license is $1,860 per month per processor. This says that a dual core system on Amazon EC2, RDS, or Azure Compute running the Oracle database will cost you the cost of the server plus $2,448 per month. If we go with a t2.large on Amazon EC2 (2 vCPUs and 8 GB of RAM) and 128 GB of disk our charge is $128 per month. If we bump this up to an r3.large (2 vCPU, 15 GB of RAM) the price goes up to $173 per month. The cost will be $2,620 per month which compares to Enterprise Edition at $3,000 per month per processor for PaaS/DBaaS. We could also run this in Oracle IaaS Compute at $150 per month (2 vCPUs, 30 GB of RAM) to compare apples to apples. It is strongly recommended that any data that you put in the cloud be encrypted. Security is good in the cloud but encryption of data in storage is much better. When you replicate data or backup data it is copied in the format that it is stored in. If your data is clear text, your backups could be clear text thus exposing you to potential loss of data. Encrypting the data at rest is storage is a baseline for running database in the cloud.

Diagnostics

Diagnostics is a subset of the Database Management Packs that allows you to look into the database and figure out things like lock contention, what is holding up a wait queue, and what resources are being consumed by processes inside the database. Historic views into the automated workload repository (AWR) reports are available with this option. You can get spot options but not historical views and comparative analytics on AWR information. Some of the tools are free like compression advisor and partitioning advisor while others are part of the diagnostics pack. Diagnostics are licensed at $150 per named user or $7,500 per processor. This correlates to $294 per processor per month. Unfortunately, you can't purchase Enterprise Edition DBaaS and add this but need to go with IaaS Compute and add this to the bring your own database license. The only way to get this feature is to go with the High Performance Edition. The binary that is installed on the cloud service specifically labels the database as Enterprise Edition, High Performance Edition, or Extreme Performance Edition. All of the features listed from here and below are prohibited from running on the Enterprise Edition when provisioned into the Oracle DBaaS. If you just want Diagnostics Pack on Enterprise Edition it does not make economic sense to purchase High Performance Edition at $4,000 per month per processor when you can do this on IaaS at $2,914 (the $2,620 from above plus $294).

Tuning

Tuning is also a subset of the Database Management Packs that allows you to look into sql queries, table layouts, and overall performance issues. Options like the SQL Tuning Advisor and Automatic SQL Tuning are part of this option. Tuning pack is $100 per named user or $5,000 per processor. This comes in at $196 per processor per month if purchased separately. A Tuning Whitepaper details some of the features and functions of the tuning pack if you want to learn more.

Partitioning

Partitioning is a way of improving performance of your database and backup by splitting how data is stored and read. Partitioning is powerful functionality that allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity. Oracle provides a comprehensive range of partitioning schemes to address every business requirement. The key improvement is to reduce the amount of data that you are reading into memory on a query. For example, if you are looking for financial summary data for the last quarter, issuing a query into eight years of financial data should not need to read in 32 quarters of data but only data from the last quarter. If we partition the data on a monthly basis we only read in three partitions rather than all 32. Partitioning also allows us to compress older data to consume less storage while at rest. When we backup the database we don't need to copy the older partitions that don't change, only backup the partitions that have updated since our last backup. Partitioning is licensed at $230 per named user or $11,500 per processor. This comes in at $450 per processor per month. The three most purchased database options are diagnostics, tuning, and partitioning. The combined cost of these three options is $940 per processor per month. When we compare the $4,000 per processor per month of DBaaS to IaaS with these three options we are at parity.

Advanced Compression

Advanced Compression is a feature that allows you to compress data at rest (and in memory) so that it consumes less resources. Oracle Advanced Compression provides a comprehensive set of compression capabilities to help improve performance and reduce storage costs. It allows organizations to reduce their overall database storage footprint by enabling compression for all types of data: relational (table), unstructured (file), network, Data Guard Redo and backup data. Cost comparisons for this feature are directly comparable to storage costs. Advanced compression is licensed at $230 per named user or $11,500 per processor. This comes in at $450 per processor per month. Typical compression ratios are 3x to 10x compressions. This means that 1 TB of data will take up 600 GB or 100 GB at these compression ratios. Lower compression rates are recommended for data that lightly changes and high compression for data that will not change. The penalty for compression comes in when you update data that is compressed. The data must be uncompressed, the new data inserted, and recompressed.

Advanced Security

Advanced Security allows you to secure and encrypt data in the database. Advanced Security provides two important preventive controls to protect sensitive data at the source including transparent database encryption and on-the-fly redaction of display data. TDE stops would-be attackers from bypassing the database and reading sensitive information directly from storage by enforcing data-at-rest encryption in the database layer. Data Redaction complements TDE by reducing the risk of unauthorized data exposure in applications, redacting sensitive data before it leaves the database. Advanced Security is priced at $300 per named user and $15,000 per processor. The monthly cost will be $587.50 per month per processor for this option. Data redaction is typically required for replicating production data to development and test. If you have credit card, social security numbers, home addresses, or drivers license information in your database, redaction is important to have to remain Sarbanes Oxly and PCI compliant.

Data Guard

Data Guard is a key foundation piece of Maximum Availability Architecture and does not cost any additional money. You get data replication between two databases at no additional cost and data can be replicated as physical or logical replication between the database instances. This feature ensures high availability, data protection, and disaster recovery for enterprise data. Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as transactionally consistent copies of the production database. Then, if the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability. It is important to note that Data Guard is not allowed in Amazon RDS and you must use EC2 or another cloud service to use this feature.

Label Security

Label Security has the ability to control access based on data classification and to enforce traditional multi-level security (MLS) policies for government and defense applications. Oracle Label Security benefits commercial organizations attempting to address numerous access control challenges including those associated with database and application consolidation, privacy laws and regulatory compliance requirements. When a user requests data, the database looks at the user credentials and roles that they have access to and filters the results that the user sees from a query. Label Security is licensed at $230 per named user or $11,500 per processor. This comes in at $450 per processor per month. Note that this is different than data redaction. With redaction, data is scrambled when data is copied. With Label Security, the data is not returned if the user does not have rights to read the data. An error is not returned from a query but a null value is returned if the user does not have rights to read a column. The biggest benefit to this option it does not require program changes to restrict access to data and present results to users. If, for example, we are going to show sales in a customer relationship program, we don't need to change the code based on the user being a sales rep or sales manager. The sales manager can see all of the sales rep information to track how their team is performing. Each sales rep can see their data but not the other sales rep data. It is important to note that Label Security is not allowed in Amazon RDS and you must use EC2 or another cloud service to use this feature.

Multitenant

Multitenant or Pluggable Database allows you to consolidate instances onto one server and reduce your overall management cost. The many pluggable databases in a single multitenant container database share its memory and background processes. This enables consolidation of many more pluggable databases compared to the old architecture, offering similar benefits to schema-based consolidation but with none of the major application changes required by that approach. Backups are done at the parent layer. Users are provisioned at the pluggable layer. Features of the instance (RAC, DataGuard, etc) are inherent to the parent and adopted by the pluggable container. To take a test system from single instance to data guard replicated only requires unplugging the database from the single instance system and plugging it into a data guard system. The same is true for RAC and all other features. Multitenant is licensed at $350 per user or $17,500 per processor. This come in at $685 per processor per month. It is important to note that this option is not available on Amazon RDS. This option is specifically disabled and not allowed. You must run this on EC2 to use this functionality or on another cloud platform.

Audit Vault

Audit Vault and Database Firewall monitors Oracle and non-Oracle database traffic to detect and block threats, as well as improves compliance reporting by consolidating audit data from databases, operating systems, directories, and other sources. Audit vault is licensed at $6,000 per processor and is not available on a per user basis. This comes in at $235 per processor per month. This option typically requires a separate server for security reasons where logs and logging information is copied to prevent data to be manipulated on a single system and the auditing system.

Database Vault

Database Vault reduces the risk of insider and outsider threats and addresses common compliance requirements by preventing privileged users (DBA) from accessing sensitive application data, preventing compromised privileged users accounts from being used to steal sensitive data or make unauthorized changes to databases and applications, providing strong controls inside the database over who can do what and controls over when and how applications, data and databases can be accessed, providing privilege analysis for all users and applications inside the database to help achieve least privilege model and make the databases and applications more secure. Database Vault is licensed at $230 per named user or $11,500 per processor. This comes in at $450 per processor per month. It is important to note that this option is not available on Amazon RDS. This option is specifically disabled and not allowed. You must run this on EC2 to use this functionality or on another cloud platform.

Real Application Testing

Real Application Testing helps you fully assess the effect of such system changes on real-world applications in test environments before deploying the change in production. Oracle Real Application Testing consists of two features, Database Replay and SQL Performance Analyzer. Together they enable enterprises to rapidly adopt new technologies that add value to the business while minimizing risk. Traces can be recorded for reads and writes and replayed on a test system. This makes the replay option perfect for development and testing instances. The product is licensed at $230 per named user or $11,500 per processor. This comes in at $450 per processor per month. It is important to note that not having the sys level access might or might not break this feature in Amazon RDS based on what you are trying to replay.

OLAP

Online Analytics Processing or OLAP is a multidimensional analytic engine embedded in Oracle Database 12c. Oracle OLAP cubes deliver sophisticated calculations using simple SQL queries - producing results with speed of thought response times. This outstanding query performance may be leveraged transparently when deploying OLAP cubes as materialized views – enhancing the performance of summary queries against detail relational tables. Because Oracle OLAP is embedded in Oracle Database 12c, it allows centralized management of data and business rules in a secure, scalable and enterprise-ready platform. OLAP is licensed at $460 per user or $23,000 per processor. This comes in at $901 per processor per month. This feature is good for BI Analytics packages and Data Warehouse systems.

Spatial and Graphics

Spatial and Graphics supports a full range of geospatial data and analytics for land management and GIS, mobile location services, sales territory management, transportation, LiDAR analysis and location-enabled Business Intelligence. The graph features include RDF graphs for applications ranging from semantic data integration to social network analysis to linked open data and network graphs used in transportation, utilities, energy and telcos and drive-time analysis for sales and marketing applications. This option is licensed at $350 per user or $17,500 per processor. This come in at $685 per processor per month. It is important to note that this option is not supported in Amazon RDS. You must select EC2 or another cloud service to get this option.

All of the above options are bundled into the High Performance Edition. If we add up all of the options we get a total of

  • Transparent Data Encryption - $587.50 per month
  • Diagnostics - $294 per month
  • Tuning - $196 per month
  • Partitioning - $450 per month
  • Advanced Compression - $450 per month
  • Advanced Security - $587.50 per month
  • Data Guard - bundled
  • Label Security - $450 per month
  • Multitenant - $685 per month
  • Audit Vault - $235 per month
  • Database Vault - $450 per month
  • Real Application Testing - $450 per month
  • OLAP - $901 per month
  • Spatial and Graphics - $685 per month
This roughly bubbles up to $5,833.50 per processor per month for the High Performance options. Oracle bundles all of this for an additional $1000 per processor per month. The Extreme Performance Edition options include Active Data Guard, In Memory, and RAC.

Active Data Guard

Active Data Guard has the same features and functions as Data Guard but allows the target database to be open for read/write and updates happen bidirectionally. Active Data Guard is licensed at $230 per user or $11,500 per processor. This come in at $450 per processor per month.

In Memory

In Memory optimizes both analytics and mixed workload OLTP, delivering outstanding performance for transactions while simultaneously supporting real-time analytics, business intelligence, and reports. Most DBAs optimize performance by creating indexes to find data quicker. This works if you know the questions ahead of time. If you don't know the question it is difficult to tune for everything. In Memory allows you to create a row based copy of the data as well as a column based copy of the data for quick column sorts and searches. In Memory is licensed at $460 per user or $23,000 per processor. This come in at $901 per month per processor. The key advantage of this option is that it prevents you from purchasing a second database to do analytics and reporting on the same box as your transactional system.

Real Application Clusters (RAC)

RAC is a cluster database with a shared cache architecture that overcomes the limitations of traditional shared-nothing and shared-disk approaches to provide highly scalable and available database solutions for all your business applications. Oracle RAC is a key component of Oracle's private cloud architecture. Oracle RAC support is included in the Oracle Database Standard Edition for higher levels of system uptime and a critical part of the MAA strategy. RAC is licensed at $460 per user or $23,000 per processor. This come in at $901 per month per processor. It is important to note that RAC is not supported in Amazon or Azure. The system requires shared storage between compute instances that neither platforms provide. The only option for this configuration is Oracle DBaaS/PaaS.

The options for Extreme performance come in at $2,252 per processor per month but Oracle only charges an extra $1000 with Extreme Edition.

In Summary, there are a ton of options for the database. You need to figure out what options you need and if you need more than a couple it is economically beneficial to go with High Performance. If you need RAC, Active Data Guard, or In Memory you must purchase the Extreme Performance Edition. It is also important to note that not all features are supported in Amazon RDS and you must either go with Oracle Database as a Service or build a system using IaaS. RAC is the only exception where it is only available with Oracle DBaaS. We will go into a couple of these features in upcoming days to look at the value, how to use, and what is required to make the functionality work with other cloud providers.

Pages