Daily Archives: February 17, 2017

12c, Administration

Oracle 12c – User administrative privileges Changes

Leave a comment

Oracle 12c has increased the security of the RDBMS by increasing the number of user accounts for administrative tasks and reducing the overall permissions these users have in the RDBMS.   This increases security by limiting the amount of control a single user account has over your data.   This article outlines the general changes to administrative account privileges in Oracle 12c.

 

  1. New roles were created to increase security through separation of duties and the principle of least privilege. SYSDACKUP, SYSDG, SYSKM.   This new roles are used to eliminate the need to grant SYSDBA to some users.
    1. SYSBACKUP – Allows for connection to DB through RMAN for all backup and recovery operation.
    2. SYSDG – Allows for Data Guard operations and can be used either through Data Guard Broker or the DGMGRL command line. To connect with a password, you must create a password file for this user.
    3. SYSKM – Allows for management of Transparent Data Encryption wallet operations. To connect with a password, you must create a password file for this user.
  2. New privilege PURGE DBA_RECYCLEBIN has been created to execute PURGE DBA_RECYCLEBIN command without requiring the SYSDBA privilege.
  3. SELCT ANY DICTIONARY privilege no longer permits access to tables:
    1. DEFAULT_PWD$
    2. ENC$
    3. LINK$
    4. USER$
    5. USER_HISTORY$
    6. XS$VERIFIERS
  4. UNLIMITED TABLESPACE privilege no longer included in RESOURCE role.

 

Larry Catt

OCP

12c, testing

Oracle 12c – Perform real application testing

Leave a comment

In Oracle 12c, Real application testing uses to Oracle utilities Database Replay and SQL Performance Analyzer (SPA).   These two utilities allow an administrator to capture application traffic into a database then replay the activities while taking analysis of the SQL acting upon the incoming traffic.    Though an iterative processes of SQL analysis we are able to optimize both the PL/SQL and other database structures for optimal performance.  This article gives a brief overview of these utilities.

 

 

Database replay captures all external database calls made to system during capture period. This capture can be replayed against test system for the purpose of testing new software, evaluating bug fixes, or optimization of current code.   The capture includes all relevant information about the client request, such as SQL text bind values, transaction information.  Background activities and scheduler jobs are not captured.   Additional client requests that are not captured are:

 

  1. Direct path load SQL*Loader.
  2. Shared Server requests (MTS)
  3. Oracle Streams.
  4. Advance Replication streams.
  5. NON-PL/SQL based advance queuing(AQ)
  6. Oracle Call Interface (OCI) based object navigation.
  7. NON SQL-based object access.
  8. Distributed transactions.

 

 

 

 

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 following Views relate to SPA.

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.

 

 

 

Larry Catt

OCP