12c - alfredokriegdba.com

Avoid Transaction Errors Due To Tablespace Shortage

How many times we have had a call from application teams regarding a session error due to a tablespace (either DATA, TEMP or UNDO) getting 100% full?

How many times we had ask for an estimate of space used by a process, session or transaction?

There are several options that can help us overcome this type of issues.

a) If you are using Oracle Enterprise Manager (if you don’t, you should), make use of Corrective Actions to manage your data tablespaces.

http://www.alfredokriegdba.com/2017/01/oem-13c-new-features-corrective-actions.html

b) If your issue is more with tablespace quotas, TEMP or UNDO you may want to enable RESUMABLE session

RESUMABLE is an option which allows the session to go into a suspended state in such a way that it won’t fail/abort. The session cannot wait forever though, this is controlled by RESUMABLE_TIMEOUT initialization parameter.

In order to enable this option at the session level you have to execute below SQL statement:

SQL> ALTER SESSION ENABLE RESUMABLE;

Alertlog file will record if the session entered into suspended mode.

Some examples are:

###########
statement in resumable session ‘User TEST1(32), Session 3, Instance 2’ was
suspended due to
ORA-01536: space quota exceeded for tablespace ‘USERS’

###########
statement in resumable session ‘User TEST1(32), Session 15, Instance 1’ was
suspended due to
ORA-01562: failed to extend rollback segment number 4

###########
statement in resumable session ‘User TEST1(32), Session 23, Instance 1’ was
suspended due to
ORA-01652: unable to extend temp segment by 32 in tablespace TEMP

###########
statement in resumable session ‘User TEST1(54), Session 2, Instance 1’ was
suspended due to
ORA-01653: unable to extend table TEST1.TEST_RESUMABLE by 256 in tablespace USERS

While the session is into this suspended state it will account time to the “statement suspended, wait error to be cleared” wait event.

Once the error that sent the session to the suspended state is fixed, the session will resume its work.

More details on below Oracle’s documentation:

https://docs.oracle.com/cd/B28359_01/server.111/b28310/schema002.htm#ADMIN11581

Thanks,

Alfredo

RMAN restore slow due to ASMB process from ASM to filesystem

I was restoring a database running on ASM to an instance running on filesystem.

The step for cataloging files was extremely slow and after taking a look at the alertlog file, I noticed tons of errors related to ASMB process.

Well, turns out this is an unpublished bug explained in more detail on the below MOS note.

12c RMAN Operations from ASM To Non-ASM Slow Performance (Doc ID 2081537.1)

After applying the suggested patch (19503821) the performance went back to normal and the restore progressed as expected.

Thanks,

Alfredo

OEM 12c very slow after upgrade to 12.1.0.4

I noticed that OEM 12c console was very slow a few hours after the upgrade to 12.1.0.4 version.

Looking at the repository DB, found several OMS sessions consuming significant CPU resources.

Bug 19199023 explains that some SQL queries executed against the repository consume high CPU on the servers. This bug affects the DB plug-in 12.1.0.6 and the patch 19176910 should be applied to the plug-ins.

