Click to give Water and Food

Wednesday, April 22, 2015

Managing Oracle Database 12c with Enterprise Manager – Part XII

We are discussing the management of Oracle Database 12c in Oracle Enterprise Manager 12c. In our previous blog post on this topic, we looked at how the Self-Service console would appear to the Self-Service Application (SSA) user, with the ability to provision databases, schemas or PDBs on the fly.
Metering and Chargeback is an important feature of the cloud. Due to the agile self-service nature of the cloud, it is important to set up quotas for the consumers, as well as meter the usage of the cloud, and use calculated dollar costs for showback or chargeback, so that the cloud is not exploited by over-usage by the consumers. This would also serve as a way for an internal IT department to show its value to the other business units, by putting an internal cost to its services – at least for showback purposes.
The “Chargeback and Capacity Planning Plug-in” (from EM12c Release 3) started to provide support for Oracle Pluggable Databases (12c PDBs). This enabled the Chargeback administrator to add a Container Database (CDB) to Chargeback, and assign each Pluggable Database (PDB) individually to a cost center.
This plug-in is now renamed to the latest available “Oracle Consolidation Planning and Chargeback” as can be seen via Setup.. Extensibility.. Plug-ins from the Enterprise Manager console.
A friend recently asked me: “Can I use showback / chargeback for an already existent database, that is, without the need to create a database as a service?”
My answer was that you cannot use chargeback unless you fully license the DBLM pack and Cloud Management pack and add the database as a resource to a zone with a charge plan. You don’t need to set up the full Database-as-a-service and enable self-service, it will just calculate the usage for an existing database if it is added in this way.
For more information on setting up chargeback, refer to the documentation here.
This blog post was originally published at this link.

Managing Oracle Database 12c with Enterprise Manager – Part XI

