Oracle 12c – Design the database layout for optimal performance

Optimization for performance is normally focused on disk usage because this is by far the slowest component of any RDBMS.    Increasing the rate of reads and writes form disk dramatically increases the performance of most all database suites.  The standard answer to optimize performance in an Oracle 12c build is to use ASM (Automatic Storage Management), because oracle ASM automatically stripes data across all available disk groups.   This elevates the majority of problems with disk performance.   ASM automatically provides the following benefits over traditional storage configuration:


  1. Striping
  2. Mirroring
  3. Online storage reconfiguration and dynamic rebalancing.
  4. Managed file creation and deletion


If your organization is still use traditional storage options we should consider implement the following to optimize disk performance:


  1. Stipe everything across every disk with RAID 1 mirror.
  2. Move Archive Logs to Different Disks – Avoid DW and ARC process disk contention to improve performance.
  3. Move Redo Logs to Separate Disk – High level of updates in most OLTP systems cause a lot of redo log writes. Thus avoid disk contention by moving to separate disks.
  4. Continually monitor database performance to determine disk contention on various database functionality and redirect storage to avoid contention.



Larry Catt




Oracle 12c -RMAN Backup Overview

Oracle provides the Recovery Manager utility (RMAN) to perform two types of database backups: Image Backup and Backup Set.    RMAN command BACKUP AS COPY command creates an image copy or bit for bit copy of the data files, archived redo log and controlfiles.




You can set the default backup to image copy with CONFIGURE DEVICE TYPE command




Backup sets are the other type of RMAN backups, each backup set has one or more binary files called backup pieces, in proprietary format that can be restored by RMAN.  You can limit the size of size of backup pieces by the MAXPIECESIZE option.   Use the RMAN command BACKUP AS BACKUPSET to create backup copy of database.




You can set the default backup to backup set with the CONIFGURE DEVICE TYPE command.




You can backup the entire database with the command:




Add PLUS ARCHIVELOGS clause will cause a log switch that allows for full media recovery to the point of starting the backup.




Incremental Backups are part of a backup set that only contains changes to the database since the last full backup.   The benefit of an incremental backup is size of the backup and time required to tack the backup.   Incremental Backups are only good if combined with the last full backup and any incremental backup between the last full backup.


You can create an incremental backup with the RMAN command BACKUP INCREMENTAL in three ways


Level 0 – Identical to full back

Level 1 Differential – backups all changed blocks since most recent incremental backup.

Level 1 Cumulative – backups all changed block since most recent Level 0 incremental backup


Restoring incremental backup use level 0 at start and apply level 1 cumulative or all differential.


Example of incremental Level 1:


RMAN> backup incremental level 1 cumulative database;


RMAN> backup incremental level 1 database;



Larry Catt


EM Express 12c Security Page

Oracle EM Express Security Page provides the ability to administrate the Users, Roles, and Profile requirements of an Oracle database.  This article covers the general layout of EM Express Security Page and its functional usage.


  1. Logon the EM Express as an administrative user.   In this case we will use the URL:   This indicates the EM Express is installed on server, listening on port 5500 (which is the default).  NOTE:  You may receive a certificate warning but select continue to site option.   Enter your sysdba credentials at the sign in page and mark the check box as sysdba, normally this is the user name and password you used to create the database.

  1. From the top level drop down memory you have four options: Configuration, Storage, Security and Performance.

  1. The Security drop down you have the options: Users, Roles, and Profiles.

  1. The Users option allows you to view all users of the database, add/drop users, modify the permissions of a user and perform any maintenance tasks to a user account.

  1. The Roles option allows you to view all defined database roles, add/drop roles, add permissions to roles, and perform any maintenance tasks to existing roles.

  1. The Profiles option allows you to view all defined profiles of the database, add/drop profiles, modify profile parameters and perform any additional profile administration required.


This article summarized the feature and use of the Security Page of Oracle 12c EM Express.


Larry Catt


Oracle 12c – Steps in SPA analyzing performance change

SQL Performance Analyzer (SPA) allows you to evaluate a set of SQL statements against known workloads.  This set of SQL statements with execution statistics and execution context is known as an SQL Tuning set.    A single SQL Tuning set can be adjusted with updated SQL statements to test various evolutions of the same process.   Additionally, you can use cursor cache and AWR snapshots to track performance.  Using SQL TUNING SET enables you to:


  1. Store SQL text and auxiliary info in single persistent database objects.
  2. Capture Insert, update, delete, and select SQL statements in the SQL TUNING SET.
  3. Load and merge content from different data sources like AWR and cursor cache.
  4. Export SQL TUNING SET where SQL workload is captured and import into other systems.
  5. Reuse SQL workload as input source for other advisors like SQL Tuning Advisor and SQL Access Advisor.


