Category Archives: 12c

12c, tablespaces

Oracle 12c – Relocate SYSAUX occupants

Leave a comment

In Oracle the SYSAUX tablespace is an auxiliary storage location for the SYSTEM tablespace.  Many of the Oracle database components which are not critical to the core functionality reside in the SYSAUX tablespace.  If the SYSAUX tablespace becomes unavailable, core database will still function, though pieces of the database which rely on SYSAUX components will fail or have limited capabilities.  You can view the components of the SYSAUX tablespace through the view V$SYSAUX_OCCUPANTS  as described below:

 

SQL> desc v$SYSAUX_OCCUPANTS

 Name                                      Null?    Type

 —————————————– ——– —————————-

 OCCUPANT_NAME                                      VARCHAR2(64)

 OCCUPANT_DESC                                      VARCHAR2(64)

 SCHEMA_NAME                                        VARCHAR2(64)

 MOVE_PROCEDURE                                     VARCHAR2(64)

 MOVE_PROCEDURE_DESC                                VARCHAR2(64)

 SPACE_USAGE_KBYTES                                 NUMBER

 CON_ID                                             NUMBER

SQL>

 

Procedure shown in the MOVE_PROCEDURE column of V$SYSAUX_OCCUPANTS details procedure name to move the product. This article will detail the steps to move the LGNMR component from SYSAUX tablespaces to its own tablespace storage.

 

  1. Logon to your database server as the oracle software owner.

 

[larry@linux2 ~]$ su – oracle

Password:

Last login: Wed Jan 11 15:04:41 EST 2017 on pts/2

Enter database to use:

1 – ORCL

2 – CDB1

Option >

1

[oracle@linux2 ~]$

 

  1. Connect to SQLPLUS as sysdba.

 

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 12 09:03:45 2017

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

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

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

and Unified Auditing options

 

SQL>

 

  1. Create a new tablespace called LOGMINER.

 

SQL> create tablespace LOGMINER datafile ‘/u01/oradata/orcl/orcl/LOGMNR_data01.dbf’ size 1g;

Tablespace created.

SQL>

 

  1. Extract the move_procedure from V$SYSAUX_OCCUPANTS view for LOGMNR.

 

SQL> select occupant_name, schema_name, move_procedure from v$sysaux_occupants where occupant_name=’LOGMNR’;

OCCUPANT_NAME

—————————————————————-

SCHEMA_NAME

—————————————————————-

MOVE_PROCEDURE

—————————————————————-

LOGMNR

SYSTEM

SYS.DBMS_LOGMNR_D.SET_TABLESPACE

SQL>

 

  1. Execute procedure passing the new tablespace name.

 

execute SYS.DBMS_LOGMNR_D.SET_TABLESPACE(‘LOGMINER’);

 

SQL> execute SYS.DBMS_LOGMNR_D.SET_TABLESPACE(‘LOGMINER’);

PL/SQL procedure successfully completed.

SQL>

 

  1. This completes movement of LOGMNR from sysaux tablespace.

 

 

 

Larry Catt

OCP

12c, Administration, privileges, Users

Oracle 12c – User privilege analysis overview

Leave a comment

Oracle 12c contains the feature to analyze the privileges actually used by an individual user account in order to implement least privilege policies within the RDBMS.  This article gives an overview of User privilege analysis in Oracle 12c.

 

 

  1. Privilege analysis is provided by Oracle Data Vault and it allows for creation of profile which captures system and object privileges used by a user.
  2. This profile can be used to bounce used privileges against granted privileges.
  3. You can reduce privileges that are not in use through this analysis.
  4. It is possible to perform privilege analysis with or without have Database Vault configured and enabled.
  5. It is possible to administer privilege analysis by EM Cloud Control or the package DBMS_PRIVILEGE_CAPTURE
  6. The role CAPTURE_ADMIN grants execute privilege on DBMS_PRIVILEGE_CAPTURE package and select on views to results.
  7. The DBMS_PRIVILEGE_CAPTURE package allows you to create/enable/disable/drop privilege analysis policies.
  8. Only one privilege analysis policy can be enabled in the database at a time, but the DBMS_PRIVILEGE_CPATURE.G_DATABASE privilege analysis can be done with one other user defined policy at the same time.
  9. Analysis policies running will still be running after DB restart.
  10. Privilege analysis policies must be disabled, before reports can be generated.
  11. Policies must be disabled before they are dropped.
  12. Dropping a privilege policy, also drops all collected data by that policy.
  13. List of some view available with Privilege Analysis:
    1. DBA_PRIV_CAPTURES — Lists information about existing privilege analysis policies
    2. DBA_USED_PRIVS — Lists the privileges that have been used for reported privilege analysis policies
    3. DBA_UNUSED_PRIVS — Lists the privileges that have not been used for reported privilege analysis policies
    4. DBA_USED_OBJPRIVS — Lists the object privileges that have been used for reported privilege analysis policies. It does not include the object grant paths.
    5. DBA_UNUSED_OBJPRIVS — Lists the object privileges that have not been used for reported privilege analysis policies. It does not include the object privilege grant paths.
    6. DBA_USED_SYSPRIVS — Lists the system privileges that have been used for reported privilege analysis policies. It does not include the system privilege grant paths.
    7. DBA_UNUSED_SYSPRIVS — Lists the system privileges that have not been used for reported privilege analysis policies. It does

Larry Catt

OCP

 

12c, EM, Oracle

EM Express 12c Configuration Page

Leave a comment

Oracle EM Express Configuration Page provides the ability to administrate the initialization parameters of an instance or multiple instances in a RAC configuration; view the memory configuration and usage; usage of various features; and database properties.  This article covers the general layout of EM Express Configuration Page and its functional usage.

 

  1. Logon the EM Express as an administrative user.   In this case we will use the URL:  https://10.30.15.63:5500/em.   This indicates the EM Express is installed on server 10.30.15.63, 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.
  2. Configuration drop down give the four options: Initialization Parameters, Memory, Database Feature Usage, and Current Database Properties.                                                                                                                                                             
  3. Initialization Parameters lists all current instance parameters and SPFILE parameters. From this page you can adjust most parameters.                                                                
  4. Memory option gives more details of memory usage that currently exists and allows the administrator a visual prospective of current environment.                         
  5. Database Feature Usage lists the features of database used by a user or application. This allows the administrator the ability to focus on features which may present a performance problem.         
  6. Current Database Properties lists the value of all database property variables. However this view does not give the ability to update this values.                                                               

 

 

 

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

 

Larry Catt

OCP

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

12c, audit

Oracle 12c – Create and enable audit policies

Leave a comment

Oracle 12c has implemented the unified auditing which is based off of system and user level policies.   This article describes the creation and enabling audit policies.

 

  1. Logon to oracle server and logon to sqlplus as sysdba.

Verify that unified auditing is enabled with command: select value from v$option where parameter=’Unified Auditing’;

 

SQL> select value from v$option where parameter=’Unified Auditing’;

 

VALUE

—————————————————————-

TRUE

 

SQL>

 

  1. Create audit policy test_user, which will audit session creation and all RDBMS actions.

 

create audit policy test_user privileges create session

ACTIONS all;

 

  1. Enable the audit policy for the user test with the command: This enabling will only be for actions performed by user test.

 

audit policy test_user by test;

 

SQL> audit policy test_user by test;

 

Audit succeeded.

 

SQL>

 

  1. View the active policies.

 

SQL> select policy_name from audit_unified_enabled_policies;

 

POLICY_NAME

——————————————————————————–

TEST_USER

 

SQL>

 

  1. This completes the creation and enablement of Unified Audit policies.

 

Larry Catt

OCP