We are discussing the management of Oracle Database 12c in Oracle Enterprise Manager 12c. In our previous blog post on this topic, we looked at “Database as a Service” and “Schema-as-a-Service”, and also talked about “Pluggable database (PDB) as a Service (PDBaaS)”.
The “Schema as a Service” capability was introduced since the Cloud plug-in “Enterprise Manager for Oracle Cloud (SSA)”. The renamed plug-in “Oracle Cloud Application” was later released in October 2013. This added the capability of the Pluggable Database as a Service, i.e. PDBaaS.
Note that the current plug-in version available is Oracle Cloud Application To check your version, go to Setup.. Extensibility.. Plug-ins from the Enterprise Manager console.  
If your plug-in version is not equivalent to the above, you may need to update your plug-in using Setup.. Extensibility.. Self Update. For more information on the self update process, please see the documentation here. You will need to be on Enteprrise Manager 12c Release 4 ( in order to use the latest plug-in. If you are on an earlier release of Enterprise Manager, you will not be able to update the plug-in to the latest version.
The final aim of the Cloud Administrator, on setting up the database pools, service catalog, quotas and chargeback plans, is to provide the self-service capability to the SSA (Self-Service Application) user as can be seen below – the ability to ask for and get databases, schemas and PDBs via self-service.

More in the next blog post. 
This blog post was originally published at this link.

Managing Oracle Database 12c with Enterprise Manager – Part X - Database as a Service

We are discussing the management of Oracle Database 12c in Oracle Enterprise Manager 12c. In our previous blog post on this topic, we looked at the Database Resource Manager and how it can be used to control resources at the PDB level in Database 12c.
Let us now look at how Enterprise Manager’s “Database as a Service (DBaaS)” capability works with Database 12c and its container and pluggable databases.
With Oracle Enterprise Manager 12c, the Cloud Administrator is able to set up and offer the self-service of virtual machines (Oracle VM) with databases – this being more of an infrastructure cloud or Infrastructure-as-a-service (IaaS), since virtual machines are being provisioned on the fly.
The Cloud Administrator is also able to set up and offer the self-service creation of single-instance or RAC databases on existing Oracle Homes in what is known as a database pool for databases, or alternatively the self-service creation of schemas in an existing single-instance or RAC database in what is known as a database pool for schemas.
This self-service capability can also be called “Database as a Service” and “Schema-as-a-Service” respectively.
With the addition of Database 12c, the additional capability shown in the illustration above is the self-service creation of PDBs, in an existing single instance or RAC Container database (CDB). This is what we now call a database pool for PDBs. This self-service capability can be called as “Pluggable database (PDB) as a Service (PDBaaS)”.
Using this capability, the Cloud Self-Service Access (SSA) users are now able to self-service the provisioning of Pluggable databases inside a Container database very easily. This is the next stage of the Private Database Cloud.
For licensing aspects, as far as DBaaS is concerned, the Cloud Management Pack for Oracle database is required, with the pre-requisite of the Database Lifecycle Management (DBLM) pack – this is the pack that does all of the provisioning under the hood, whereas the Cloud Management pack adds the capabilities of self-service, quotas, chargeback/showback and so on.
This blog post was originally published at this link.

Tuesday, March 10, 2015

Managing Oracle Database 12c with Enterprise Manager – Part IX

We are discussing the management of Oracle Database 12c via Oracle Enterprise Manager 12c. In our previous blog post on this topic, we looked at how easy it was to set up Oracle Data Redaction for a 12c database using Enterprise Manager - this capability being used to hide confidential data on the fly as it is being displayed.
We will now look at the Database Resource Manager and its new capabilities to handle PDBs in Database 12c, and how it can be set up via Enterprise Manager.
Drill down to the 12c Database target in Enterprise Manager. From the home page of the database, open the Administration menu and choose Resource Manager. The Resource Manager Home page is displayed.
As can be seen, the Database Resource Manager is now CDB aware and allows you to create CDB Resource plans. This will control resources to Pluggable databases inside the CDB. Drill down to the CDB resource plans, this displays the following screen.
Click on “Edit” to define the CDB Resource Plan.

In the Resource Plan, by default, all the PDBs have a “share” of one (1) . There are three PDBs in this container database, as displayed on this page - “PDB(3)”. Therefore, each PDB has a share of 33%. 
You can add a separate Resource Allocation by clicking on the “Add/Remove” button.
Here we have added PDB3 as a separate entry with 2 shares to the Resource Allocation. The remaining PDBs have 1 share each.  This means 50% is the Resource Allocation of PDB3, and the other two PDBs have 25% each. This can be used to cover a scenario where PDB3 is an important application’s pluggable database, and more resources are required to be allocated to this pluggable database.
We are also activating this plan with a simple tick mark on this page. Click on Apply. And we haven’t written a single line of PL/SQL code (which is normally required to define a resource plan if not using Enterprise Manager).
For more detailed information on the powerful capabilities of the Oracle Database Resource Manager, please refer to the chapter “Managing Resources with Oracle Database Resource Manager” in the 12c Database Admin guide.
In the next blog post, we will look at more management aspects of Oracle database 12c using Enterprise Manager, in particular the ability to self-service the creation of PDBs using “Pluggable database (PDB) as a Service (PDBaaS)” – a form of Database as a Service.
This blog post was originally posted at this link.

Managing Oracle Database 12c with Enterprise Manager – Part VIII

We are discussing the management of Oracle Database 12c in Oracle Enterprise Manager 12c. In our previous blog post on this topic, we started to look at Data Redaction, and created a Policy on the HR user and Salary column. We specified Full Redaction as seen below, to hide all the Salary column figures (a zero will be displayed).

Click on “Show SQL”, this displays the PL/SQL code that is generated to create the Data Redaction policy. Even this simple example demonstrates how Enterprise Manager saves a lot of admin time, since it writes the PL/SQL code for the DBA. Fancy writing this manually?

The policy is created successfully. We can see that the policy is “enabled” and there is one redacted column.

So, the Data Redaction Policy has been created successfully and enabled. But how do we verify this? 
From now on, any select statement issued by a non-SYSDBA should show the salary as zero (0) in each case.  This can be seen below in SQL Plus, logging in as the HR database user:

[oracle@db12c admin]$ sqlplus hr/@pdb3
SQL>  select * from employees;
NAME                                     DEPARTMENT               SALARY             JOINDATE
John Smith                            Science                           0                          01-MAR-13
Simon Pereira                        Commerce                      0                          01-APR-13

Whereas, if you login as sysdba, you can see the salary values  since no redaction is applied.

[oracle@db12c admin]$ sqlplus sys@pdb3 as sysdba
SQL>  select * from hr.employees;
NAME                                     DEPARTMENT               SALARY             JOINDATE
John Smith                            Science                           200000                01-MAR-13
Simon Pereira                        Commerce                     500000                01-APR-13

If the concept of Oracle Data Redaction interests you, then you can read further about it in the documentation here.
In the next blog post, we will look at how the Resource Manager can be set up for 12c Databases, via the powerful and time-saving interface of Enterprise Manager 12c.
This blog post was originally posted at this link.

Managing Oracle Database 12c with Enterprise Manager – Part VII

We are discussing the management of Oracle Database 12c in Oracle Enterprise Manager 12c. In our previous blog post on this topic, we looked at how user administration is performed for a 12c Database using Enterprise Manager, such as the creation of a common user for the CDB$ROOT.
Let us talk now about the capability of Oracle Data Redaction. This is a new Oracle Database 12c feature, which camouflages data on the fly when being displayed. It is a feature of Oracle Advanced Security i.e. it is licensed via theAdvanced Security Option (ASO).

Login to Enterprise Manager 12c Cloud Control, and open the (12c) Database target menu. From this menu, selectAdministration.. Security.. Oracle Data Redaction as seen above. This opens the Data Redaction page displayed in the following screenshot.
There are no Data Redaction Policies currently in this database. Click on “Create” to formulate a new policy. The Create Data Redaction Policy page appears.
Name the Data Redaction Policy as “SAL_POLICY” (any appropriate name), and select the schema as HR and table as EMPLOYEES, and also the policy expression. Here we have used 1=1 signifying that all rows will be used.
Note that the HR user was previously created in the PDB3 Pluggable database.
You can now add an Object column to the policy, by clicking on the “Add” button.
Select the Salary column and a Redaction template. Full Redaction will redact (hide) all the data in the column, i.e. the entire Salary figure will be hidden.  Since it is a Numeric column, it will be redacted with a Zero.
We will continue setting up Data Redaction via Enterprise Manager, in the next blog post. The Redaction policy will also be verified by a login test to the database.
This blog post was originally posted at this link.

Friday, February 13, 2015

Snap Clone using EMC SAN and ASM

By Pete Sharman

Recently we announced the latest update to Enterprise Manager Cloud Control 12c Release 4.  One of the enhancements in that release is support for Snap Clone on Automatic Storage Management (ASM) and EMC Storage. Before we examine the details of this specific enhancement, let's look at a quick refresher on what Snap Clone provides for you.

What is Snap Clone?

Snap Clone is a storage agnostic and self service approach to creating rapid and space efficient clones of large databases (and by large, we’re talking terabytes or more). Now that’s probably more buzz words in one sentence than anyone’s brain can deal with without exploding, so let’s explain some of those terms more:
  • Storage agnostic – by that I mean Snap Clone supports all storage vendors, both NAS and SAN. It can leverage storage layer APIs or layer a ZFS filesystem on top to provide copy on write.
  • Self service – in the XaaS world – where X can be any of I, MW, P and DB :) – one of the key features is empowering the end user to do the work, rather than waiting on some techie to find time in their otherwise busy schedules. So it’s the end user who makes the adhoc clones here, not the storage admin
  • Rapid – People simply don’t have the time to wait weeks for provisioning to happen any more, so you have to support the functionality to clone databases in minutes rather than the days or weeks things used to take.
  • Space efficient – When you’re working with terabyte or larger databases, you simply may not have the storage to create full-sized clones, so you have to significantly reduce the storage footprint to start with.