The Views and  package that relate to SPA are:

DBA_ADVISOR_TASKS – Info about SPA tasks created.

DBA_ADVISOR_EXECUTIONS – info about SPA tasks executed.

DBA_ADVISOR_FINDINGS – info about SPA finding, generate the following reports:

  • Performance regressions problems.
  • Changing structure of execution plan.
  • Errors like non-existence of objects.
  • Info messages.

DBA_ADVISOR_SQLPLANS – list of execution plans.

DBA_ADVISOR_SQLSTATS – list of SQWL compilations and execution statistics.

V$ADVISOR_PROGRESS – displays operation progress of SPA.


DBMS_SQLPA .CREATE_ANALYSIS_TASK – This function create analysis tasks for single SQL statements from SQL text, AWR, or cursor cache.  Also create analysis task from SQL Tuning set.

DBMS_SQLPA .EXECUTE_ANALYSIS_TASK – This function executes defined analysis tasks.

DBMS_SQLPA .REPORT_ANALYSIS_TASK – This function displays results of executed tasks.



Steps in Performing Performance Analysis:


  1. Capture SQL workload to analyze and store it in a SQL tuning set.
  2. If using test system it should be as close to production as possible and transfer SQL Tuning Set.
  3. On test system create SPA task
  4. Build pre-change SQL trails by executing the SQL statements store in the SQL tuning set.
  5. Perform the system change.
  6. Build post-change SQL trial by re-executing the SQL statements in SQL tuning set on the post-change test system.
  7. Compare and analyze the pre-change and post-change versions of performance data and generate a report to identify the SLQ statements that have improved, remained unchanged, or regressed after system change.
  8. Tune any regressed SQL statements that are identified.


Larry Catt


Perform Flashback for a CDB in Oracle 12c

Oracle 12c provides the ability to flashback the database to a previous state.  This procedures demonstrates the use of this process to flashback the database 15 minutes.   NOTE:  Four states must exist for flashback to work:

  1. Must be in archivelog mode.
  2. Database must have flashback on.
  3. Control files must contain flashback logs, thus not recreated.
  4. No tablespaces exist with flashback off set.



  1. Logon to your Oracle database server as the Oracle software owner and logon to SQLPLUS.


[root@linux2 etc]# su – oracle

Last login: Thu Jul  1 09:25:23 EST 2015 on pts/1

[oracle@linux2 ~]$

[oracle@linux2 ~]$ sqlplus / as sysdba


SQL*Plus: Release Production on Thu Jul 1 13:47:01 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options



  1. Determine SCN of database 15 min ago by using the timestamp_to_scn().


SQL> select timestamp_to_scn(sysdate-1/96) from dual;






  1. Now using SCN extracted by the previous step, shutdown the database, startup in mount mode and execute the flashback command.


SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.


Total System Global Area  734003200 bytes

Fixed Size                  2928728 bytes

Variable Size             524292008 bytes

Database Buffers          201326592 bytes

Redo Buffers                5455872 bytes

Database mounted.



Flashback complete.




  1. Startup the database for normal use with resetlogs option and see that all transactions of the last 15 minutes are gone.


SQL> alter database open resetlogs;

Database altered.




  1. NOTE: You should take a full backup of the database as soon after flashback as possible to ensure future recovery.


  1. This completes the use of Flashback in Oracle 12c.



Larry Catt


Oracle 12c – Implement Application Tuning

Tuning an application to run efficiently against any version of the Oracle RDBMS is a recursive process which requires continuous monitoring and adjustments.    There are multiple Oracle tools and process to aid in both the monitoring and adjustment process.    This article covers the basic process of Application Tuning.


  1. Tune to a problem or performance level – Due not go about changing settings just to see what happens. Rather try to find a problem or bottle-neck in the database and tune to reduce the problem or performance slow down.
  2. Gather Data/statistics – Use diagnostics tools to gather info about problem/slow down. Normally this tools will be related to gathering statistics based against a known baseline.
  3. Make Discrete Changes – Make small exact changes and observe response. Try to make one change at a time, thus you will be able to determine what change helped solve the issue.
  4. Stop – Stop making changes once the problem or bottle-neck has been addressed.
  5. Monitor – Monitor the database and observe things that can be associated with performance problems:
    1. Long waits.
    2. Long held latches and locks.
    3. Long running processes.
    4. High CPU usage.
    5. High I/O
  6. Create new baselines for performance with known workloads. These baselines can be used to truly determine that a perceived database issue is truly abnormal and needs to be addressed.


