Storing XML in the Database

Previously you could store XML in a CLOB data type. Then you could use DBMS_LOB to get at parts of the XML. This had the advantage that the original XML format was saved. However, the database did not know anything about the structure of the XML document in the CLOB.

The XMLTYPE was introduced in Oracle 9iR1. This allows the database to natively support XML. The type is treated as a user defined type with built in functions that operate on it. Therefore you can use it in SQL just like any other user defined type. The SQL can query parts of the XML document. You can use the type for column data types. You can use it in PL/SQL as types. It supports XML Schema.

There are different ways that XMLTYPE can internally store the data. However it is stored, an XML DOM tree structure can be constructed on demand when the need arises. Here are some options on how the XMLTYPE can internally store the data:

1. As a CLOB
2. Object Relational
3. Binary XML

CLOB format keeps the original XML document format. Performance is decreased. Object relational format will shred the XML. White spaces get removed. Performance is increased. The binary XML storage option was added in Oracle 11g. The XML gets parsed first then stored in binary form.

If you want to get a some parts of the XML, you can use the extract() function. Pass it an XPath string. It will give you back an XMLTYPE. Or you can use the extractvalue() function. It too takes an XPath string to specify the piece of XML you want. It however returns a scalar value.

OEM and Grid Control

First there was Oracle Enterprise Manager Database Control. This is known as OEM. It controlled a database. You view it in a singe window. It is web based. Was historically good for new Oracle features. It is easy to set up.

Next there was Oracle Enterprise Manager Grid Control. This allows you to manage many databases, application servers, and other products. The Grid Control resides on a separate server and has its own database. You can monitor Oracle infrastructure. You can also monitor non-Oracle items such as:

• Other databases
• Web servers
• Application servers
• Firewalls
• Operating systems

Grid Control reduces your costs. It reduces complexity. There are a couple components that make up the Grid Control:

1. Oracle Management Servive
2. Oracle Management Agent
3. Oracle Management Repository.

The agents run on the things being managed. There is a separate server that manages all these agents. And the information is stored in a database (repository). Grid control lets you perform system monitoring. You can view server alerts. You can set up notification rules. This allows you to detect performance problems.

To simply monitoring, you can combine single components into groups. Then you can monitor the whole group as one. You can automate administrative tasks. You can publish customized reports. These reports can be HTML based. You can manage Oracle patches. The Grid Control is extensible through management plugins.

Data Masking

We test the application on our project using a development database created by scrubbing production data. That original scrub happened over 10 years ago. Our development data is starting to get a bit stale. It gets updated every year to try to keep pace with the new dates. But formats and structures change. This sounds like a job for Oracle Data Masking,

Data Masking is an add on pack for Enterprise Manager. It allows you to meet data privacy and data protection mandates. It replaces sensitive values with realistic replacements. Thus you can use production like data for non-production uses.

Data Masking can actually scan for sensitive data. It can make sure sensitive data does not leave the production environment. Its operation is also called scrambling or anonymization. The scrubbing is based on rules that you set up or choose from some preset formats.

Here are the data type supported for scrubbing:

• Numeric
• String
• Date
• LOB (requires Grid 11gR1)

These are the overall steps you take to perform the data masking:

1. Clone production to a staging environment
2. Select mask definition in staging
3. Clone the masked staging data to a test environment

The predefined mask formats allow you to generate random numbers for replacements. You can also do some post processing to ensure the data is of believable format. Masking is controlled by the DM_FMTLIB package. Here are some presets for well known data types that are supported:

• Credit card numbers
• ISBN numbers
• UPC codes
• Social Security Numbers
• Phone numbers

 Here are the roles you need to perform a masking operation:

• Select catalog role
• Select any dictionary
• Execute privileges on DBMS_CRYPTO

Be aware that masking partitioned tables might cause some partition movement if you mask the partition key. There are some types that are not supported by masking:

• Clustered tables
• Object tables
• XML tables
• Virtual columns

Masking can happen quickly because of the following optimiations:

• Bulk operations
• Disabled logging
• Running in parallel

Oracle Spatial

