50,39 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
Your ultimate guide to passing the OCP 12c Upgrade 1Z0-060 Exam
About This Book
- This is your one-stop solution to understanding what's new in Oracle 12c and how to bring these features to your systems in a hassle-free manner
- A complete guide to clearing the OCP Upgrade 1Z0-060 Exam
- Dive deep into Oracle administration and get up to date
Who This Book Is For
This book is for Oracle Admins who have a working knowledge of Oracle administration and now want to upgrade their knowledge to the latest version (Oracle 12c). This book is perfect for those who wish to pass the OCP Upgrade 1Z0-060 Exam.
What You Will Learn
- All of the new features of the Oracle 12c database that you are required to know for the OCP Upgrade Exam 1Z0-060
- New multitenant architecture introduced in Oracle 12c and how it works
- Monitor complex database operations and use the new enhanced SQL tuning features to tune bad SQLs
- Manage data over time using information life cycle management
- Make your data more secure by implementing new enhanced auditing
- Learn new security features and how to implement encryption to secure your data
- Explore how backup and flashback works with new multitenant architecture
- Performance management techniques that will analyze and tune your database
In Detail
This guide will get Oracle admins up to date with the latest developments in Oracle 12c. It includes all the necessary information that you need to implement in your existing systems.
All of the information in this book has been handpicked to help you study for the Oracle 12c upgrade exam. Each chapter has been written with the objective of helping you pass this exam with ease. Content in this book is aligned with the objectives of the exam, making it really easy to follow the course content. Every example mentioned in this book has been tried and tested in actual environment. Real-world examples will help you learn about new features such as multitenant containers database architecture, managing containers, pluggable databases, database administration enhancements, database auditing, tuning, backup, and flashback enhancements.
You will also learn about storage enhancements, security updates, tuning, troubleshooting, and backup enhancements. This book also covers section 2 of the exam course making this book a complete guide for passing OCP 12c upgrade exam 1Z0-060.
Style and approach
This book systematically covers various aspects of Oracle administration and provides the information necessary to pass the OCP Upgrade 1Z0-060 Exam Set 1 and Set 2.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 794
Veröffentlichungsjahr: 2016
Ähnliche
Table of Contents
OCP 12c Upgrade 1Z0-060 Exam Guide
OCP 12c Upgrade 1Z0-060 Exam Guide
Copyright © 2016 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
First published: November 2016
Production reference: 1241116
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham
B3 2PB, UK.
ISBN 978-1-78712-660-2
www.packtpub.com
Credits
Authors
Advait Deo
Indira Karnati
Copy Editor
Safis Editing
Reviewer
Osama Mustafa
Project Coordinator
Shweta H Birwatkar
Commissioning Editor
Veena Pagare
Proofreader
Safis Editing
Acquisition Editor
Divya Poojari
Indexer
Mariammal Chettiyar
Content Development Editor
Amrita Noronha
Graphics
Disha Haria
Technical Editor
Deepti Tuscano
Production Coordinator
Arvindkumar Gupta
About the Authors
Advait Deo has 12 years of experience in the database domain starting from Oracle Version 9i until Oracle 12c. By nature, a mechanical engineer, Advait transformed his career around Software and databases right from the beginning. He joined a consultancy company Tata Consultancy Services and 2004 and learned about database technology. In 2006, he moved to Oracle India Pvt. Ltd. where he was mentored by Indira Karnati. He later moved to Amazon.com as database administrator in 2010. He is currently working at Amazon as a senior database engineer.
Advait has worked in various aspects of Oracle databases and administered huge fleet of complex production databases. He is an expert in performance tuning and troubleshooting. He also focuses on schema design, automation using shell and Python, and architecting solutions to complex problems.
Currently, he is working on various products in Amazon Web Services (AWS) technology and providing database and storage solutions in AWS to various clients.
Advait is an Oracle Certified Professional in Oracle 9i, 10g, 11g, and 12c databases and Oracle Certified Expert in 11g RAC. His certification credentials are available at https://www.youracclaim.com/users/advait-deo. He also publishes some of his work and learnings on his website http://avdeo.com.
You can find him on LinkedIn at https://www.linkedin.com/in/advaitdeo
Prior to writing this book, Advait reviewed Oracle Database 11g R2 Performance Tuning Cookbook and Learning Linux Shell Scripting.
I would like to thank my wife for putting up with my busy schedule and supporting me all the way. This book would not have happened without her support. I would also like to thank my parents and my mentors who helped me over the years and who believed in me. Without them, I would not have reached to the place where I am now.
Without someone passing the knowledge to us, it makes it is more challenging to learn and understand the concepts and I feel everyone is obligated to pass on the knowledge that they gain, back to the community. This book, along with my blog, is a medium by which I would like to pass on the knowledge to the community, and I hope this will prove to be helpful to everyone.
Indira Karnati is an engineering graduate in electronics and communications and did her Executive General Management Program at IIM Bangalore, India.
Indira is an expert Oracle DBA and Apps DBA, having worked in various capacities for the past 19 years in IT, of which 15 years has been with Oracle Corporation, India Development Centre, Hyderabad. She has extensive hands on experience of installations, configurations of Oracle databases (single instance and RAC), Oracle Identity Management (OID/OAM/OIM), Fusion Middleware products, Oracle EBS, Oracle Fusion applications, Oracle Demantra, and Oracle iLearning.
Indira has rich experience in turning around the complex Oracle installations. Her expertise includes installation, configuration, cloning, refreshing, patching, upgrading, debugging many Oracle Products, the primary being Oracle Database, identity management, and enterprise manager. Her work experience covers high availability and disaster recovery solutions.
She is an Oracle Certified Professional in 12c and 11g in the Database Administration track, Oracle Certified Expert in 11g RAC, and Exadata Ceritified Implementation Specialist. Her Certification credentials are available at https://www.youracclaim.com/users/indira-karnati
Find her on LinkedIn at https://www.linkedin.com/in/indirakaranati
"Praise God, Seek God, Worship God, Trust God, Thank God" is one of the most important things that my father taught me in life. So, I thank God for making me achieve this milestone from the bottom of my heart. I would like to thank my parents for their blessings and teachings, which have made my life more meaningful. Without their inspiration, drive, and support I would not be the person I am today. I would like to thank my husband Srinivas, and my son Kartheek; I am so fortunate to have them in my life, but for their love and continued support in whatever I do, I would not have achieved this. And special thanks to my wonderful brothers Prasanna, Srinivas and Mohan Krishna for being there during all the difficult times to support. Also I would like to thank my ex-colleague, friend and co-author Advait for his invaluable effort in transforming this dream into reality, it's so wonderful knowing him and having worked closely with him.
About the Reviewer
Osama Mustafa (Oracle ACE) has progressive experience in Oracle Products, community. He recently served as Oracle Database Administrator, Osama Mustafa holds more than 25 Oracle certification in different products.
Provide Database implementation solutions, high availability solution, infrastructure and storage planning, install, configure, implement, and manage Oracle E-Business suite environments. Architect, build and support highly-available Oracle EBS, database and fusion middleware specialist, exalytics and exalogic expert.
Included to this Osama is volunteer in Oracle user group, international speaker, and author for Oracle penetration testing book, reviewer for Oracle book, organizing RAC attack around the world, board member in Oracle RAC SIG, volunteer in IOUG and ODTUG, publish online articles on his blog https://osamamustafa.blogspot.com and his articles published in Oracle magazine and OTech magazine.
Some of the book he reviews:-
Finally, I would like to take this opportunity to thank my family for supporting me specially my mother during this career and tolerate me during my life for the long working hours and traveling most of time.
www.PacktPub.com
For support files and downloads related to your book, please visit www.PacktPub.com.
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at [email protected] for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.
https://www.packtpub.com/mapt
Get the most in-demand software skills with Mapt. Mapt gives you full access to all Packt books and video courses, as well as industry-leading tools to help you plan your personal development and advance your career.
Why subscribe?
Preface
This book provides complete details about the new features introduced in Oracle 12c release 1 (12.1.0.1). This book will get Oracle admins up to date with the latest developments in Oracle 12c. It includes all the necessary information so you understand and learn how to implement the latest features of Oracle 12c in your existing systems.
In this book, all the information is organized so you can pass your Oracle 12c upgrade exam with much ease. Every chapter in this book is set in line with the objectives of the exam. With the help of real-world examples, you will learn concepts of new features such as multi-tenant container database architecture, managing container, pluggable databases, database administration enhancements, database auditing, tuning, backup and flashback enhancements etc.
What this book covers
Chapter 1 , Getting Started with Oracle 12c, this chapter introduces some of the new features related to installation and configuration of Oracle 12c. It also introduces a new XML based web tool in Oracle 12c – Oracle EM Database Express. We will also see other features that are introduced in Oracle 12c like emergency monitoring, real-time ADDM and compare period ADDM. Finally, it introduces us to the new ASH analytics page in Oracle EM cloud control 12c.
Chapter 2, Multitenant Container Database Architecture, this chapter introduces you to Oracle 12c multitenant architecture. We are going to look into the benefits of using multitenant architecture, different components of multitenant architecture, how to create and configure multitenant database and different ways to create pluggable database PDB and finally how to migrate a non-CDB database as pluggable database into a container database.
Chapter 3, Managing CDBs and PDBs, in this chapter we are going to see how to establish connection to container database and pluggable database. Performing general administration tasks such as startup and shutdown of CDB and PDBs. Managing tablespace, users, roles and privileges in CDB and PDBs etc.
Chapter 4, Information Life Cycle Management and Storage Enhancements, in this chapter we are going to introduce you to information life cycle management in Oracle 12c and how this has been remarkably automated in Oracle 12c. We will also show you have to move the datafiles online and other archiving solutions introduced in Oracle 12c like in-database archiving and valid-time temporal.
Chapter 5, Auditing, Privilege Analysis and, Data Redaction, this chapters introduces you to the new security features of Oracle 12c. We are going to look into the new auditing introduced in Oracle 12c called unified auditing. We will learn to create new auditing policy to meet our audit requirement and how we can move our database to use unified auditing. We will also introduce you to the new tool in Oracle 12c which allows you to perform privilege analysis. Finally, we will also see another new feature called data redaction and how it works in Oracle 12c.
Chapter 6, Database Tuning and Troubleshooting, in this chapter we will learn about new features related to performance tuning. Oracle 12c introduces real-time operations monitoring which is an extension of real-time SQL monitoring. We will also learn about new enhancements to SQL tuning like adaptive SQL plans, Dynamic statistics and SQL plan directives. This is one of the most important chapter for learning new features of SQL tuning in Oracle 12c. Finally, we will see some improvements related to statistics gathering followed by enhancements to histogram. We will conclude this chapter with enhancements related to SQL plan management (SPM).
Chapter 7, Backup and Flashback, in this chapter we are going to look into backup and recovery scenarios and how multitenant architecture affect these scenarios. We are also going to look into some of the RMAN enhancements in Oracle 12c followed by table restore and recovery using simple RMAN command. We will conclude this chapter by mentioning new enhancement related to flashback data archive.
Chapter 8, Database Administration Enhancements, in this chapter we are going to look into resource manager and how it will work with Oracle 12c multitenant database. We are also going to introduce some enhancement related to indexes and tables and at the end, look into ADR and network enhancements.
Chapter 9, Miscellaneous New Features, this chapter bundles the other new features introduced in Oracle 12c including enhancement related to Oracle data pump, SQL* Loader, online operations etc. It also provides new partitioning enhancements introduced in Oracle 12c and new top n SQL clauses in Oracle 12c.
Chapter 10, Core Administration, this chapter is more of a general administration and we are going to explain the fundamentals of database administration. We are briefly cover installation and configuration of database, setting up client and server connections, monitoring database alerts, different tasks performed by DBAs on daily basis, patching, backup and recovery, troubleshooting etc. We also cover data recovery advisor and how to use the same to recover the data. Later we cover implementing flashback technology and techniques for loading and unloading of data.
Chapter 11, Performance Management, in this chapter we discussed about designing the database for optimal performance, monitoring the performance of database to improve the same, analyze and identify performance issues and preforming real application testing to analyze the performance. We will also see how to use resource manager and tune our applications so that they perform the best.
Chapter 12, Storage, in this chapter we are going to introduce the logical and physical structure of database and how they are different. We will also look into Oracle ASM and how that can be used to manage database storage. We will talk about ASM disks and disk groups, managing ASM instances and automatic space management in Oracle.
Chapter 13, Security, in this chapter we will discuss about developing and implementing robust security policy for Oracle database. Auditing the actions in Oracle database, creating password file and authenticating privileged users remotely using password file. Finally, we will talk about encryption and how to use the same to encrypt Oracle data.
What you need for this book
This book needs an Oracle database 12c release 1 (12.1.0.1) software to be installed and a container database to be created. You can use any flavor of operating system that are certified with Oracle 12c release 12.1.0.1.
In terms of resources required, you should have at least 2GB of physical memory and around 10G of disk space.
For practicing ASM section, you need 4 raw devices added to the system for creating ASM disks and disk groups.
Who this book is for
This book is for Oracle Admins who have working knowledge of Oracle administration and now want to upgrade their knowledge to the latest version (Oracle 12c). This book is perfect for those who wish to pass the OCP upgrade 1Z0-060 exam.
Conventions
In this book, you will find a number of text styles that distinguish between different kinds of information. Here are some examples of these styles and an explanation of their meaning.
Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: "You can use the DBMS_ADDM package's COMPARE_INSTANCES function to compare two periods within the same instance."
A block of code is set as follows:
SQL> exec dbms_xdb_config.sethttpport(5500); PL/SQL procedure successfully completed.New terms and important words are shown in bold. Words that you see on the screen, for example, in menus or dialog boxes, appear in the text like this: "You can access the new ASH analytics page using Performance | ASH Analytics as shown in the following screenshot."
Note
Warnings or important notes appear in a box like this.
Tip
Tips and tricks appear like this.
Reader feedback
Feedback from our readers is always welcome. Let us know what you think about this book-what you liked or disliked. Reader feedback is important for us as it helps us develop titles that you will really get the most out of. To send us general feedback, simply e-mail [email protected], and mention the book's title in the subject of your message. If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide at www.packtpub.com/authors.
Customer support
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
Downloading the example code
You can download the example code files for this book from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
You can download the code files by following these steps:
Once the file is downloaded, please make sure that you unzip or extract the folder using the latest version of:
The code bundle for the book is also hosted on GitHub at https://github.com/PacktPublishing/OCP-12c-Upgrade-1Z0-060-Exam-Guide. We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!
Downloading the color images of this book
We also provide you with a PDF file that has color images of the screenshots/diagrams used in this book. The color images will help you better understand the changes in the output. You can download this file from https://www.packtpub.com/sites/default/files/downloads/OCP12cUpgrade1Z0060Examguide_ColorImages.pdf.
Errata
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books-maybe a mistake in the text or the code-we would be grateful if you could report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded to our website or added to any list of existing errata under the Errata section of that title.
To view the previously submitted errata, go to https://www.packtpub.com/books/content/support and enter the name of the book in the search field. The required information will appear under the Errata section.
Piracy
Piracy of copyrighted material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works in any form on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at [email protected] with a link to the suspected pirated material.
We appreciate your help in protecting our authors and our ability to bring you valuable content.
Questions
If you have a problem with any aspect of this book, you can contact us at [email protected], and we will do our best to address the problem.
Chapter 1. Getting Started with Oracle 12c
Oracle released a new version of Oracle 12c in June 2013. This is by far the most advanced version of the Oracle database containing some of the best features and improvements that have been released so far. We are going to start looking into all the new features of Oracle 12c (12.1.0.1) in each of the coming chapters. Let's begin our journey with the installation of Oracle 12c and what new things we see during installation.
In this chapter, we are going to cover the following topics:
Installation and configuration using OUI and DBCA
Oracle 12c has lots of new features introduced and it all begins right from the creation of the database. To begin with, when we create the database at the time of installation using Oracle Universal Installer (OUI) or separately after installation using Database Creation Assistance (DBCA), we can see new options have been introduced in the Oracle Call Interface (OCI) and Database Configuration Assistant (DBCA). Oracle database 12c can be created as a multitenant container database containing multiple pluggable databases. So when we create the Oracle 12c database, we have an option to create the database as a multitenant database with zero or more pluggable databases. A pluggable database provides an option to consolidate many databases as pluggable databases into a single container database making it easier to administer and maintain them together along with many more benefits.
DBCA in Oracle 12c lets you create three types of databases:
The following screenshot shows new options while creating the Oracle 12c database:
As you can see from the preceding screenshot, we can create the new database as a multitenant container database (by checking the checkbox) or a normal non-container database as it used to be until Oracle 11gR2 version. Also, if we select the option of creating a multitenant container database, we can either create the database as empty with no pluggable databases or a number of pluggable databases.
Managing PDB databases using DBCA
Before we check the details of configuring pluggable database using DBCA, I would like to give a simple definition of pluggable database in order to understand the configuration details. A pluggable database is a user created database containing application data. It has all schema and non-schema objects and appears to end user as normal database. But pluggable database needs to be part of main container database (CDB). Pluggable database cannot be created without a container database.
You can manage and configure a pluggable database using DBCA. When you launch DBCA, you get an option to manage pluggable database as shown in the following screenshot:
When you click on Manage Pluggable Databases, you get multiple management options for pluggable databases as listed in the following screenshot:
Using EM Database Express
Until the previous release of the Oracle database, we used to have built-in Oracle EM database control which was a GUI tool to manage the Oracle database. Oracle EM database control used to have complete access for an individual database and was even capable of doing most of the administration tasks and intrusive changes to databases including shutdown/startup and configuration changes.
Starting with Oracle 12c, Oracle EM database control has been deprecated and replaced by Oracle EM Database Express. It is a lightweight monitoring tool providing a web-based interface for performance monitoring, configuration management, diagnostics, and tuning.
The following figure shows how EM Express works:
EM Express is available out of the box after creation of the database, so every database has a separate EM Express URL. It runs inside a database with minimal CPU and memory resources because the database only runs SQL calls and UI rendering is actually done by web browser on the client site. EM Express uses a web-based console that communicates with a built-in web server available in XML DB. Once a request is made on the web console, an EM servlet handles the request, including authentication, session management, compression, and so on. The servlet processes requests for reports by running the required SQL in the database and returns XML pages containing the output data. These XML pages are then rendered by the web browser on the client site. Often a single request is made per page to reduce round-trips to the database.
The following are the operational features of Oracle EM Express:
Note
EM Express cannot be used to shut down or start up a database. We can however use it to change any database parameter, as it is very good in diagnosing and analyzing bottlenecks in a database.
Configuring EM Express
The Oracle database EM express configuration is very simple. If you are installing the Oracle database using DBCA, it provides an option to configure EM Express as shown in the following screenshot:
You only need to provide the EM Express port number. This port will be used in the URL to access EM Express. If you have multiple databases on the same server, you need to make sure to have different ports assigned to EM Express in each database. We cannot have one Oracle database EM Express to monitor all databases in a host.
If you have manually created the database, you can configure the port as either SSL or non-SSL:
dbms_xdb_config.sethttpport procedure for non-SSL connection using the command:
SQL> exec dbms_xdb_config.sethttpport(5500); PL/SQL procedure successfully completed.dbms_xdb_config.sethttpsport for SSL connections using the command
SQL> exec dbms_xdb_config.sethttpsport(5500); PL/SQL procedure successfully completed.You can also check the port number that is configured for EM Express using dbms_xdb_config.gethttpport for non-SSL connections or dbms_xdb_config.gethttpsport for SSL connections as shown in the following code:
SQL> select dbms_xdb_config.gethttpsport() from dual; DBMS_XDB_CONFIG.GETHTTPSPORT() ------------------------------ 5500Once you configure the port, EM Express can be accessed using the following URL:
https://<hostname>:<port>/em
In our case it would be https://192.168.56.20:5500/em. You can use system user to access EM Express.
Access control for EM Express
You can use a system user and password to access EM Express. A system user has access to all functionality of EM Express. Oracle also provides EM_EXPRESS_BASIC and EM_EXPRESS_ALL roles which can be assigned to different users to control access to functionality in EM Express.
EM_EXPRESS_BASIC, which includes SELECT_CATALOG_ROLE, gives read-only permissions to users, whereas EM_EXPRESS_ALL gives all administrative privileges.
Exploring Oracle database EM Express
Oracle EM Express is a lightweight web-based tool for managing the Oracle database 12c. This tool is built inside the Oracle database and does not have any moving parts outside. As explained previously, we need to configure a port on which we can communicate with an XMLDB component inside the database and EM Express starts working. We will be looking into the different sections of EM Express and how this tool will help DBAs in managing the Oracle database 12c.
Oracle database EM Express has four major sections:
Each section provides key information in the database in respect to namespaces. Before checking details about each section, let's quickly go over the home page of EM Express.
The home page provides a quick overview status of the database, the uptime, and other database-related information in the Status section. It also has a Performance section, which provides information about database performance in terms of IO, waits, CPU, and so on.
The home page provides resource utilization in terms of host CPU, memory, and active sessions. We can also see currently running jobs in the Running Jobs section as well as top bad performing SQL in the SQL monitor - Last Hour section. The SQL monitor section provides performance metrics for top bad performing SQLs in the database.
The following shows the screenshot for the home page of EM Express:
At the top of the home page, we can see four different menus which can take us to different sections in EM Express. Let's go through the sections one by one:
For example, if we click on Initialization Parameter section, it will take us to the initialization parameters page, where we can view or modify initiation parameters.
On many pages, when we take some action (for example, changing the parameters and so on), we have a Show SQL button which provides us the SQL that will be run in order to take the required action. DBA can copy the SQL and run it in SQL prompt to make same changes.
Storage: This includes table space, undo management, redo log groups, archive logs, and control files.Security: This includes users, roles, and profiles.Performance: This menu includes the performance hub and SQL tuning advisor.Emergency monitoring and Real-Time ADDM
Until now it has been very difficult to understand the root cause of the issue causing database slowness especially in situations where the database has become completely inaccessible. Getting the database back to normal usually required bouncing the database.
But what if we have some mechanism to get inside the database and query some statistical data during the time when it has become completely slow and is inaccessible to the normal user? This will help a lot in understanding the root cause and in many cases even fixing the root cause, thus preventing the bounce of the database. This is exactly what emergency monitoring and Real-Time ADDM does.
Slow system
We have observed many times that a database becomes slow. When this happens, we see that either the connection to the database is slow, or queries run slow, or even EM page refreshes run slow. Also, many times users complain that applications are running slow.
With DBA, how do you try to find out the reason for the slowness? It could be a session holding too many locks, or some DML which is causing slowness or any other issue. DBA usually start the performance analysis using a regular ADDM report. But we can see following limitations in generating an ADDM report:
As a final resort, DBA can go for bouncing the database. But after the database is bounced, you might be losing the analysis data from the memory and it would be really difficult to identify the root cause of the problem that caused the database to slow down. If we don't know the root cause, we cannot apply the fix and if we don't fix the issue, it can cause database slowdown again and will incur another downtime.
So to identify the root cause before we bounce the database, it's important for us to have some kind of a lightweight tool which can get the required analysis data from the memory buffer without taking much resources and provide us the root cause of the problem. Oracle 12c has introduced a new tool, Emergency Monitoring, for doing analysis in such a situation.
Emergency monitoring
In the previous release, we had the memory access mode, which could be enabled and disabled explicitly using enterprise manager. Starting the memory access mode in enterprise manager starts a collector process, which was used to collect data from memory for further analysis.
In Oracle 12c, we don't have the memory access mode in enterprise manager. Instead we have Emergency Monitoring. We don't have to switch between modes in case of emergency monitoring. Emergency monitoring can be enabled by accessing the Emergency Monitoring page from Oracle database cloud control as shown in the following screenshot:
Before shutting down the database, you can launch emergency monitoring, which allows you to perform a quick performance analysis of a database instance even in a situation where we cannot connect to the database. This is possible because emergency monitoring connects to database SGA memory in a diagnostic mode bypassing all other IO and global resources.
As soon as you access the emergency monitoring page in Cloud Control, an agent connects directly to SGA data, bypassing the SQL layer to get performance statistics. Data collection stops when you navigate away from the emergency monitoring page to regular performance monitoring.
Emergency monitoring uses data from ASH buffers in memory to populate a performance page. ASH buffers generally contains the data for last 60 minutes and these buffers are rolling buffers so older data gets overwritten. It may not have enough history in following cases:
When you enable emergency monitoring, you can view following information:
The hang analysis page helps you to identify the exact session or, in some situations, multiple sessions which are blocking all other sessions. You can kill the main session which is blocking all other sessions and causing the database to hang. This should fix the hang issue right away in most situations. In a certain situation, if it doesn't help or if there are no blocking sessions, then you have to go for database reboot. The following shows emergency monitoring page in EM Cloud Control 12c:
Real-Time ADDM
Emergency monitoring is good for identifying the top blocking sessions or checking ASH real-time data to understand the problem with the database. But sometimes the root cause is complex and emergency monitoring is not helpful in situations where we are not able to see any top blocking sessions or ASH data does not show any concrete issue with the database.
Real-Time ADD analysis provides a complementary analysis to emergency monitoring. It provides a deeper root cause analysis that emergency monitoring does not provide.
Real-Time ADDM analysis is available through Oracle Enterprise Manager 12c and provides the detailed analysis of the issue along with the root cause of the issue. It helps DBA to resolve the critical issues such as database hang without the need to restart the database. This feature is different to emergency monitoring in that you log into the database during the database hang to get the real-time statistical data to fix the issue. Emergency monitoring does not provide a root cause analysis.
Working of Real-Time ADDM
Real-Time ADDM works in similar way to normal ADDM to analyze performance. Normal ADDM uses an AWR snapshot to analyze and provide the findings and recommendations to let you know what needs to be changed to improve the performance.
Real-Time ADDM uses the data from ASH buffers in SGA memory. Real-Time ADD does not use an AWR snapshot to perform the analysis. With Real-Time ADDM, you can connect to the database in either mode depending on the state of the instance:
For example, you can encounter such a situation when you have exhausted all the connections to the database and you cannot make another connection. Similarly, you have filled up all the space where audit trace files are stored and when you try to make a new connection, Oracle wants to create a new audit trace file and there is no space left to do that. In that case, you cannot log into the database and so Real-Time ADDM will help you a lot.
Automatic triggering of Real-Time ADDM
Oracle also enables triggers, which automatically start collecting Real-Time ADDM analysis data. This is required so that DBA does not have to use Real-Time ADDM and collect analysis data manually whenever there is an issue. So Oracle makes sure that if a certain threshold is crossed, it should automatically start collecting Real-Time ADDM analysis data that would be useful later to perform the analysis.
To automatically collect Real-Time ADDM analysis data, Oracle relies on the Manageability Monitor (MMON) background process and runs every 3 seconds using in-memory data without the need for any latches or locks. This helps in collecting Real-Time data without using any resources. The following are the triggering thresholds used by MMON to start collecting Real-Time ADDM data and store in the Automatic Workload Repository (AWR):
Data collected for Real-Time ADDM analysis is stored in the Automatic Workload Repository (AWR) view in DBA_HIST_REPORTS and DBA_HIST_REPORTS_DETAILS.
To make sure that automatic triggering does not happen too often, Oracle has implemented checks and fulfilling those checks only will trigger an automatic collection of Real-Time ADDM data. To avoid any performance impact caused by collecting the data, new reports won't be generated if a Real-Time ADDM report was generated in the past 5 minutes by an automatic trigger. Also, to avoid multiple triggers pointing towards a performance problem with same severity, a new trigger will come into effect if it has an impact of 100% or higher when compared to previous impact within the past 45 minutes. This applies to reports triggered by the same triggering issue.
Generating Real-Time ADDM report
We can use the following function to generate a Real-Time ADDM report:
Select dbms_addm.real_time_addm_report() from dual;This query, which uses the dbms_addm function real_time_addm_report, will get you a Real-Time ADDM report for the last 5 minutes.
Alternatively, you can get a Real-Time ADDM report from EM Database Express by doing the following:
You can also use Real-Time ADDM from EM Cloud Control 12c. The following screenshot shows the Real-Time ADDM menu from the Performance tab dropdown in EM Cloud Control 12c:
Once you click on Real-Time ADDM, it uses the data from ASH buffers in the SGA memory to perform the analysis. In the following screenshot of the ADDM page in EM Cloud Control, you can see the findings tab, hang data tab, and a few other tabs along with the statistics tab:
Emergency monitoring versus Real-Time ADDM
If you are confused about when to use emergency monitoring and when to use Real-Time ADDM, you can follow these guidelines.
Whenever a system becomes slow or hung, always start with emergency monitoring. Emergency monitoring is useful for finding any issue that stands out above other small issues.
For example, if we have a blocking session which is using lot of resources and causing the database to hang, emergency monitoring will highlight such sessions immediately. We also have ASH real-time data for doing the analysis of what is going on during the time of issue.
If nothing obvious stands out and you want to go for bouncing the database, you can give it a shot and run Real-Time ADDM analysis.
Real-Time ADDM does a much deeper analysis to find the root cause of the issue. It uses hang analysis and other key metrics such as IO to understand the analysis data. You can check hang analysis data as well as top activity data in Oracle Cloud Control 12c to understand the analysis and root cause. Real-Time ADDM also provides you with findings and recommendations just like normal ADDM that you can implement to resolve the issue.
Generating ADDM compare period
Imagine that your database performance is degrading over time and you need to understand what changed over the period of time which is causing performance to degrade. To check this out manually, you need to have an AWR snapshot when the database is running good (or a preserved AWR baseline) and take an AWR snapshot when database is running slow. You can then compare the two AWR reports and analyze what has changed which could have resulted in the degraded performance.
Comparing two AWR reports is both time consuming and error prone. That's why in Oracle 11g, Oracle has introduced an AWR comparison period report.
It takes two different snapshots as input and performs comparison of statistical values of two different time periods (a good one and a bad one), providing a single report that highlights the difference in performance between the two periods.
In the case of the AWR compare report, you can select two sets of snapshots from the existing preserved snapshots. So the first set of snapshots (two snapshots) will be from the previous timeline and another set of snapshots will be from the time where we encountered the issue. You can generate an AWR compare period report either in text format or HTML format and the report provides the difference values in critical areas such as wait events, response times of top queries, OS statistics, and instance activity.
In addition to comparing the snapshot at two different time period, an AWR compare report can also be generated for two different DB replays. We can set up different environments and run replay multiple times comparing the AWR statistics for two different snapshots or replays.
Regardless of the nature of the comparison-two different time periods or two different DB replays-you still need to analyze the huge volumes of performance metrics summarized in the report and identify the change that has happened between old time and current time to identify the root causes of the performance difference between them. The reports don't map the root causes to performance changes. So you have to guess what change could have caused the performance degradation.
How does the ADDM compare report help?
The ADDM compare report performs cause-to-effect analysis:
Unlike the AWR compare periods report, which tells you what exactly is the difference in performance between two periods, the compare period ADDM report tells you what you can attribute the changes to.
Workload commonality
Workload commonality defines how close the two workloads that we are comparing are. Are we comparing similar things? Are we comparing similar SQL statements in two periods? Is it the same application running during the two time periods?
