Thursday, October 15, 2015

Managing Oracle Database 12c with Enterprise Manager – Part XXI

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 tablespace level Heat Map in Database 12c, showing which objects were accessed with a full table scan. We now move to the Policy tab.
Information about the Compression and Storage policies is displayed. There are 2 storage policies present. Only one object has a policy enabled.  Drill down to the enabled policy.
The object displayed is the table HR.EMPLOYEES. This object has the policy enabled. Click ok to go back to the policy tab. On the policy tab, select the policy and click on “Execute Policy”.
In the window that appears, confirm by clicking on Execute. The policy will be executed. You can also see the PL/SQL that you would have to write to perform this simple action, if it were not for Enterprise Manager.
The evaluation job is submitted. You can go back to the evaluations tab, select the policy, and click on execution history.
This history shows that the job has been created.
On the main Policy tab, there is another button called “Policy Details”. This shows further details of the policy.
Finally, there is the button “Default Execution Settings” on the main Policy tab. This displays information about when and how the policy will be executed.
This has been a quick introduction to what is available in Enterprise Manager 12c for Information Lifecycle Management (ILM) in Database 12c. For more information on Managing the ILM Heat Map and ADO with Enterprise Manager, please see the documentation here.
This blog post was originally published at this link.

Managing Oracle Database 12c with Enterprise Manager – Part XX

We are discussing the management of Oracle Database 12c in Oracle Enterprise Manager 12c. In our previous blog post on this topic, we used Enterprise Manager and added Automatic Data Optimization (ADO) policies that enforceInformation Lifecycle Management (ILM) in Database 12c.
To view all the ADO policies and the Heat Map, select Administration.. Storage.. Information Lifecycle Managementfrom the 12c Database (non-CDB) Home Page in Enterprise Manager:
This displays the Information Lifecycle Management page.  The information you want is in two tabs, the Heat Map tab and the Policy tab. The Heat Map is seen below.

On this page, you can see the Database level heat map or go down to the Tablespace level, and then to the Object level.  Here we have displayed the heat map for the EXAMPLE tablespace by clicking on the "Show Additional" button and selecting this tablespace.
We have then selected a date range, and view by “Last Full Table Scan Date”. This displays the tables that have experienced a full table scan in the date range we have selected. The green color specifies that the date range did not see a very heavy access of these tables; otherwise a different color would have been used for the object that was more heavily accessed.
In the table in the bottom half of the screen, we have searched for the HR schema. A list of objects belonging to the schema is displayed, along with the associated IML policies if any, and other information about the objects.
Let us move to the Policy tab.
This blog post was originally published at this link.

Managing Oracle Database 12c with Enterprise Manager – Part XIX

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 how Enterprise Manager helps with Information Lifecycle Management (ILM)in Database 12c. We changed the database initialization parameter heat_map to ON.
As the next step, we can add an Automatic Data Optimization (ADO) Policy by editing an Existing Tablespace as shown in the following screenshot.
In this case, we have selected the USERS tablespace, and are specifying a segment level Storage Tiering policywhereby we move newly created (from here on) objects in the tablespace  to a second tier storage tablespace, if the object has not been modified upto a period of 3 months.
The second tier is built on a more economical storage system, as opposed to the first tier high performance storage system. After creating the ADO policy in this manner, if we create a new table in the same tablespace, it will inherit the tablespace ADO policy .
Or, we can add an ADO Policy by editing an Existing Database Object:
This allows you to add a new ADO Policy to an existing database object.

This blog post was originally published at this

Managing Oracle Database 12c with Enterprise Manager – Part XVIII

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 Information Lifecycle Management (ILM) in Database 12c, and we will now see how Enterprise Manager helps with that.
Enterprise Manager Cloud Control 12c allows the management of ILM in Database 12c. This was enabled via the DB Plug-in release released 4 months after Enterprise Manager PS2  i.e. Release 3 (released in July 2013).  The DB plug-in release was in October 2013.
The following was included in the Oct. 2013 release:
  • ILM Heat Map for DB & Tablespace
  • ILM Administration feature to setup ILM policy on Tablespace & Objects
Note that Automatic Data Optimization (ADO) is not supported currently on CDBs and PDBs. It only works on a non-CDB 12c database.
The first step is to change the database initialization parameter heat_map to ON as seen in the following screenshot:

This blog post was originally published at this link.

Managing Oracle Database 12c with Enterprise Manager – Part XVII - Information Lifecycle Management (ILM)

We are discussing the management of Oracle Database 12c in Oracle Enterprise Manager 12c. In our previous blog post on this topic, we were exploring the different tabs in the Performance Hub of Enterprise Manager Database Express 12c. Let us now look at Information Lifecycle Management (ILM) in Database 12c, and how Enterprise Manager helps with that.
Oracle Database 12c has an excellent feature known as Information Lifecycle Management (ILM) which is now fully automated in moving data between different storage, depending on data age and access. The Heat Map and Automatic Data Optimization (ADO) features of Database 12c can be used to implement your ILM strategy, along with the Partitioning, Advanced Compression, and Hybrid Columnar Compression (HCC) options/features of the Oracle Database.
The "Heat Map" feature automatically tracks access to segments (tables and partitions) and rows and blocks.  The ADO feature allows you to define policies at the tablespace, table, partition, or row level. The policies contain conditions and associated actions.
The conditions are based on the data collected by Heat Map, and the actions can either be storage tiering (move the segment to another tablespace) or compression tiering (move the segment or block to a different level of compression).
The actual syntax of setting these ADO policies is not complex, but in totality, setting up many different ADO policies for storage tiering or compression tiering manually at the command line, and keeping track of those policies manually by a DBA, would work out as a complex task.
To alleviate that kind of task, Enterprise Manager would be very useful. Database 12c DBAS would want to use Enterprise Manager for this purpose.
More on the use of Enterprise Manager for ILM in the next blog post.
This blog post was originally published at this link.