Oracle has support to location enable application. This is called Spatial. It was named Oracle Spatial since Oracle 8. And it is now called Oracle Spatial and Graph since Oracle 10g. Spatial consists of functions and procedures for quick spatial data access and analysis. The code is available in the MDSYS schema.

Spatial defines a number of geometry types:

• Point
• Line
• Polygon
• Arc
• Circle
• Rectangle

Spatial allows you to deal with height and depth data. Or it can represent longitude and lattitude data. This is application to a Geographic Information System (GIS) application. It allows you to project this data onto a two dimensional surface.

The basic building block in Spatial is an element. Types, like mentioned before, are point, line, or polygon. Many elements make up a geometry. And a collection of geometries is a layer.

Spatial allows you to perform an area of interest query. There are two tiers to its query model: (1) primary filter, and (2) secondary filter. To help speed querying, a concept of a spatial index has been created. This is a logical index. There are two types. The first, which is the default, is the R-tree. It uses a minimum bounding box. The second is the quadtree. It uses a technique called tessellation. Each rectangle is chopped into smaller rectangles.

Oracle Label Security

There are many ways to lock down data access in Oracle. A low granularity technique is one we use on my project. That would be the Discretionary Access Control (DAV). With this you pretty much determine whether users have SELECT/INSERT/UPDATE/DELETE privilege to any given table. The users can either do those operations, or they cannot.

The Virtual Private Database (VPD) technology takes security to another level. You get more control. Specifically you decide which rows any type of user is allowed to see. I have written about VPD before. Now I want to talk about Oracle Label Security (OLS). OLS is built upon VPD. But unlike VPD, which requires you to write an access function in PL/SQL, OLS runs "out of the box."

Out of the box means you don't need to do any programming to use OLS. You can set up what you need using Oracle Enterprise Manager. You can create labels. Assign labels to rows. And also assign labels to users. Also you can use Oracle's policy manager tool.

As you can imagine, OLS was originally created to server government applications. Specifically it was meant to address Department of Defense security needs. However OLS must have been a hit. Because now it is also being used in the commercial sector.

The high level idea is that users can have labels associated with them. Specific rows of specific tables can also have labels applied to them. When a table is accessed by a user, the labels of the user and rows are compared. If the labels match, the user gets access. This label information is stored in a new column added to the table for access purposes.

Although I have heard it different ways, more people report that OLS does not come installed by default in the Oracle database. If so, you must use the Universal Installer to add it in. You also need to configure the LBACSYS account that gets installed with OLS. You must unlock it, set its password, and grant it SELECT ANY DICTIONARY.

Recovery Manager

Oracle has had the Recovery Manager (RMAN) since Oracle 8. It is installed along with the database. RMAN is a database client. The client can be run from the command line or Enterprise Manager. One main feature is cross platform backup and restore. RMAN automates a lot of the administration of backups. It can do point in time table recovery. It also can detect corruption in backups.

RMAN backups are stored as backup sets. It can do incremental as well as full backups. RMAN works with a media manager. That would be the system that controls the devices where backups are stored. For example, one media manager might control your tape drive.

An important facet of RMAN operation is the recovery catalog. This is a database schema where RMAN activity is recorded. It tracks the file names of all database files. It stores the location of redo logs. It can be used if the control file is lost.

RMAN is started by running the rman executble. You CONNECT TARGET to log into a database. The user needs SYSDBA privileges, although you do not explicitly state AS SYSDBA. The BACKUP command will start the backup. Unique file names are generated for backup files. The files are proprietary and specific to RMAN only.

You can do a hot backup while the system is running. The database must be in ARCHIVELOG mode. This results in what they call an inconsistent backup because it will require redo logs. Backups can be full or incremental. Incremental backups start at level 0 which is equivalent to a full backup. The VALIDATE command checks whether there is any backup corruption.

You can put RMAN commands in a file. Run it with the @ sign, just like SQL*Plus. Use the LIST BACKUPS for a report. Or use the REPORT command for a more comprehensive report. DELETE will explicitly get rid of old backups. Any backups older than the retention period are automatically deleted. Recovery involves a RESTORE DATABASE along with a RECOVER DATABASE command.