Over the various EM releases,  more and more functionality has been added to Snap Clone:
  • EM12cR2 provided Snap Clone for NAS storage (NetApp and Sun ZFSSA).  It provided RMAN backup based clones, and included the Snap Clone Analyzer to show you the storage savings you could make using Snap Clone
  • EM12cR3 added in support for Snap Clone using the Solaris File System (ZFS) and admin flows for Snap Clone for PDB’s (pluggable databases)
  • EM12cR4 added a lot more:
    • Snap Clone using CloneDB – this is the biggie, as it means Snap Clone can now be used with ANY Oracle database release that supports CloneDB, regardless of what storage it’s on
    • Data Guard standby as a test master – allows offloading the impact of creating the test master from your Production environment
    • NetApp Ontap 8.x cluster mode support
    • Certification for engineered systems, with I/O over Infiniband
    • Support for NFSv4
  • And in the latest plugin update that's just been shipped, we added:
    • Integrated data lifecycle management
    • Snap Clone using EMC SAN and ASM
    • Admin flows for test master creation
    • Integration with masking, patching, upgrades etc.

Snap Clone using EMC SAN and ASM

Most NAS technologies offer storage efficient clones in the form of Snapshots. The snapshots make use of underlying volumes, knows as flexvols (Netapp) or shares (ZFS). Unfortunately, SAN storage does not provide native snapshotting capability unless a file is created on it by leveraging TCP/IP over iSCSI over Ethernet. However this defeats the purpose of having high speed fiber channel fabric, not to mention that it makes little sense to overlay SAN with a filesystem. Another complaint we heard from our customers is that cloning is a data intensive operation that could flood the corporate IT backbone if Ethernet is used. Consequently, lot of our customers want native support for SAN for cloning purposes, especially, the ones who run ASM on SAN. And they are quite a lot in number.

