OCP 12c Enhancements for the DBA

Course Overview
Hi everyone, my name is Tim Boles. I'm a senior manager at Hitachi Consulting. I'm excited to be sharing my experience from nearly 18 years of working with Oracle database technologies. Welcome to my course, OCP 12c Enhancements for the DBA. This is another course in a series of courses to help the viewer prepare to take the OCP 12c Upgrade Certification exam. With each new release of Oracle there are new features and enhancements. In this course, we're going to focus on the ones that will be of particular interest to the database administrator. We're going to cover the ability to quickly determine database problems through enhancements and emergency monitoring, the Automatic Database Diagnostic Monitor, and the ASH Analytics page. There are new enhancements in the tools that unload and load data into the database. There are also new technologies in partition that increase their versatility and reduce the amount of time and effort for performing partition maintenance. By the end of this course you'll be familiar with the enhancements in Oracle 12c that will help you quickly diagnosis performance problems and changes in the system. You'll also have a good grasp on the new capabilities and its cleaner syntax of the Oracle utilities that load and unload data from the database. I hope you'll join me on this journey to learn some of the new features that will make the DBA's life a little easier, in the OCP 12c Enhancements for the DBA course, at Pluralsight.

Emergency Monitoring
Hello, my name is Tim Boles, and welcome to our course, OCP 12c Enhancements for the DBA. This course is part of a series that follows the story of Mark and a team of DBAs at Globomantics. The company is wanting to bring everything into alignment and standardize on Oracle 12c. The team is reviewing the new features of Oracle 12c in hopes to understand the best path forward and make sure everything can align with current processes when they do upgrade to Oracle 12c. Additionally, they hope all this review will help them to prepare for the OCP Upgrade to 12c exam. Many DBAs cringe at the call from users saying their performance is really slow or that their jobs are stuck. The stress level increases dramatically when there's a high-profile production system instance, and no one seems to be able to connect. The DBA will often start by running Automatic Database Diagnostic Monitor or an Automatic Workload Repository report to get an idea of what is going on. However, if connections are not being allowed, the entire situation takes on a whole new stress level. In many previous versions the DBAs are left with scrambling around trying to use OS tools to troubleshoot the issue or giving up entirely and just trying to solve the problem by balancing the entire system. Oracle 12c introduces 2 new features to help the DBA troubleshoot a database undergoing a severe performance deterioration or database hang. The Emergency Monitoring and the Real-Time ADDM go beyond the monitoring capabilities in earlier releases, and they allow troubleshooting even when the DBA cannot connect to the database in a normal manner.

The ADDM Compare Period Report
The DBA team at Globomantics has been using the Automatic Database Diagnostic Monitor since Oracle 10g. They're interested in seeing this new Compare Period Report. The ADDM has been around for several database versions. It requires the optional Diagnostic Pack and Tuning Pack to licensed. The control_managment_pack_access initialization parameter must be set to a value of Diagnostic+Tuning, and the statistics_level parameter should be set to TYPICAL or ALL. The ADDM tool works in three phases. It analyzes the AWR metrics, it will then diagnosis the root cause of performance problems, finally, it will recommend solutions to remediate the performance problems. DBAs can use this tool to help understand what changes they can make to improve performance during the time period covered by the report. But in the past it has been very difficult to compare two time periods to see what changes are causing a performance problem.

Diagnosing Performance Issues Using ASH Enhancements
The DBA team at Globomantics is excited to see that Enterprise Manager Cloud Control has some new enhancements in the way it displays Active Session History data. The V$ACTIVE_SESSION_HISTORY view has been around since Oracle 10g. It requires the Diagnostic and Tuning Pack license. It contains detailed snapshots of active database sessions taken once per second. A database session is considered active if it was on a CPU or it's waiting for an event that didn't belong to the idle wait class. The issue with the V$ACTIVE_SESSION_HISTORY view is that it contains many columns, which only a portion of are shown here, and as we said before, the data is collected once per second. So the amount of data can be overwhelming and you need to be good at writing analytical reports to pull meaningful inferences from it. Oracle 12c brings with it the Active Session History page, or as it's more commonly referred to as the ASH Analytics page. This provides a visually effective way to review the Active Session History information, and you have the view of the overall load on the system. There are numerous ways to filter on the data. You can see the information within the last hour over a 24-hour period, over the week, month, or you can even customize it. There are also several pull-down filters. Notice from here you can also choose a SQL statement to tune or create a SQL Tuning Set from a set of SQL statements. Mark and his team are very excited about getting into and using this and trying to understand it better. Let's do a demonstration next to see how it works.

ADR and Network Enhancements
In this module we'll talk about the new enhancements to the Automatic Diagnostic Repository and some network enhancements. We continue our story of Mark and his team of DBAs at Globomantics. They have some automated scripts that report errors and provide information to the auditor, so they're interested in the two new capabilities of the ADR that seem to be focused on data definition language logging. They don't currently have network bandwidth problems, but they're still interested in learning about the network enhancements for future reference.

SQL Enhancements for Data
This module will take the viewer through the SQL enhancements. In Oracle 12c Database, there have been several SQL enhancements that deal with data. The maximum size of character data type columns have been expanded. A new tool called the Database Migration to Unicode Assistant helps in migrating from standard character sets to Unicode, and there is a new SQL clause that let's you limit the number of rows returned by a query. Prior to Oracle 12c, regardless of the character set used, the maximum size of a VARCHAR2 and NVARCHAR2 was 4000 bytes, in RAW columns 2000 bytes. Oracle 12c does allow you to extend data types to 32767 bytes. Remember, these are bytes, not characters. If you use a multi-byte character set, then you may have less characters than the number of bytes. The basic way to enable the database to utilize the larger data type is to change the max_string_size from the default of standard to extended. Start the database in upgrade mode, then run the file Utl32k. sql to change the old maximum sizes for VARCHAR2 to the new larger values. There is a warning about this. You must run Utl32k. sql immediately after changing the max_string_size to be extended or you risk invalidating the database columns. The actual steps to do this vary depending on if you're performing the change in a non-container database versus a container database and its pluggable containers. In the next session we'll do a demonstration of extending the data types.

SQL*Loader and External Table Enhancements
This module introduces Oracle 12C Enhancements to SQL Loader and External Tables. These new features bring performance enhancements and simplified management of these type of activities. At Globomantics, Mark and his team of DBAs consistently receive files from the developers and users that contain data that needs to be uploaded to the Oracle databases. This is generally completed utilizing SQL*Loader or external tables. The DBAs want to check out what enhancements to Oracle 12c that might improve the process or load speed of these activities and also want to make sure nothing breaks in their current processes. This session is all about the Direct NFS Client utilization for SQL*Loader and external tables, other SQL*Loader enhancements, and Oracle external table enhancements.

Partitioning Enhancements
Partitioning is a feature often used in large databases with very large tables as a means to help improve performance accessing data in those tables. Mark and his team are interested in learning more of the enhancements added to Oracle partitioning in Oracle 12c. Partitioning can enhance data access performance and reduce total cost of ownership of storing large amounts of data by allowing the subdivision of tables, indexes, and index-organized tables into smaller pieces. This can enable database objects to be managed and accessed at a finer level of granularity. In previous versions of Oracle, performing maintenance on these partitions could be very complex, taking a substantial amount of planning and often taking an excessive amount of time to complete. Oracle 12c brings with it many new features that focus on the maintenance of partitions and its associated indexes. This session will cover each of these new features in detail and provide some demonstrations on many of them.