Larry Catt


Oracle 12c – Apply and review patches

In Oracle 12c, Patching is performed to correct discovered bugs and close security vulnerabilities.   Oracle provides four types of Patches:

Interim Patches – single/collection of bug fixes or one-offs.

Diagnostic Patches – Diagnose or verify a fix.

Patch Set Updates (PSU) – Collection of high impact, low risk, proven fixes for products and components

Critical Patch Updates (CPU) – Collection of patches for security vulnerabilities.

Patching of the RDBMS can be done with two methods: Manually or EM Cloud Control Patch Wizard.   Additionally, the patching processes have added:  Online Patching and Queryable Inventories.   This article will give a brief overview of these Features.

Manual Patching

Step in manually patching are:

  1. Logon to oracle support
  2. Click Patches & Updates tab.
  3. Enter the patch number you are looking for on Patches % Updates page.
  4. Click download on patch for your OS
  5. Click download Patch Metadata.
  6. Review the documentation for specific installation instructions.
  7. Use the OPatch utility to install all patches.

EM Cloud Control Patch Wizard

The EM Cloud Control Patch Wizard has the following benefits over Manual Patching:

  1. Integrate Patch work with Oracle support and provides single view to determine patch needs.
  2. End to End workflow of patching, resolving patch conflicts.
  3. Easy determination of Patch needs for single system.
  4. Patch plans can be saved as templates.
  5. Out of place patching is possible for support of RAC and GRID infrastructure.
  6. EM Cloud allows you create a patch plan to apply patches to one or more targets. A patch can be added to plan only if it’s for same release and OS.

EM Cloud can apply patches with two methods:

  1. Online Mode – EM connects to Oracle Support, searches for necessary patches, resolves any conflicts and adds them to patch plan.
  2. Offline Mode – Used when EM cannot connect to Oracle Support. EM searches for patches manually uploaded to software library and adds them to patch plan.

Online Patches

Before 11g all patches require relink of RDBMS libraries and a shutdown of system.  Now a large number of Patches can be done online, they contain .so files and do not require relink of RDBMS.

Benefit of Online Patches:

  1. No downtime.
  2. persistent across shutdown
  3. Allow rolling patches in RAC
  4. Fast install.

Downside of Online Patches:

  1. Require more memory
  2. Online patches not available for all OS
  3. Not all patches are hot patches.

Queryable Patch Inventory

Queryable Patch inventory is new 12c, implemented with DBMS_QOPATCH package and provides view of patches installed just as OPATCH lsinventory command.    DBMS_QOPATCH queries the OUI Oracle Universal Installer inventory to gather this metadata.

It provides the following:

  1. Query patches installed from SQL prompt.
  2. Creates reports and provides validation across many installs.
  3. Checks patches on RAC from single node.


DBMS_QOPATCH components:

GET_OPTACH_BUGS – Provides list of bug fixes per patch number.

GET_OPATCH_COUNT – Provides total number of patches installed.

GET_OPATCH_DATA – Provides top level patch info – number, creation date.

GET_OPATCH_FILES – Provides list of files updated by patch number.

GET_OPATCH_INSTALL_INFO – Provides info such as ORACLE_HOME, patch, inventory_location

GET_OPATCH_LIST – List of Patches installed.

GET_OPATCH_LSINVENTORY – Whole patch inventory

GET_OPATCH_OLAYS –Overlay for specific patch

GET_OPATCH_PREQS – Provides prerequisites for a patch

GET_OPATCH_XSLT – Provides style sheet for opatch XML inventory

GET_PENDING_ACTIVITY – Provides SQL patches applied to single inventory from binaries.

GET_SQLPATCH_STATUS – Patch status from SQL patch registry.

IS_PATCH_INSTALLED – Patches applied info from XML inventory

PATCH_CONFLICT_DETETION – Patch in conflict for give file.

SET_CURRENT_OPINST – inventory information for specific node in RAC.


Larry Catt