More information available on MOS note, 12.1.0.4 OEM: High CPU utilization on Repository Database due to SYSMAN query WITH TARGETGUID AS (SELECT target_guid, host_name FROM mgmt_targets (Doc ID 1912172.1)

Thanks,

Alfredo

Understanding Oracle SQL Plan Management SPM – Part 1

This time I want to talk about SQL Plan Management, explain how this feature works and how to make the best use of it. As is a very extensive topic I will split it in three posts.

Introduction

The Oracle Cost Based Optimizer (CBO) introduced in Oracle version 7. It determines the most efficient way to execute SQL statements after considering several factors such as database initialization parameters, optimizer statistics and many others like bind peeking, cardinality feedback, etc.

The CBO’s goal is to produce an optimal execution plan for the SQL statements executed in the database system. This is the reason why we want the CBO to be flexible enough to produce the best execution plans for the SQL statements. We must not forget that DB systems are dynamic enough to have data distribution changes, new Indexes may be added and initialization parameters could be modified, leading the CBO to produce multiple execution plans for the same SQL statement. This is an expected behavior of the CBO and we should be glad that is smart enough to produce optimal plans most of the times during the hard parsing, however sometimes it may also produce sub-optimal plans under special circumstances.

Here’s where plan stability tools come on the scene. Tools like hints, outlines, SQL Plan Management and custom SQL Profiles help the DBA and developers to allow only optimal plans be executed, hence avoiding severe performance problems within the database.

SQL Plan Management (SPM) is a new feature of Oracle 11g. SPM provides a framework for plan stability and control by ensuring that only selected plans are executed. If new plans are created by the CBO, they will not be executed until they are verified by the database or by the administrator and marked as accepted.

SPM controls the execution plans by using three control flags. The “ENABLED” flag that accepts two values YES & NO controls if the plan is available or not for the CBO to be considered. If the “ACCEPTED” flag is set to YES and “ENABLED” is set to YES, then the CBO will execute the plan. If “ACCEPTED” is set to NO, the plan should be verified by the database system or the administrator to check if it has comparable or better performance than the current plan. This process of making not “ACCEPTED” plans into “ACCEPTED” is called plan evolution. The last control flag name is “FIXED” and can only be set to YES if the plan is “ENABLED” and “ACCEPTED”, if set to YES plans have priority over “ACCEPTED” plans just because they are not expected to change.

SPM uses a mechanism called SQL plan baseline (baseline). Baseline is a group or set of “ACCEPTED” plans the CBO is allowed to use for a particular SQL statement. These baselines and the not “ACCEPTED” plans are stored into the SQL plan history. All baselines and the plan history are logically stored into the SQL management base (SMB) in the data dictionary. The SQL management base stores the statement log, plan histories, SQL plan baselines, and SQL profiles. Oracle provides information about SQL baselines in the DBA_SQL_PLAN_BASELINES and v$SQL system views. Database administrators can query information about what SQL baselines exists, their status and origin.

More information about DBA_SQL_PLAN_BASELINES can be found here https://docs.oracle.com/database/121/REFRN/refrn23714.htm#REFRN23714.

Oracle matches SQL statements with SQL baselines by using signatures. SQL signature is a unique identifier created from the normalized SQL text, uncased and whitespaces removed. SQL signatures ensure that the same SQL statement is always having the same SQL signature independent of the upper/lower case or spaces. SQL signatures are stored into the SQL management log in the SMB. If a SQL statement has its signature stored in the SMB, means to be a repeatable statement and a baseline will be created during the next execution.

SQL baselines also have three status flags. The first flag “REPRODUCED” is automatically set to YES when the CBO is able to reproduce the plan for the given SQL statement and NO when not possible, like when an Index is dropped. The second flag “AUTOPURGE” is user modifiable, if set to YES the plan will be purged when not used and reaches the SPM plan retention limit parameter. The last status flag “REJECTED” is set to YES when “ACCEPTED” is set to NO and the plan was verified (has LAST_VERIFIED), or “ENABLED” is set to no in 11gR2 and 12c versions.

SQL plan management is controlled by two initialization parameters. When the optimizer_use_sql_plan_baselines parameter is set to TRUE (default), the CBO will make use of the baselines created and stored in the SMB. The second parameter optimizer_capture_sql_plan_baselines is set to FALSE by default and controls if the database will automatically capture the SQL signature in the SQL management log and automatically create a SQL baseline on the second execution of the SQL statement. As a best practice and personal recommendation, don’t set optimizer_capture_sql_plan_baselines to TRUE. If set to TRUE, any baseline that is automatically created is also set to “ACCEPTED”, this means that the second execution of a SQL statement will always be used by the CBO even if there’s a better execution plan available.

In the next post I’m going to cover how to capture and evolve SQL baselines in 11g and 12c version, stay tuned!

Thanks,

Alfredo

Upgrade Oracle Enterprise Manager to 12.1.0.4

Enterprise Manager 12.1.0.4 was just released and now is time to test and document the process. The official documentation can be obtained from here (http://docs.oracle.com/cd/E24628_01/upgrade.121/e22625/upgrading_12101_PS1_gtgstrtd.htm) and this document cover the steps I followed and may not work on yours (I’m not responsible of any issues/problems caused to your system).

This document covers Oracle Enterprise Manager upgrade from 12.1.0.3 to 12.1.0.4 version in a Single-OMS configuration using one system upgrade method and is divided in 3 sections describing prerequisites, upgrade and post upgrade tasks.

Prerequisites

Download the software from (http://www.oracle.com/technetwork/oem/enterprise-manager/downloads/index.html) on your server and extract it to a stage directory.

mkdir /stage

unzip em12104_linux64_disk1.zip -d /stage

unzip em12104_linux64_disk2.zip -d /stage

unzip em12104_linux64_disk3.zip -d /stage

Ensure that you have a valid OMS (middleware home & inventory), Repository and Software Library backup.
If you have BI Publisher installed, then stop BIP server through WebLogic Admin Console.
Drop any snapshot created against Repository tables.

SQL> select master , log_table from all_mview_logs where log_owner=’SYSMAN’

no rows selected

Check and compile invalid objects in the Repository schema.

SQL> select ‘alter ‘||object_type||’ ‘||owner||’.’||object_name||’ compile;’ from all_objects

2 where status=’INVALID’;

no rows selected

Execute privileges on DBMS_RANDOM are required during the upgrade, run the below commands to set them.

SQL> grant execute on DBMS_RANDOM to DBSNMP;

SQL> grant execute on DBMS_RANDOM to SYSMAN;

SQL> revoke execute on DBMS_RANDOM from PUBLIC;

Copy the emkey from OMS to the Management Repository.

$ emctl config emkey -copy_to_repos

Oracle Enterprise Manager Cloud Control 12c Release 3

Enter Enterprise Manager Root (SYSMAN) Password :

The EMKey has been copied to the Management Repository. This operation will cause the EMKey to become unsecure.

After the required operation has been completed, secure the EMKey by running “emctl config emkey -remove_from_repos”.

This is a critical mandatory step. You must apply the following patches to your Repository database in order to avoid an upgrade failure (full list can be found http://docs.oracle.com/cd/E24628_01/upgrade.121/e22625/upgrading_12101_PS1_gtgstrtd.htm#BABGEHCI). In my case need to apply patch 12419378 for 11.2.0.1 database version.

Stop OMS, extended instances and both OMS and Repository agents.

$ emctl extended oms jvmd stop -all

Oracle Enterprise Manager Cloud Control 12c Release 3

Please enter the SYSMAN password:

Stopping all Engines

{}

No engines found for this operation

Emctl Extended verb failed. Please check /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/log/emctl.log for more details

$ emctl extended oms adp stop -all

Oracle Enterprise Manager Cloud Control 12c Release 3

Please enter the SYSMAN password:

Stopping all Engines

{}

No engines found for this operation

Emctl Extended verb failed. Please check /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/log/emctl.log for more details

$ emctl stop oms -all

Oracle Enterprise Manager Cloud Control 12c Release 3

Stopping WebTier…

WebTier Successfully Stopped

Stopping Oracle Management Server…

Oracle Management Server Successfully Stopped

AdminServer Successfully Stopped

Oracle Management Server is Down

$ ./emctl stop agent

Oracle Enterprise Manager Cloud Control 12c Release 3

Stopping agent ….. stopped.

Upgrade

Run the runInstaller executable from the folder where the software was extracted.

$ cd /stage

$ ./runInstaller

Type My Oracle Support account details.

Choose if you want to search for any software updates.

Review and fix any prerequisite failure.

Choose the upgrade method, in my case One system Upgrade then select the Oracle Home.

Type the “NEW” Middleware Home location.

Type the required Repository credentials.

Some prerequisites failed, click Yes to let the installer fix them automatically.

Review the list of plug-ins that are going to be automatically upgraded.

Select any additional plug-ins to be deployed after the upgrade.

Type the required WebLogic Domain credentials.

Review the all the information provided and click Install.

Review the progress.

Run the required allroot.sh script as root.

Review the final information about the upgrade process.

Login to Cloud Control and verify the version. Note that some targets are marked as down, we are going to fix them in the post upgrade tasks.

Upgrade Oracle Management Agents (at least OMS and Repository). Go to Setup -> Manage Cloud Control -> Upgrade Agents.

Type a Job Name (or use the default name) and click Add to select the agents you want to upgrade to 12.1.0.4.

Select the agents from the list and click OK.

Choose credentials and click Submit. There’s the option to schedule the upgrade for later under Additional Inputs.

You must run the root.sh script after the upgrade, click OK.

Review the agent upgrade process.

Agent upgrade was successful.

Post Upgrade

Deleting the Old OMS Home is an optional task, however I have decided to perform it in order to save space and have a clean Inventory.

Invoke the installer from the old OMS home

$ export ORACLE_HOME=/u01/app/oracle/oms12cr3/oms

$ /oui/bin/runInstaller -deinstall

Select only the OMS-related plug-ins homes (from the Old Home only!!) and click Remove.

Select the Java Development Kit JDK home and click Remove.

Select the Oracle WebTier home and click Remove. After finishing the deinstall click Close.

Finally select the OMS home & the Oracle Common directories and click Remove.

All targets marked up.

Thanks,

Alfredo

How to remove OEM agent in the release 3 of the Oracle Enterprise Manager.

OEM 12c R3 has a very useful Perl script which allows removing an installed OEM agent in the host. The process is quite straightforward just by invoking the AgentDeinstall.pl script located under:

$AGENT_HOME/sysman/install/AgentDeinstall.pl

[oracle@ol6-112 12.1.0.3.0]$ /u01/app/oracle/agent12cr3/core/12.1.0.3.0/perl/bin/perl /u01/app/oracle/agent12cr3/core/12.1.0.3.0/sysman/install/AgentDeinstall.pl -agentHome $AGENT_HOME

Agent Oracle Home: /u01/app/oracle/agent12cr3/core/12.1.0.3.0

NOTE: The agent base directory: /u01/app/oracle/agent12cr3 will be removed after successful deinstallation of agent home.

DetachHome Command executed:/u01/app/oracle/agent12cr3/core/12.1.0.3.0/oui/bin/runInstaller -detachHome -force -depHomesOnly -silent ORACLE_HOME=/u01/app/oracle/agent12cr3/core/12.1.0.3.0 -waitForCompletion -invPtrLoc /u01/app/oracle/agent12cr3/core/12.1.0.3.0/oraInst.loc
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 8015 MB    Passed
The inventory pointer is located at /u01/app/oracle/agent12cr3/core/12.1.0.3.0/oraInst.loc
'DetachHome' was successful.
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 8015 MB    Passed
The inventory pointer is located at /u01/app/oracle/agent12cr3/core/12.1.0.3.0/oraInst.loc
The Oracle home '/u01/app/oracle/agent12cr3/sbin' could not be updated as it does not exist.

Deinstall Command executed:/u01/app/oracle/agent12cr3/core/12.1.0.3.0/oui/bin/runInstaller -deinstall -silent "REMOVE_HOMES={/u01/app/oracle/agent12cr3/core/12.1.0.3.0}" -waitForCompletion -removeAllFiles -invPtrLoc /u01/app/oracle/agent12cr3/core/12.1.0.3.0/oraInst.loc
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 8015 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-11-11_02-22-46PM. Please wait ...Oracle Universal Installer, Version 11.1.0.11.0 Production
Copyright (C) 1999, 2013, Oracle. All rights reserved.

Starting deinstall

Deinstall in progress (Monday, November 11, 2013 2:23:00 PM CST)
Configuration assistant "Agent Deinstall Assistant" succeeded
............................................................... 100% Done.

Deinstall successful

End of install phases.(Monday, November 11, 2013 2:23:37 PM CST)
End of deinstallations
Please check '/u01/app/oraInventory/logs/silentInstall2013-11-11_02-22-46PM.log' for more details.

Do you want us to delete the old oracle home [yes/no] :y

Hope this help you when need to remove an OEM agent installation.

Thanks,
Alfredo

Oracle Enterprise Manager 12c Release 3 New Features

Today I will talk about some new features in the release 3 of the Oracle Enterprise Manager.

All Metrics Chart Enhancements

This new feature show more data in the metrics page, like minimum and maximum values, it will highlight periods when the agent was unreachable or target was down, it will also show the period when the metric was in warning or critical severity.

This really helps DBAs to better understand the behavior of the target including the metric data.

Metrics Schedule Enhancements

Metrics now support additional collection schedule like weekly on specified days of the week and monthly on specified days of the month.

Service Target Dashboard

Provides a quickly out-of-box overview of the health of services, this is really useful when looking for a quick view of what service is having problem in the environment.

Performance Diagnostics Enhancements

Even when this is not a new feature of the OEM 12c R3, ASH Analytics is really cool. It allows DBAs to diagnose performance exactly when DBA desire; it has customized selection of the desired point in time when issues appeared.

Well, these are some new and cool features of the Oracle Enterprise Manager 12c Release 3. Hope you can enjoy this features as much as me.

Thanks,

Alfredo

Oracle 12c ASM new features

Today I will talk about some new features for ASM on 12c version.

Oracle Flex ASM

Oracle Flex ASM is a set of new features that provide critical capabilities required for cloud computing. Oracle Flex ASM redefines the traditional ASM cluster architecture of having one ASM instance on every node in the cluster; this means you can have less ASM instances than nodes in your cluster. In this configuration the number of ASM instances running is called ASM cardinality, by default the ASM cardinality is 3 and can be changed with a Clusterware command.

What are the benefits?

On the traditional architecture when an ASM instance fails; all DB instances connected to that instance in the node will also fail. With Oracle Flex ASM if the ASM instance fail the Clusterware will relocate that ASM instance to a different node and the DB instances will remotely (through private network) connect to the relocated ASM instance, all without disruption to the DB client.

Dedicated ASM Network

This new 12c feature provides the option to dedicate a private network for ASM network traffic only. There’s also the option to use the Oracle Clusterware interconnect private network.

Remote Access

As per the new Oracle Flex ASM feature, the DB instance can remotely connect to ASM instance hence the need of a password file in order to authenticate remote DB instances to ASM. Oracle Flex ASM has the ability of storing password files in a Disk Group and is extended to DB clients; this is really useful to avoid synchronizing multiple password files within the cluster.

Oracle Flex ASM is one of the most important enhancements to ASM in 12c version, however in the below whitepaper describes the rest of new features present.

http://www.oracle.com/technetwork/products/cloud-storage/oracle-12c-asm-overview-1965430.pdf

Bertrand Drouvot has already tested this Oracle Flex ASM feature and uploaded the contents into his own blog (http://bdrouvot.wordpress.com/2013/06/29/build-your-own-flex-asm-12c-lab-using-virtual-box/).

Another interesting feature is the Extent Reading Selection Enhancement which evenly distributes the selection of which copy of a block is read in ASM Disk Groups with normal or high redundancy. This feature is enabled by default in 12c and states that users on I/O bound systems should notice a performance improvement while reading blocks from disks.

Thanks,

Alfredo