Separate or one Enterprise Manager environment for managing prod and non-prod targets

A friend wrote:
I know we can manage all targets using one single Enterprise Manager 12c environment but I know there are advantages of having two separate environments, one for Prod targets and the other for non-Prod targets.  Please explain pros and cons of both architectures, so that we can decide one over the other.
My answer:
Mate, the benefits of a single Enterprise Manager install for Prod and non-prod are manifold:
  1. Single pane of glass to see and manage and monitor all software and hardware, whether prod or non-prod
  2. Ability to control access between DBA teams. A dev DBA will not be able to even see a prod target if the access is properly set up.
  3. Several EM packs work more effectively if targets are in 1 EM system. For example, comparing configurations between dev, test and production is only possible if all targets are together. Change management – copying schema changes from development  to test to production – works seamlessly if all the targets are together. And so on.
  4. A separate EM for separate targets will increase EM management overhead. You will need to patch the OMS as well, so now you are patching two instead of one, and so on.
  5. Everything in EM, including things like monitoring templates, will need to be duplicated.
  6. Defeats the idea of Database as a Service, if the company wants to move to that in the future. If targets are in different EM systems, you cannot use them in database pools for the same self-service user defined in an EM system. The self-service user now will have two self service screens, one for production, one for test. This may work however with a separate orchestration engine, like Oracle BPM.

Regarding the advantages of separate systems, do tell me. Security? Less load on the OMS from non-prod targets? Most of these points and objections ultimately turn out to be non-valid. Nothing beats a centralized EM installation managing and monitoring your entire enterprise.
This blog post was originally published at this link.

Tuesday, July 21, 2015

Snap Cloning Databases on Exadata using Enterprise Manager

By Subhadeep Sengupta (Oracle)
Historically, Exadata has mostly been deployed for heavy, production workloads, leaving cheap commodity hardware and third-party storage to perform as infrastructure for Development and Testing. From the viewpoint of Enterprise Manager, we have seen customers clone production databases running on Exadata to secondary storage such as ZFS Storage Appliance or even third-party NAS or SAN for the purpose of testing. Customers mainly used RMAN (with or without Enterprise Manager) to clone the databases. While the master clones (often referred to as Test Master) could be further cloned via storage efficient snapshots, there were significant limitations to the approach.
  • First of all, the testing on non-like systems from Exadata (both compute and storage) often yielded erroneous inferences. 
  • Second, both the compute and storage on existing Exadata racks often remained underutilized.
Most surveys establish that there are several Dev/Test copies for every Production database, and leaving Dev/Test outside the realm of Exadata can only yield partial usage of engineered systems. 

Two recent advancements in Exadata break this existing barrier. First of all, the compute nodes on Exadata can now be virtualized. Consolidated environments can now use Oracle Virtual Machine (OVM) on X5-2, X4-2, X3-2, and X2-2 database servers to deliver higher levels of consolidation and isolation between workloads. The Virtual machines can be configured on demand with the appropriate number of Virtual CPUs (vCPUs) for iterative testing. Second, with Oracle Database 12c Release 1 (12.1) release BP5 or later, space-efficient database snapshots can now be quickly created for test and development purposes on Exadata.

Snapshots start with a shared read-only copy of the production database (referred to as the Test Master in Enterprise Manager Parlance) that has been masked and/or cleansed of any sensitive information. Snapshot technology as deployed on Exadata is "allocate on first write", not copy on write. As changes are made, each snapshot writes the changed blocks to a sparse disk group. Multiple users can create independent snapshots from the same base database, therefore multiple test and development environments can share space while maintaining independent databases for each task. The base database must remain read-only during the usable lifespan of snapshots that are created from that base database. If there is a need to create another set of snapshots from a more recent copy of production data, a new read-only base from a production database needs to be created.

Enterprise Manager 12cR5 leverages the capabilities of Exadata to extend the Snap Clone capabilities for Exadata Sparse clones. As shown in this tutorial, with Snap Clone, Enterprise Manager can create a Test Master using either Dataguard technology or RMAN preceded by data masking. The Test Master can be created either on the same Exadata rack or on a different one. Once the Test Master has been created, snapshots can then be created on the sparse disk groups using the Deployment Procedures. The Deployment Procedures also automate the post-cloning discovery and promotion of the cloned targets, making them fully managed right from inception. Internal testing confirms that for cloning a Terabyte of database with a complete discovery of all its components takes less than a minute.

Enterprise Manager also helps DBAs track the lineage of the clones by providing a report on the production database, the Test Master and its clones. Enterprise Manager Snap Clone on Exadata supports both regular as well as pluggable databases with optional ACFS configuration. In addition to support for Exadata sparse clones, Snap Clone continues to support NAS (ZFS Storage Appliance and NetApp) and SAN (certain EMC storage arrays), in case users want to deploy these for their Dev-Test environments. 
Further Reading Resources
  • To learn more about Database as a Service visit the otn page.
  • Prerequisites for setting up Exadata Snapshots are documented here.
  • Watch the video, Snap Clone Multitenant (Pluggable Database) on Exadata here.
--Subhadeep Sengupta (@Subh_here


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