EM 13c - alfredokriegdba.com

Upgrade Your DB to 19c As A PDB using Enterprise Manager 13c

With Oracle Database 19c being the long term release, makes sense to start planning the upgrade of the DBs running on previous releases. Even better, what about converting Non-CDB databases into PDBs at the same time?

Using Oracle Enterprise Manager (EM) 13c, we can kill two birds with one stone. That is upgrade our DBs to 19c and migrate them into a PDB at the same time.

How this works? First of all, this functionality is part of the Database Lifecycle Management Pack. You can find the documentation for this feature along with all the pre-reqs below:

Click here

Let me guide you through this process.

In this environment I have a single instance database “orders”. As you can see on the screenshot below, is currently running version 18.10.

Orders DB Running 18.10 And Db19c Running 19.3 Version

Let’s now navigate to Enterprise -> Provisioning and Patching -> Database Provisioning

Locate the Provision Pluggable Databases link under Related Links section

This will open the Provision Pluggable Database dashboard.

Select the Container Database which is out target. Click on Migrate Existing Databases and then click the Launch button.

Select the desired migration method. You can choose between Data Pump Full Transportable Export and Import or Plug as a PDB. There are cons and pros on each method. Review the documentation previously shared so you can choose the best option for your migration.

For the purpose of this example we are going to choose Data Pump Full Transportable Export and Import.

Now add the source DBs to be migrated.

Add or select the required Named Credentials and provide the path for the Export Directory.

Provide the required Destination Information and click Next.

EM will execute compatibility and validation checks. If there are no errors click Close to proceed.

Type a name for the EM job and select the desired schedule for it to run.

Review all the information provided and click Submit.

Wait for the Confirmation pop-up window and click View Job.

Monitor the progress of the deployment procedure until it completes.

Confirm that the db19c container DB now has the ORDERS PDB.

Click on the ORDERS PDB and verify the version information.

We now have migrated our orders DB into the db19c container running 19.3 version.

I also recommend to take a look at MOS Doc ID 2694986.1 before attempting the migration.

Hope this helps to ease the migration process for your DBs.

Thanks,
Alfredo

Oracle Database Consolidation Workbench

During database migrations, Oracle Database Administrators (DBA) face a series of challenges that could make the migration success unpredictable. Oracle Real Application Testing pack combined with Oracle Enterprise Manager 13c offers a tool that simplifies and predicts the database migration and consolidation process.

Oracle Database Consolidation Workbench enables DBA’s to create consolidation projects and scenarios where they can input different variables and run different analysis before even attempting the migration. This helps predicts the migration and consolidation process, but it doesn’t end there. Oracle Enterprise Manager 13c also implements the migration and validates the migration using SQL Performance Analyzer (SPA).

Below is a 10 minute overview video of the Oracle Database Consolidation Workbench.

Thanks,
Alfredo

Do You Trust SQL Tuning Advisor?

Today’s post is about SQL tuning. During my more than 14 years of experience as a Database Administrator I haven’t found a better tool than Enterprise Manager (EM) to monitor Oracle’s DB performance. The “Top Activity” graph has been a constant during all these years.

Note: The EM’s Top Activity page has been deprecated in EM 13.3 PG.

click here

So let’s start from the beginning. If I receive a call or ticket stating that the DB or a process in the DB is slow, the first thing I do is to jump to the “ASH Analytics” page in EM. This page is part of the “Diagnostics” DB pack.

Here I can spot right away SQL_ID 6kd5jj7kr8swv being the top CPU consumer followed by other 3 SQL_ID’s. I’m going to create a SQL Tuning Set (STS) containing all these SQL’s.

From the STS dashboard I’m going to to click on “Create” to launch the wizard.

Step 1 requires a name and owner for the STS.

Note: You can create an empty STS and populate it later.

The next step is to load the STS with SQL’s. I’m going to choose “Load SQL one time only” and select from “Cursor Cache“. This because I’m sure they are loaded in memory. Another possible option is to load them from the AWR repository.

Next is to filter the SQL’s we want top load to our STS. I’m using the SQL ID row along with the IN operator to achieve this.

