Monthly Archives: February 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

12c, EM

EM Express 12c

Leave a comment

Oracle EM Express is an administrative console for the Oracle 12c database and is normally installed and configured via the Database Configuration Assistant (DBCA).   It uses the internal structures of an Oracle database for monitoring and administration and SQL*Net for communication, which greatly reduces the load on database being administered.   EM Express runs against most web-browsers with Flash plug-in installed.  EM Express is not designed to administrate multiple oracle databases and natively only connects to a single oracle database.   It is part of the oracle enterprise license package, however functions like SQL tuning page will result in additional licensing charges.  EM Express is extremely useful in relieving the administrative burden of a single database installation.

11g, 12c, Administration, backup/recovery, RMAN

Oracle 12c – Recovery Manager (RMAN) Utility Commands

Leave a comment

RMAN is the preferred method used for backup and recovery of Oracle database.   This article gives a brief overview of commands used to Configure, Display Configurations, Reset to Default, and take simple backups.

 

 

 

  • The SHOW command gives current settings for RMAN utility to the associate target database.
  • The CLEAR command resets to RMAN defaults.
  • The following command allows for three backup copies of all data files and control files. the forth oldest will be considered obsolete and deleted. default is 1.
    • RMAN> configure retention policy to redundancy 3;
  • The following ensures that backup window of recovery is at least 7 days in the past, backups will be kept to allow for this regardless of redundancy policy.
    • RMAN> configure retention policy to recovery window of 7 days;
  • The following changes the default device type to tape, the default is disk.
    • RMAN> configure default device type to sbt;
  • Regardless of default device type, you can perform backup to specific backup with ‘device type’ clause.
    • RMAN> backup device type sbt database;
    • RMAN> backup device type disk database;
  • RMAN can backup as an image copy or backup set.
    • RMAN> configure device type disk backup type to backupset;
    • RMAN> configure device type disk backup type to copy;
  • TAPE backups can only be backup sets.
  • binary compression can be used with backupsets with COMPRESSED option.
    • RMAN> configure device type disk backup type to compressed backupset;
    • RMAN> configure device type sbt backup type to compressed backupset;
  • An RMAN channel is a connection to db server process, use the configure channel command to create and change channel settings.
    • RMAN> configure channel device type disk maxpiecesize 1g;
    • RMAN> configure channel device type disk format /tmp/%U;
  • By default RMAN allocates a single disk channel for all operations, you can specify a distinct file name for a channel, but RMAN does not create a backup in the fast recovery area.
    • RMAN> configure channel device type disk format ‘/u01/oradata/orcl_df%t_s%s_s%p’;
    • RMAN> configure channel device type disk format ‘+dgroup1’;
  • You can perform parallel backups by defining multiple channels for use.
    • RMAN> RUN
    • {
    • allocate channel u01 device type disk format ‘/u01/%u’;
    • allocate channel u02 device type disk format ‘/u02/%u’;
    • backup database plus archivelog;
    • }
  • RMAN can be configured to autobackup control files and if in archivelog mode and a change occurs to controlfiles, they will be automatically backed up.
    • RMAN> configure controlfile autobackup on;
    • RMAN> configure controlfile autobackup off;
  • RMAN configuration can be reset to default values by using the CLEAR option.
    • RMAN> configure default device type clear;
    • RMAN> configure retention policy clear;
    • RMAN> configure controlfile autobackup clear;
  • When RMAN optimization is enabled, it will skip files that have not changed since the last backup set. RMAN determines this by:
    • Datafile must have same DBID, checkpoint SCN, creation SCN, resetlogs scn and time as one already in backup set.
    • Datafile must be offline, read-only or closed.
    • Archived log – must have same DBID, thread, sequence number, resetlogs scn and time.
    • Backup Set – must have the same DBID, backup set record id, and stamp.
  • RMAN will still take a backup of retention policy requires and if TO DESTINATION is used with BACKUP RECOVERY AREA or BACKUP RECOVERY FILES.
  • Turn RMAN optimization on and off with:
    • RMAN> configure backup optimization on;
  • Once optimization is enabled, backup will not be performed, if nothing has changed since the last valid backup.
  • Backup optimization can be overridden by the FORCE option.
    • RMAN> backup database force;
    • RMAN> backup archivelog all force;

 

 

 

Larry Catt

OCP

12c

Oracle 12c – External table enhancements

Leave a comment

External table option uses directory objects to indicate where data files and output files are located.  User must have read on input files and write on output files at OS level.  If directory location does not exist, SQL*Loader will create it but user must have CREATE ANY DIRECTORY privilege and DROP ANY DIRECTORY privileges.

 

Direct NFS – Oracle Direct NFS (dNFS) is an internal I/O layer to provide faster access of NFS client files.  You use the DNFS_ENABLE=TRUE command parameter to user with SQL*Loader.  dNFS is used by default on files larger than 1GB.

 

Larry Catt

OCP

12c, security, Users

Oracle 12c – Create the password file

Leave a comment

Oracle uses a password file to restrict access to database for users with elevated permissions.   Password file is required for users with the following privileges: SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM.   If the password file is lost or corrupted, it must be recreated with the utility ORAPWD.    Syntax of ORAPWD is:

ORAPWD File=<filename> [ENTRIES=number_users] [FORCE={Y/N}] [IGNORECASE={Y/N}]

Command options for ORAPWD are:

  • FILE – Name of the file, can include complete directory or file is written in current directory.
  • ENTRIES – Max number of entries (user accounts) to add. This is users with any of the privileges of SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM
  • FORCE – if Y command will overwrite existing password file.
  • IGNORECASE – If Y password will not be case sensitive.
  • FORMAT – When set to 12 which is the default, will create file in 12c format. 12C format is required for SYSBACKUP, SYSDG, SYSKM admin privilege support.  IF set to less, then file will only support for SYSDBA and SYSOPER privileges.
  • SYSBACKUP – If Y creates a SYSBACKUP entry in password file.
  • SYSDG – If Y creates a SYSDG entry in password file.
  • SYSDM – If Y creates a SYSKM entry in password file.

 

example of use of password file creation for a database with ORACLE_SID of db1:

orapwd File=orapwdb1 Entries=10

The initialization parameter REMOTE_LOGIN_PASSWORDFILE controls the connection to the password file with the following options:

  • NONE – Database will not recognize any password file.
  • EXCLUSIVE – Password file can be used with only one database. It allows you to add multiple users to the database of which any can have one of the privileges: SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM.   This is the default.
  • SHARED – Password file can be used by multiple databases running on the same server or multiple instances in a RAC database. Shared password files are read-only and cannot be modified.  All users needing access must be must be added while the REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE, then change the init parameter back to SHARED.

 

Larry Catt

OCP