Using Snap Clone on ASM and EMC storage provides the ability to create ‘live’ thin clones of databases that are on ASM. A live clone is NOT snapshot based but rather a live copy of the database, residing on copy-on-write storage technology, that can be within the same cluster or indeed another one. Both single instance and RAC are supported – supported versions are or higher of the database, 11.2 and higher of the Grid Infrastructure code. This functionality works on both EMC VMAX (with Time Finder VPSnap) and VNX storage appliances.

Diagrammatically, the configuration looks like this:

Why Use Snap Clone with EMC SAN and ASM

There are a number of major challenges that Snap Clone can be used to address:
  1. Lack of automation - Manual tasks such as provisioning and cloning of new databases (for example, for test or development systems) is one area that many DBA’s complain is too time consuming. It can take days to weeks, often because of the need to coordinate the involvement of different groups, as shown in the image below:
  2. When an end user, be it a developer or a QA engineer, needs a database he or she typically has to go through an approval process like this, which then translates into a series of tasks for the DBA, the sysadmin and storage admin. The sysadmin has to provide the compute capacity while the storage admin has to provide the space on a filer. Finally, the DBA would install the bits, create the database (optionally on Real Application Clusters), and deliver that to the user. Clearly, this is a cumbersome and time-consuming process that needs to be improved on.

  3. Database unfriendly solutions – Obviously, when there is a need looking for a solution, different people take different approaches to solving that need. There are a variety of point solutions and storage solutions out there, but the vast bulk of them are not database aware. They tend to clone storage volumes rather than databases and have no visibility into the database stack, which of course makes it hard to triage performance issues as a DBA. They also lack the ability to track configuration, compliance and data security issues, as well as having limited or no lifecycle capabilities. As mentioned before, DBAs would like to leverage the native FDDI protocol of SAN for cloning.  This will make cloning fast and efficient without disrupting regular network traffic.
  4. Storage issues and archaic processes – Of course, one of the main issues is storage. Data volumes are ever increasing, particularly in these Big Data days, and the growth can often outpace your storage capacity. You can throw more disks at the problem, but it never seems to be enough, and you can end up with degraded performance if you take the route of sharing clones between users. There can also be different processes and different priorities between the storage team and the DBA team, and you may still have fixed refresh cycles, making it difficult to clone on an adhoc basis.
So the end result of all of this is that far too often, there are competing priorities at odds. Users want flexibility – simplified self service access, rapid cloning, and the ability to revert data changes. IT, on the other hand, want standardization and control, which allows a reduction in storage use, reduction in administrative overhead, visibility into the complete database stack and lineage tracking.  

Snap Clone on EMC storage helps you to address all these competing priorities, using hardware you may already own.  Indeed, EMC is well established as an Oracle database storage vendor over many years, and that integration has become tighter and tighter over the past few years.  In addition to that, the actual setup and configuration can be simpler than is the case when using other hardware, as you do not need to create Database Profiles in this configuration.  Service Templates are created directly on either a single instance or RAC database that resides on ASM.  Because you're using this combination of ASM and EMC SAN storage, the database is already Snap Clone enabled as it resides on copy-on-write storage technology.

In my next post, I'll discuss more details of what else is new in the Snap Clone product in this latest release, so stay tuned for more details on that soon!

For More Information

You can see more details on how you actually set Snap Clone up on EMC storage by viewing the following screenwatches:
For more details on using Enterprise Manager Cloud Control 12c to provide Database as a Service functionality, visit the OTN page located here.

Blog Entry written by Pete Sharman.


Opinions expressed in this blog are entirely the opinions of the writers of this blog, and do not reflect the position of Oracle corporation. No responsiblity will be taken for any resulting effects if any of the instructions or notes in the blog are followed. It is at the reader's own risk and liability.

Blog Archive