The next screen will ask you if you want to run that immediately or you want to schedule this for a later time.

And finally the review page. If everything looks right, then click on “Submit“.

Once the STS is created you will be able to see those 4 SQL’s loaded into it. I’m going now to schedule a SQL Tuning Advisor (STA) task to find out if these SQL’s can be tuned for better performance. STA is part of the “Tuning” DB pack.

Let’s review the results of the STA task we just created couple of minutes back. STA examined 4 distinct SQL’s. It only found recommendations for 3 of them with an overall potential benefit of 55 seconds in DB time.

Let’s click on “Show all results” button to see detailed information about the recommendations. The table below shows all 4 SQL’s along with their recommendations. We can spot right away the benefits of tuning SQL ID 6kd5jj7kr8swv. A SQL Profile could be created with a benefit of 57% or an Index with a benefit of 72%.

What do you do here? Do you hit “Implement All SQL Profiles” and cross your fingers hoping the new execution plan are going to be faster?

You don’t know how many times I saw sub-optimal plans generated by SQL Profiles.

That’s actually me fingers crossed hoping everything works!!!

There’s actually a better way to handle this. Real Application Testing (RAT) DB pack has a tool named SQL Performance Analyzer (SPA). This tool allows you to play the SQL before and after the changes (SQL Profile) and gives you a detailed report with the results.

In order to do that you’ll have to click on the “Validate All Profiles with SPA” button.

What is going to happen is that SPA will gather data before and after implementing the SQL Profiles and will produce a report.

Note: SPA will actually implement the SQL Profiles. Keep this in mind in a running Production system. RAT has also the capabilities to replay this workload in a Non-Production system. You can implement the SQL Profiles there without harming the Production performance.

Ooops! The SQL Profiles actually have a regression impact of -824%. Look at the Net Impact for the 1st SQL. It has a -830% impact, this means that the SQL Profile produced a sub-optimal plan. After this I will go and disable the recently created SQL Profiles before they cause a bigger impact.

My next step will be to propose the Index creation. This may have different implications depending on the application you are running, the business needs and operating procedures. I decided to implement the index to measure the performance benefit.

Here you can see how the performance was before the SQL Profile (maroon), with the SQL Profile implemented (turquoise) and with the index created (pink).

In summary, SPA is a great tool that helps you validate that the changes made to the DB system are working as expected. SPA not only validates SQL Profiles, it also validates statistics, DB parameter changes, DB upgrades and also you can verify the performance of your DB system with an Exadata storage server simulation.

Thanks,
Alfredo

Lifecycle Management Automation Series

Today’s infrastructure management is getting complex. We now have hybrid datacenters, multi-cloud solutions, virtual machines and so on. With all this complexity is difficult to manage configuration consistency, changes and to keep up with security vulnerabilities and patches.

Enterprises realize that is not possible to engage more human resources to overcome these issues that easy. Here is where Oracle Enterprise Manager (EM) can help to meet all these challenges by automating discovery of targets, provisioning, patching, cloning and upgrading databases. But more important, doing all these in a consistent manner and following compliance rules.

Lifecycle Management Pack in EM 13c contains a set of tools that provide:

Automated Discovery of hosts and targets
Provisioning DBs, Oracle GI, Linux VMs and Fusion Middleware applications
Patching of DB targets through the patching workflow and patch plans

Click here

Out-of-place patching using fleet maintenance
Full multitenant management support allows you to create, clone, plug/unplug and delete PDBs
Change management which captures and compares object definitions, parameters and data
Configuration management searches and compares configuration data across the enterprise

Click here

Target inventory tracker
Compliance management evaluates targets and systems as they relate to your business best practices for configuration and security
Enterprise data governance identifies DB’s that potentially contain sensitive data
Change activity planner enables you to to plan, manage and monitor operations in your datacenter

This EM pack is under an additional license covered under the Lifecycle Management pack. I’ll be covering all these tools one by one in future posts.

Additional information can be found in the Oracle EM Lifecycle Management administration guide:

click here

Thanks,
Alfredo