Showing posts with label 12c New Features. Show all posts
Showing posts with label 12c New Features. Show all posts

Sunday, March 24, 2019

How to mitigate the performance/protection trade-off in Oracle Data Guard

Introduction
The most know trade-off in Oracle Data Guard is the performance vs. protection.
Oracle Data Guard brings several protection modes, which brings companies the flexibility in choosing the right configuration in order to meet the application SLA policies.

Data Protection Modes
There are 3 protection modes in Oracle Data Guard, which can be summarized in the following table:
















Data Protection Modes - Key Considerations

  •  Maximum Performance = compromise on protection 
  •  Maximum Protection = compromise on performance (usually)
  •  Better to use at least 2 standby databases when running maximum protection
  •  NET_TIMEOUT parameter of LOG_ARCHIVE_DEST_n=30 seconds (default)

Mitigating the performance/protection trade-off in Oracle Data Guard
Oracle 12cR1 introduced a feature which can enable both performance and protection for far standby environments. For example, when the standby database is very far from the primary database, setting a "maximum protection" configuration will likely affect the performance due to the network lag and it may take time to acknowledge commit back to the primary database.
In order to address this challenge, Oracle introduced a feature  called "Far Sync". Far Sync enables having a lightweight instance which resides close to the primary database and is synchronized with the primary database. This light-wight instance has only control files, standby redo logs and archived redo logs. It does not have data files and therefore it cannot be opened for access. The redo entries will be transported from the near "far sync" instance to the "regular" standby database asynchronously.
This feature enables achieving both performance (due to the smaller network latency between primary and far sync instance) while allowing for higher data protection as the far sync instance is always synced with the primary database.

Here is an illustration of this new feature:








Licensing
This feature requires having the Active Data Guard license which is an extra option in Oracle Enterprise Edition. For more information about licensing: https://docs.oracle.com/en/database/oracle/oracle-database/18/dblic/Licensing-Information.html#GUID-B6113390-9586-46D7-9008-DCC9EDA45AB4

Useful Links

    Tuesday, October 10, 2017

    ODC Appreciation Day : Online Move Data File Operation

    I've decided to dedicate my ODC Appreciation Day blog post to "Online Move Data File Operation" - a feature which has been introduced in Oracle 12c.
    As a quick background to this blog post, ODC (Oracle Developer Community) appreciation day is Tim Hall's (Oracle-Base) initiative which started last year (when it was OTN Appreciation Day).

    So what is online move data file operation?

    Moving data files is a common-use case, for example, when the DBA would like to move data files to a faster/larger disk. Another common use case is when DBA would like to migrate the files from traditional file systems to Oracle's Automatic Storage Management (more information about Oracle ASM is available in my ODC published article : https://community.oracle.com/docs/DOC-995178)

    Prior to Oracle 12c, there was no way to accomplish this task with zero downtime. Common solution was to take the entire tablespace or data file offline, move it to the new location, change the location using the "ALTER DATABASE" command (which  essentially changes the location in the Oracle's control file) and then bring the tablespace/file online again; however, during this operation the entire tablespace or datafile won't be accessible. Better approach to mitigate the downtime was to change the tablespace or data file to be in a read-only mode which will at least allow queries to be executed against the data which reside on that tablespace/file - but it's still not favorable because DMLs/DDLs cannot be executed during that operation.

    Oracle 12c allows us to do this completely online with zero downtime - while the database is open and users are accessing the data file. I've presented this feature with several syntax examples as part of my IOUG Collaborate 16 presentation - "Best New Features of  Oracle Database 12c" (slide deck is available here: https://www.slideshare.net/PiniDibask/best-new-features-of-oracle-database-12c).

    You could also find several examples and even a short video which demonstrates this cool feature in the Oracle documentation: https://docs.oracle.com/database/121/ADMIN/dfiles.htm#ADMIN13837

    Summary

    Whether running Oracle databases on premise or in the cloud, this feature is very useful. When Larry Ellison spoke at Oracle OpenWorld 2017 keynotes about Oracle 18c and Oracle's option of to ensure having up to 30 minutes down-time a year with their cloud offering, this feature is another useful way  that can help when it comes to ensuring zero impact on the customer's applications (among the many other Oracle's HA & DR features - such as ASM, RAC Rolling Upgrades, Active Data Guard, Flashback, etc.)

    Monday, May 29, 2017

    SYSBACKUP and SYSDG permissions in Oracle 12c

    Introduction

    In many companies there is a clear separation of duties for various Oracle Database related tasks such as administering ASM and backing up/restoring Oracle databases.
    In the past, DBAs used SYSDBA permission for administering ASM and RMAN. As you probably know, SYSDBA is the most powerful permission in Oracle Database which even allows viewing all the application data.

    Oracle realized that they need to address the separation of duties requirement of many customers and therefore they have provided in Oracle 11g a dedicated permission for administering ASM - I've written a dedicated blog post in the past for this matter. The SYSASM permission cannot access application data, but it can perform various ASM related management tasks (such as altering diskgroup, adding disks, etc.)

    What about RMAN?

    Until Oracle Database version 12cR1, there wasn't a good solution from a separation of duties when it comes to RMAN backups as users had to use SYSDBA which also allows them to access any application data (as well as other strong permissions).
    In Oracle 12cR1, Oracle introduced the SYSBACKUP permission which allows a user to perform backup and recovery operations either from Oracle Recovery Manager (RMAN) or SQL*Plus.
    You can view here the full list of operations allowed by this administrative privilege

    And what about Data Guard?

    Very similar to RMAN, Oracle also introduced in version 12cR1 a dedicated privilege named SYSDG which can be used with the Data Guard Broker and the DGMGRL command-line interface. 


    Demo

    First, we can connect to a 12c instance and look for those accounts. Next step would be to connect / AS SYSBACKUP since I'm logged with a user that has OS permissions to connect without any username and password

    SQL> SELECT username, account_status
      FROM dba_users
     WHERE username LIKE '%SYS%';
    
    USERNAME   ACCOUNT_STATUS
    ---------- --------------------------------
    SYS        OPEN
    SYSTEM     OPEN
    SYS$UMF    EXPIRED & LOCKED
    APPQOSSYS  EXPIRED & LOCKED
    GGSYS      EXPIRED & LOCKED
    WMSYS      EXPIRED & LOCKED
    SYSBACKUP  EXPIRED & LOCKED
    SYSRAC     EXPIRED & LOCKED
    AUDSYS     EXPIRED & LOCKED
    SYSKM      EXPIRED & LOCKED
    SYSDG      EXPIRED & LOCKED
    
    SQL> connect / as sysbackup
    Connected.
    SQL> show user
    USER is "SYSBACKUP"
    
    
    I can also create a new user and grant him the SYSBACKUP or SYSDG permissions
    SQL> connect / as sysdba
    Connected.
    
    SQL> create user C##PINI identified by PINI;
    User created.
    
    SQL> grant SYSBACKUP to C##PINI;
    Grant succeeded.
    
    SQL> select username,SYSBACKUP, SYSDG from V$PWFILE_USERS;
    
    USERNAME   SYSBA SYSDG
    ---------- ----- -----
    SYS        FALSE FALSE
    SYSDG      FALSE TRUE
    SYSBACKUP  TRUE  FALSE
    SYSKM      FALSE FALSE
    C##PINI    TRUE  FALSE
    
    Note that in order to connect to the database as either SYSDG or SYSBACKUP using a password, there must be a password file for it because it is possible to connect even when the database is not up and running, as follows
    SQL> connect / as sysdba
    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount
    ORACLE instance started.
    
    Total System Global Area 1644167168 bytes
    Fixed Size                  8793400 bytes
    Variable Size             989856456 bytes
    Database Buffers          637534208 bytes
    Redo Buffers                7983104 bytes
    Database mounted.
    SQL> connect c##pini/pini
    ERROR:
    ORA-01033: ORACLE initialization or shutdown in progress
    Process ID: 0
    Session ID: 0 Serial number: 0
    Warning: You are no longer connected to ORACLE.
    
    SQL> connect c##pini/pini as SYSBACKUP;
    Connected.
    

    Summary

    In this post we've reviewed the SYSDG and SYSBACKUP users and permissions in Oracle 12c which could be useful in case that in your company there is a requirement to have a separation of duties for backup/recovery as well as for Data Guard related administration tasks. I hope you find it useful for you.

    Sunday, December 18, 2016

    My favorite Oracle 12cR2 Multitenant Enhancements

    Introduction

    Oracle 12c Release 1 (12.1) introduced the new Multitenant architecture which is the highlight feature of Oracle 12c. The main purpose of Multitenant architecture is to simplify Database Consolidation by allowing to have multiple Pluggable Databases which are associated to the same instance or instances (in case of RAC). Each pluggable database is a self-contained, independent Database with its own schemas, data, etc. Pluggable Database is a “regular” Database from the application standpoint. This is a new paradigm compared to previous releases (i.e. pre 12c) which allowed to have only one single database associate to an Oracle instance. You can read more about Multitenant in my blog post here.

    Oracle Multitenant 12c Release 1 - The Main Challenges

    Even though Oracle 12c Multitenant announcement was very exciting, it still had several areas in the first release of Oracle 12c (i.e. 12cR1) which were problematic for DBAs - I've wrote a blog post this named "Where Oracle 12c Multitenant can (and should) be improved". The main items are:
    • Flashback Database - In Oracle 12cR1 there is no PDB-level flashback database. It is only possible to flash back the entire Container Database (CDB) with all its associated PDBs. This effectively means that all the PDBs lose data during a Flashback Database operation.
    • Memory Resource Management - Oracle 12c allows to ensure quality of service by defining resource plan via Oracle DBRM feature (Database Resource Manager) in order to prioritize CPU resources to pluggable databases across the container; however, in Oracle 12cR1 there is no way to limit or prioritize the memory usage by competing pluggable databases within the same CDB.
    • PDB Cloning - Oracle 12c allows fast provisioning by cloning a PDB from another PDB within the same CDB or by cloning a PDB from another PDB in a remote CDB. The only problem is that Oracle 12cR1 support "cold cloning", i.e. the source PDB must be in a READ ONLY mode which essentially means that a down time is required during the clone operation. 

    My Favorite Oracle Multitenant 12c Release 2 Enhancements

    Based on Oracle's "Oracle Multitenant 12cR2 New Features" white paper, all of the above challenges were addressed in the latest 12cR2:
    • Flashback Database - Flashback PDB is now fully supported with Oracle 12R2. In order to enable this feature, Oracle introduced the concept of local undo in 12cR2 which allows a PDB to have its own undo (In Oracle 12cR1 the undo was shared for the entire CDB). Please note that the shared undo mode is still supported in 12cR2.
    • Memory Resource Management - Now with Oracle 12cR2, it is possible to set the following parameters at PDB level (which were previously modifiable only at CDB level):
      • SGA_TARGET
      • SGA_MIN_SIZE (new in 12.2)
      • DB_CACHE_SIZE
      • DB_SHARED_POOL_SIZE
      • PGA_AGGREGATE_LIMIT
      • PGA_AGGREGATE_TARGET
    • PDB Cloning - "Hot Clone" is now supported with Oracle 12cR2, i.e. it is possible to clone a PDB while the source PDB is in OPEN READ WRITE mode; hence, the on-line cloning is available without interrupting operations in the source PDB. 

    Additional cool 12cR2 Multitenant Enhancements 

    Additional great 12c Multitenant features which I believe worth mentioning are:
    • 4K PDBs Per Container - In Oracle 12cR1, the maximum number user pluggable databases per CDB was 252. Now with Oracle 12cR2, the limit has been increased to 4,096.
    • Lockdown profiles - This feature allows to define granular control over network access, common users, common objects, and administrative features. For example, it can be used to limit developers that in a specific PDB, they can execute ALTER SYSTEM SET command only to set a specific parameter like plsql_warnings. This is done by defining a lockdown profile and apply it to the PDB. This feature could be very useful in some cases as we probably don't wont to grant the ALTER SYSTEM SET to developers as they should not have permissions to change other parameters.
    • Character Set at PDB Level - In Oracle 12cR1, character set was defined at CDB level, i.e. all PDBs within the same CDB must be defined with the same character set. Now with Oracle 12cR2, it is possible to define different character sets for different PDBs with the same CDB.
    • AWR at PDB Level - In Oracle 12cR1, all the AWR data was stored at the CDB root container (CDB$ROOT), meaning that if a DBA unplugs a PDB and plugs it into a different CDB, all the AWR data will be lost. Now with Oracle 12cR2, AWR data is available at PDB level.
    • Data Guard Broker - Now allows to perform a "PDB" level failover. The way it is implemented is by having 2 pairs of CDBs in 2 servers - one pair for the primary and another pair for the standby, with replication in opposite directions. Once a PDB fails, the standby counterpart can be moved to the other CDB within the same server. Since no physical movement of copying files is required (as this involves shared storage), this process can be done with minimum downtime. This feature effectively allows a PDB-level failover to standby without having to fail over the entire CDB.
    • Refreshebale PDB - This new feature allows to have a cloned PDB that can be refreshed from another PDB either manually (on-demand) or automatically (scheduled refresh). The way this works is by creating a full clone at first stage (which can be taken with no downtime now with the new 12cR2 hot clone feature), and then there is no need to perform a full clone again because this feature allows applying incremental redo since the last clone or the last refresh time. This could be very useful in scenarios that the source PDB is very large and we would like to avoid creating a full clone to the entire PDB from scratch every time, because this process takes a long time when the PDB is very large. By having to apply only the incremental redo since last refresh or last clone, the process of having up to date cloned PDB for dev/test purposes becomes much faster and easier.

    Summary

    Oracle 12cR2 introduced several significant Multitenant enhancements. In this post, I've listed my favorite Oracle 12cR2 Multitenant enhancements. Note that Oracle 12cR2 is currently available only in Oracle Cloud and not in "regular" on premise deployments.

    Sunday, August 21, 2016

    Where Oracle 12c Multitenant can (and should) be improved

    Introduction

    Since it was introduced in Oracle Database 12c, the new exciting Oracle Multitenant architecture makes the Oracle DBA's life much easier in terms of administration and management. It allows to manage many Oracle Databases as one and by doing so it solves many consolidation challenges.
    In this post, I would like to review 3 items where I believe Oracle can (and should) improve its Multitenant capabiliities:
    • DBCA Installation
    • Flashback Database
    • Memory Resource Management
    • PDB Cloning

    DBCA Installation

    During an installation of Oracle 12c with a container database (A.K.A multitenant), DBCA doesn't allow deselecting database options and it basically force you to install all the optional database options. See the following screenshot for example:







































    I think it would be nice if Oracle will allow deselecting database options because the current design has few disadvantages:
    • It makes the installation process much longer
    • It consumes more disk space
    • Installing unneeded database options may raise the chances to hit potential bugs.
    I've raised an idea in the Oracle community to allow deselecting database options during Oracle 12c Multitenant installation. See: https://community.oracle.com/ideas/6778

    Flashback Database

    The "Flashback Database" feature is supported only at the CDB-level, and it does not allow a "granular flashback" at the PDB-level. In many cases, the requirement is to rewind one specific application (i.e. PDB), and not the entire CDB. RMAN, for example, allows a PDB point-in-time-recovery, so the same granularity should also be available in the FLASHBACK DATABASE feature. 
    I've raised an idea in the Oracle community to allow using the Flashback Database feature at the PDB-level. See: https://community.oracle.com/ideas/12367

    Memory Resource Management

    In Oracle 12c, Oracle Resource Manager has been enhanced to support Multitenant by allowing to limit and prioritize CPU resources among competing PDBs. Currently it is not possible to prioritize memory resources; For example, today we cannot specify which PDBs will have higher priority for SGA areas than other PDBs. The outcome of this is that potentially a low-priority PDB can consume most of the SGA buffers. 
    I've raised an idea in the Oracle community to enhance Oracle Resource Manager to be able also to limit and prioritize memory resources among competing PDBs.


    Online PDB Cloning
    Cloning a PDB is a great feature which allows fast provisioning by creating a copy of a PDB either from another PDB within the same CDB, or from another PDB in a remote CDB. The problem with this feature is that cloning a PDB requires the source PDB to be in OPEN READ ONLY mode which essentially requires a down time as no DML/DDL operations are allowed on PDB when it's in a READ ONLY mode.


    Summary

    Generally speaking, Oracle multitenant is a full-featured product with many advantages. I would definitely recommend every Oracle DBA to upgrade to Oracle 12c Multitenant. Yet, I believe that there are some areas where Oracle can and should improve regarding its Multitenant feature and I hope we will see some Multitenant enhancements in the upcoming Oracle release (12cR2). 

    Monday, July 4, 2016

    My Upcoming Webinar for DBTA (Database Trend and Applications)

    I'm happy to update that I will be presenting a webinar on How Oracle 12c Multitenant Architecture Can Simplify Database Consolidation. The webcast is organized by DBTA (Database Trends and Applications).

    Thursday, June 23, 2016

    Oracle Database 12c Deployment Options

    Introduction

    Oracle Database 12c Multitenant Architecture is definitely the highlight feature of Oracle 12c. In this article we will review the Deployment Options for Oracle Database 12: Multitenant, Single Tenant, and Non-CDB.

    Multitenant

    Multitenant is the new paradigm in Oracle 12c which allows managing multiple Pluggable Databases in a single instance (i.e. Single Instance configuration) or multiple Pluggable Databases with multiple instances (i.e. RAC configuration). This is an extra cost option which is available only for Enterprise Edition Databases. In order to choose the Multitenant Option, check the "Create As Container Database" option in DBCA, as follows:


























    Single Tenant

    Single Tenant is similar to the Multitenant in terms of the architecture and pluggable databases capabilities such as unplug/plug and PDB cloning options; however, it allows having only one single Pluggable database. This option does not require the extra cost license, as opposed to the Multitenant option. 

    Non-CDB

    Non-CDB basically means the old pre-12c architecture. In this option there is only one single Database with one single instance (i.e. Single Instance configuration), or one single Database with multiple instances (i.e. RAC configuration). In either case, Single Instance or RAC, there is only one single Database. The concept of Pluggable Databases is irrelevant and the 12c PDB options such as unplug/plug and clone are not available. In order to use the Non-CDB option, uncheck the "Create As Container Database" option in DBCA, as follows:






























    Single Tenant vs. Non-CDB

    In case that you are interested in upgrading to Oracle Database 12c, but you are not interested in using the Multitenant option, you may ask yourself whether you should use Single Tenant or Non-CDB. Personally, I highly recommend on adopting  the Single Tenant due to the following reasons:
    • Unplug/Plug - With Single Tenant you could unplug your PDB and plug it into a different CDB which could simplify upgrades and migrations in many cases.
    • Fast Cloning - With Single Tenant you could fast clone your PDB to a different CDB which could simplify data migrations in many cases.
    • Deprecation of Non-CDB Architecture - As per Oracle official documentation "The non-CDB architecture is deprecated in Oracle Database 12c, and may be desupported and unavailable in a release after Oracle Database 12c Release 2. Oracle recommends use of the CDB architecture.". This means that in the future we will all have to use either Single Tenant or Multitenant, so I believe it is better to get the knowledge and skills for managing Pluggable Databases as soon as possible.

    Summary

    I've created the following diagram which covers the 3 deployment options in Oracle Database 12c.
























    Additional Resources


    Wednesday, March 9, 2016

    Oracle 12c Improved Column Addition

    Introduction

    In my "Oracle 12c Improved Defaults" article, I review the enhancements of column defaults in Oracle 12c.
    "Oracle 12c Improved Column Addition" is a continuation article because it covers performance improvements for adding a new column with a default value to an existing table.

    Before Oracle 11g 

    Prior to Oracle 11g, adding a new column with a default value to an existing table, requires updating all the rows in the table which is a very long and high consuming operation, especially if the table is big.

    Oracle 11g 

    In Oracle 11g a new feature named "Metadata-only value" was introduced. This means that upon every new column addition with a default value, Oracle will not update every row in a table, but rather make a small metadata-only change in the data dictionary. As a result, every time that a user queries the table, Oracle will obtain the default value from the data dictionary. This feaure allows us to add a new column with a default value within milliseconds instead of waiting for a long time and generating lots of redo and undo which may impact the entire database.

    In the following example, a table named "TEST" is being created and populated with 100,000 rows.
    Afterwards, we will test the time it takes to add a new column with a default value:
    SQL> CREATE TABLE TEST (ID NUMBER);
    Table created.
    
    SQL>BEGIN
            FOR i in 1 .. 100000 LOOP
                EXECUTE IMMEDIATE 'INSERT INTO test VALUES (:i)' USING i;
            END LOOP;
        END;
        /
    PL/SQL PROCEDURE successfully completed.
    
    SQL> COMMIT;
    Commit complete.
    
    SQL>set timing ON
    
    SQL> ALTER TABLE test ADD string1 VARCHAR2(20) DEFAULT 'DUMMY TEXT' NOT NULL;
    Table altered.
    Elapsed: 00:00:00.10
    
    As you can see in the above demonstration, the column addition operation completed instantly.
    However, this feature works only if the column was added with the NOT NULL constraint.
    Let us see what will happen if we will try to add a nullable column with a default value (i.e. a column that doesn't have the NOT NULL constraint):
    SQL> ALTER TABLE test ADD string2 VARCHAR2(20) DEFAULT 'DUMMY TEXT';
    Table altered.
    Elapsed: 00:01:08.17
    
    Now, the operation took more than 1 minute! This table is only populated with 100K rows. Imagine how much time it would take if a table contains hundreds of millions of rows ...

    Oracle 12c

    In Oracle 12c, Oracle took the "metadata-only value" feature one step further.
    Now, this feature is supports nullable column, as per the below demonstration:
    SQL> ALTER TABLE test ADD string VARCHAR2(20) DEFAULT 'DUMMY TEXT';
    Table altered.
    Elapsed: 00:00:00.06
    
    As you can see, in Oracle 12c adding a column with a default value is an instant operation, regardless of the the nullable attribute.

    Tuesday, February 16, 2016

    Creating Multiple Indexes on the same column or set of columns

    Introduction

    Oracle 11g Introduced a nice feature named "Invisible Indexes" which allows us to mark an index as invisible. This index will still be maintained by Oracle during DML operations like a "regular" index but it is being ignored by the optimizer, i.e. execution plans will not use the invisible indexes.

    Oracle 12c leverages the "Invisible Indexes" feature by allowing us create multiple indexes on the same column or set of columns, as long as only one index is visible and all the indexes are different, i.e. it is impossible to create 2 B-Tree indexes on the same column, even if one of them is invisible.

    Demonstration

    For this demonstration, we will create a table of employees and populate it with 10 records:
    SQL> CREATE TABLE EMP
      2  (
      3     id          NUMBER,
      4     name        VARCHAR2 (20),
      5     salary      NUMBER,
      6     hire_date   DATE
      8  );
    Table created.
    
    SQL> insert into EMP value values (1, 'DAVID', 8500, '07-APR-15');
    1 row created.
    
    SQL> insert into EMP value values (2, 'JOHN', 10000, '17-MAR-15');
    1 row created.
    
    SQL> insert into EMP value values (3, 'JANE', 13500, '23-DEC-15');
    1 row created.
    
    SQL> insert into EMP value values (4, 'DAN', 15000, '02-JAN-15');
    1 row created.
    
    SQL> insert into EMP value values (5, 'RACHEL', 19000, '20-FEB-15');
    1 row created.
    
    SQL> insert into EMP value values (6, 'BRAD', 20000, '25-JUN-15');
    1 row created.
    
    SQL> insert into EMP value values (7, 'TIM', 15000, '16-MAR-15');
    1 row created.
    
    SQL> insert into EMP value values (8, 'KELLY', 9000, '28-APR-15');
    1 row created.
    
    SQL> insert into EMP value values (9, 'NICK', 7500, '04-FEB-15');
    1 row created.
    
    SQL> insert into EMP value values (10, 'ERIK', 6000, '09-JUL-15');
    1 row created.
    
    SQL> commit;
    Commit complete.
    
    SQL> select * from EMP;
            ID NAME                     SALARY HIRE_DATE
    ---------- -------------------- ---------- ---------
             1 DAVID                      8500 07-APR-15
             2 JOHN                      10000 17-MAR-15
             3 JANE                      13500 23-DEC-15
             4 DAN                       15000 02-JAN-15
             5 RACHEL                    19000 20-FEB-15
             6 BRAD                      20000 25-JUN-15
             7 TIM                       15000 16-MAR-15
             8 KELLY                      9000 28-APR-15
             9 NICK                       7500 04-FEB-15
            10 ERIK                       6000 09-JUL-15
    10 rows selected.
    
    Now, we will create 2 different indexes on the "id" column, the first index will be a visible B-Tree index, and the second index will be an invisible Bitmap index:
    SQL> CREATE INDEX BTREE_IDX ON EMP (ID);
    Index created.
    
    SQL> CREATE BITMAP INDEX BITMAP_IDX ON EMP (ID);
    CREATE BITMAP INDEX BITMAP_IDX ON EMP (ID)
                                           *
    ERROR at line 1:
    ORA-01408: such column list already indexed
    
    
    SQL> CREATE BITMAP INDEX BITMAP_IDX ON EMP (ID) INVISIBLE;
    Index created.
    
    The error in the second statement is expected, because the B-Tree index is visible and as previously mentioned, only one index can be visible at a time. Using USER_INDEXES dictionary view, it is possible to determine the current status of the indexes:
    select index_name, index_type, visibility
      from user_indexes
     where TABLE_NAME = 'EMP'
    
    INDEX_NAME INDEX_TYPE                  VISIBILIT
    ---------- --------------------------- ---------
    BTREE_IDX  NORMAL                      VISIBLE
    BITMAP_IDX BITMAP                      INVISIBLE
    
    If we want to test the performance of the SQL statements, or investigate the execution plans after switching between the indexes, it can be done very easily, as follows:
    SQL> ALTER INDEX BTREE_IDX INVISIBLE;
    Index altered.
    
    SQL> ALTER INDEX BITMAP_IDX VISIBLE;
    Index altered.
    
    SQL> SELECT index_name, index_type, visibility
      FROM user_indexes
     WHERE TABLE_NAME = 'EMP';
    
    INDEX_NAME INDEX_TYPE                  VISIBILIT
    ---------- --------------------------- ---------
    BTREE_IDX  NORMAL                      INVISIBLE
    BITMAP_IDX BITMAP                      VISIBLE
    
    

    Summary

    In this article I have demonstrated a new 12c feature that allows create more than one index on a column or set of columns, assuming the indexes are from different types.
    This can be useful when you want to test the impact of different indexes easily without dropping and creating a new index. However, it is important to bear in mind that additional indexes means additional overhead during DML operations. This additional overhead is necessary in order to maintain the indexes, therefore use this feature with cautious

    Monday, February 15, 2016

    Oracle 12c Improved Defaults

    Introduction

    Oracle version 12c introduced some very nice and useful enhancements for table column defaults. This includes the following enhancements:
    • Identity columns
    • Default value when using a sequence
    • Default value when NULL is inserted

    Identity columns

    When identity column is created for a table, it will automatically generate an auto increment value upon every row insertion. It has the following options:
    • ALWAYS (default option) - In this option, Oracle forces the usage of the sequence generator. If an insert or update statements reference the identity column, Oracle raises an error.
    • BY DEFAULT - This option allows to explicitly assign a value for the column as part of an INSERT or UPDATE statements.
    • BY DEFAULT ON NULL - In the previous option (BY DEFAULT), If you will attempt to reference the identity column, and assign it a NULL value, you will encounter an error. Using the BY DEFAULT ON NULL option tells Oracle to use the identity generator when there is an attempt to insert a NULL to the table.


    ALWAYS

    In the first demonstration, I will create a sample table named "PRODUCTS" with an identity column for the primary key, that uses the ALWAYS option.
    CREATE TABLE PRODUCTS
    (
       ID     NUMBER GENERATED ALWAYS AS IDENTITY,
       NAME   VARCHAR2 (20),
       CONSTRAINT PK_ID PRIMARY KEY (ID)
    );
    
    SQL> insert into PRODUCTS(NAME) values ('Product1');
    1 row created.
    
    SQL> insert into PRODUCTS(ID, NAME) values (2, 'Product1');
    ORA-32795: cannot insert into a generated always identity column
    
    SQL> insert into PRODUCTS(ID, NAME)  values (NULL, 'Product2');
    ORA-32795: cannot insert into a generated always identity column
    
    As you can see in the above demonstration, when trying to reference the identity column, Oracle raises an error.

    BY DEFAULT

    In the next demonstration, a "BY DEFAULT" clause is used for the identity column, which allows us to explicitly assign a value for the identity column (as long as it is not NULL), as follows:
    SQL> DROP TABLE PRODUCTS PURGE;
    Table dropped.
    
    CREATE TABLE PRODUCTS
    (
       ID     NUMBER GENERATED BY DEFAULT AS IDENTITY,
       NAME   VARCHAR2 (20),
       CONSTRAINT PK_ID PRIMARY KEY (ID)
    );
    Table created.
    
    SQL> insert into PRODUCTS(NAME) values ('Product1');
    1 row created.
    
    SQL> insert into PRODUCTS (ID, NAME) values (2, 'Product1');
    1 row created.
    
    SQL> insert into PRODUCTS(ID, NAME)  values (NULL, 'Product2');
    ORA-01400: cannot insert NULL into ("SYS"."PRODUCTS"."ID")
    

    BY DEFAULT ON NULL

    Now, we will specify the "BY DEFAULT ON NULL" clause, which tells Oracle to use the identity generator even if NULL value is explicitly assigned:
    SQL> DROP TABLE PRODUCTS PURGE;
    Table dropped.
    
    CREATE TABLE PRODUCTS
    (
       ID     NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
       NAME   VARCHAR2 (20),
       CONSTRAINT PK_ID PRIMARY KEY (ID)
    );
    Table created.
    
    SQL> insert into PRODUCTS(NAME) values ('Product1');
    1 row created.
    
    SQL> insert into PRODUCTS (ID, NAME) values (102, 'Product1');
    1 row created.
    
    SQL>  insert into PRODUCTS(ID, NAME)  values (NULL, 'Product2');
    1 row created.
    
    SQL> select * from PRODUCTS;
    
            ID NAME
    ---------- --------------------
             1 Product1
           102 Product1
             2 Product2
    
    When creating an identity column, Oracle "behind the scenes" creates a sequence. This means that all the options that available for sequences (e.g. START WITH , INCREMENT BY, MAX_VALUE, etc.) are available for identify columns as well, as you can see in the following example:
    SQL> DROP TABLE PRODUCTS PURGE;
    Table dropped.
    
    CREATE TABLE PRODUCTS
    (
       ID     NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 10 INCREMENT BY 2),
       NAME   VARCHAR2 (20),
       CONSTRAINT PK_ID PRIMARY KEY (ID)
    );
    Table created.
    It is possible to query the [CDB|DBA|ALL|USER]_tab_identity_cols dictionary view to obtain all of the table identity columns, as follows:
    SQL> select table_name,column_name,generation_type, identity_options, sequence_name 
         from user_tab_identity_cols;
    
    TABLE_NAME COLUMN_NAME GENERATION IDENTITY_OPTIONS                    SEQUENCE_NAME
    ---------- ----------- ---------- ----------------------------------- ---------------
    PRODUCTS   ID          BY DEFAULT START WITH: 10, INCREMENT BY: 2, MA ISEQ$$_176970
                                      X_VALUE: 99999999999999999999999999
                                      99, MIN_VALUE: 1, CYCLE_FLAG: N, CA
                                      CHE_SIZE: 20, ORDER_FLAG: N
    

    Additional Comments

    • Identity  columns are NOT NULL, i.e. you cannot assign a NULL value to identity column
    • As previously mentioned, when creating an identity column, Oracle automatically creates a sequence. This means that in order to use this feature, the use must have the CREATE SEQUENCE privilege.

    Default value when using a sequence

    Another enhacement in the improved defaults which was introduced in Oracle 12c, it the ability to use a sequence as a default value for a column. It can be either CURRVAL or NEXTVAL. The following is a demonstration of this feature:
    SQL> CREATE SEQUENCE id_seq;
    Sequence created.
    
    CREATE TABLE PRODUCTS
    (
       ID     NUMBER DEFAULT ID_SEQ.NEXTVAL,
       NAME   VARCHAR2(20)
    );
    Table created.
    
    SQL> insert into PRODUCTS (NAME) values ('Product1');
    1 row created.
    
    SQL> insert into PRODUCTS (ID, NAME) values (DEFAULT, 'Product2');
    1 row created.
    
    SQL>  insert into PRODUCTS (ID,NAME) values (NULL,'Product3');
    1 row created.
    
    SQL> select * from PRODUCTS;
    
            ID NAME
    ---------- --------------------
             1 Product1
             2 Product2
               Product3
    

    As you can see in the above example, Oracle will use the sequence as a default value if the column is not referenced, or if it is referenced and the value "DEFAULT" is specified. When an explicit NULL value was inserted, the default sequence value is not being used, instead, a NULL was inserted to the table.

    Additional Comments

    • Creating a table with a default sequence value, requires having a SELECT privilege on the sequence (in addition to the CREATE TABLE privilege)
    • Inserting a value to the table with a default sequence requires having a SELECT privilege on the sequence (in addition to the INSERT privilege on that table)


    Default value when NULL is inserted

    In order to use a default sequence number even when an explicit NULL is being inserted, you can use the ON NULL clause. Let us see a demonstration of this feature:
    SQL>  DROP SEQUENCE id_seq;
    Sequence dropped.
    
    SQL> DROP TABLE PRODUCTS PURGE;
    Table dropped.
    
    SQL> CREATE SEQUENCE id_seq;
    Sequence created.
    
    CREATE TABLE PRODUCTS
    (
       ID     NUMBER DEFAULT ON NULL ID_SEQ.NEXTVAL,
       NAME   VARCHAR2(20)
    );
    Table created.
    
    SQL> insert into PRODUCTS (NAME) values ('Product1');
    1 row created.
    
    SQL> insert into PRODUCTS (ID, NAME) values (DEFAULT, 'Product2');
    1 row created.
    
    SQL> insert into PRODUCTS (ID,NAME) values (NULL,'Product3');
    1 row created.
    
    SQL> select * from PRODUCTS;
    
            ID NAME
    ---------- --------------------
             1 Product1
             2 Product2
             3 Product3

    Summary

    In earlier releases (prior to Oracle 12c), there was no straightforward way to specify an auto increment value for a column; in order to solve this challenge, a common solution was creating a sequence and a trigger that inserts the sequence next value upon every insert to the table. Starting with Oracle 12c, there are 2 options that can provide that - using sequence as a default value, and using an identity columns. Both options provide a better performance than the old trigger approach, and both are simpler to use.

    Saturday, January 23, 2016

    Oracle 12c enhanced syntax for row-limiting (A.K.A Top-N queries)

    Introduction

    Prior to Oracle 12c we had to use the ROWNUM pseudo column in order to implement Top-N reporting. Following are few simple examples of Top-N queries:
    • Query the top 3 employees with the highest salaries
    • Query the first 2 employees who have joined the company

    Using the ROWNUM pseudo column had worked well, however it had major disadvantages:
    • It's not a straight-forward syntax, especially if you need to implement query paging such as query the second block of the top 3 employees with the highest salaries.
    • It allows us specifying the Top-N rows but it doesn't allows specifying the Top-N percent (for example, query the top 25% percent of employees with the highest salaries.

    In Oracle 12c, SQL SELECT syntax has been enhanced in order to address the 2 disadvantages with the ROWNUM pseudo column listed above. You can specify the number of rows or percentage of rows to return with the FETCH NEXT keywords. You can use the OFFSET keyword to specify that the returned rows begin with a row after the first row of the full result set. In the case of a negative OFFSET, the OFFSET value is treated as 0.

    Demonstration

    For the demonstration I will create a table of employees and populate it with 10 records:
    SQL> CREATE TABLE EMP
      2  (
      3     id          NUMBER,
      4     name        VARCHAR2 (20),
      5     salary      NUMBER,
      6     hire_date   DATE,
      7     CONSTRAINT id_pk PRIMARY KEY (id)
      8  );
    Table created.
    
    SQL> insert into EMP value values (1, 'DAVID', 8500, '07-APR-15');
    1 row created.
    
    SQL> insert into EMP value values (2, 'JOHN', 10000, '17-MAR-15');
    1 row created.
    
    SQL> insert into EMP value values (3, 'JANE', 13500, '23-DEC-15');
    1 row created.
    
    SQL> insert into EMP value values (4, 'DAN', 15000, '02-JAN-15');
    1 row created.
    
    SQL> insert into EMP value values (5, 'RACHEL', 19000, '20-FEB-15');
    1 row created.
    
    SQL> insert into EMP value values (6, 'BRAD', 20000, '25-JUN-15');
    1 row created.
    
    SQL> insert into EMP value values (7, 'TIM', 15000, '16-MAR-15');
    1 row created.
    
    SQL> insert into EMP value values (8, 'KELLY', 9000, '28-APR-15');
    1 row created.
    
    SQL> insert into EMP value values (9, 'NICK', 7500, '04-FEB-15');
    1 row created.
    
    SQL> insert into EMP value values (10, 'ERIK', 6000, '09-JUL-15');
    1 row created.
    
    SQL> commit;
    Commit complete.
    
    SQL> select * from EMP;
            ID NAME                     SALARY HIRE_DATE
    ---------- -------------------- ---------- ---------
             1 DAVID                      8500 07-APR-15
             2 JOHN                      10000 17-MAR-15
             3 JANE                      13500 23-DEC-15
             4 DAN                       15000 02-JAN-15
             5 RACHEL                    19000 20-FEB-15
             6 BRAD                      20000 25-JUN-15
             7 TIM                       15000 16-MAR-15
             8 KELLY                      9000 28-APR-15
             9 NICK                       7500 04-FEB-15
            10 ERIK                       6000 09-JUL-15
    10 rows selected.
    

    In my first example I will query the first 2 employees who have joined the company:
    SQL>   select *
      2    from EMP
      3    ORDER BY hire_date
      4    FETCH FIRST 2 ROWS ONLY;
            ID NAME                     SALARY HIRE_DATE
    ---------- -------------------- ---------- ---------
             4 DAN                       15000 02-JAN-15
             9 NICK                       7500 04-FEB-15
    
    Now, let's demonstrate a query paging via the OFFSET clause. In this example, I will query the second block of the first 2 employees who have joined the company:
    SQL>   select *
      2    from EMP
      3    order by hire_date
      4    offset 2 rows fetch next 2 ROWS ONLY;
            ID NAME                     SALARY HIRE_DATE
    ---------- -------------------- ---------- ---------
             5 RACHEL                    19000 20-FEB-15
             7 TIM                       15000 16-MAR-15
    
    Now, I will query the top 3 employees with the with the highest salaries:
    SQL>   select *
      2    from EMP
      3    order by salary DESC
      4    fetch first 3 rows only;
            ID NAME                     SALARY HIRE_DATE
    ---------- -------------------- ---------- ---------
             6 BRAD                      20000 25-JUN-15
             5 RACHEL                    19000 20-FEB-15
             4 DAN                       15000 02-JAN-15
    
    In my sample table there are 2 employees with the salary of 15000 : DAN and TIM, but because I used the "ONLY" clause it limited the output to be exactly 3 rows. By replacing the "ONLY" clause with the "WITH TIES" clause, it will diplay all the top-3 employees even if there is more than one employee with the 3rd highest salary , as follows:
    SQL>   select *
      2    from EMP
      3    order by salary DESC
      4    fetch first 3 rows with ties;
            ID NAME                     SALARY HIRE_DATE
    ---------- -------------------- ---------- ---------
             6 BRAD                      20000 25-JUN-15
             5 RACHEL                    19000 20-FEB-15
             4 DAN                       15000 02-JAN-15
             7 TIM                       15000 16-MAR-15
    

    In my last example I will demonstrate how to query the Top-N percent using the PERCENT clause. The following query retrieves the top 40% of employees with the lowest salary:
    SQL>   select *
      2    from EMP
      3    order by salary
      4    fetch first 40 percent rows only;
            ID NAME                     SALARY HIRE_DATE
    ---------- -------------------- ---------- ---------
            10 ERIK                       6000 09-JUL-15
             9 NICK                       7500 04-FEB-15
             1 DAVID                      8500 07-APR-15
             8 KELLY                      9000 28-APR-15
    


    Additional Notes

    • If you specify a negative OFFSET number, OFFSET is treated as 0. If you specify NULL or a number greater than or equal to the number of rows that are returned by the query, 0 rows are returned.​
    • The FIRST or NEXT keywords can be used interchangeably. They are provided for semantic clarity.
    • The ROW keyword and the ROWS keyword can be used interchangeably. They are provided for semantic clarity.

    Summary

    The new enhanced syntax for row-limiting is one of my favorite new features of Oracle 12c. It provides an extremely straight-forward syntax for implementing a Top-N and query paging instead of using less convenient syntax via the ROWNUM pseudo column as we used to do prior to Oracle 12c.

      Tuesday, December 29, 2015

      Oracle 12c Privilege Analysis

      Introduction

      In this post I'd like to demonstrate another new Oracle 12c security feature - Privilege Analysis. Using this feature, the DBA can easily understand which privileges are actually being used by users. Once the analysis has been completed, the DBA can revoke all of the unnecessary privileges/roles. This feature is only available for Enterprise Edition with Oracle Database Vault (extra cost option).

      How does it work?

      Oracle introduced a new package, DBMS_PRIVILEGE_CAPTURE which allows you to create a new privilege analysis policy, enable/disable it, generate the results of the analysis and drop the policy once the analysis is over and the policy is not needed anymore. In order to use the DBMS_PRIVILEGE_CAPTURE package you need to be grnted the CAPTURE_ADMIN role.

      Demonstration

      In order to create a new privilege analysis policy, use the following syntax:
      DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
         name              VARCHAR2, 
         description       VARCHAR2 DEFAULT NULL, 
         type              NUMBER DEFAULT DBMS_PRIVILEGE_CAPTURE.G_DATABASE, 
         roles             ROLE_NAME_LIST DEFAULT ROLE_NAME_LIST(), 
         condition         VARCHAR2 DEFAULT NULL);
      Let's start by creating a user named "pini" and grant him the DBA role. Afterwards, we'll create and enable a simple policy named "capture_pini_privs" that simply captures all the privileges used by user pini:
      SQL> create user pini identified by pini default tablespace users;
      User created.
      
      SQL> grant dba to pini;
      Grant succeeded.
      
      SQL> BEGIN
        2    DBMS_PRIVILEGE_CAPTURE.create_capture(
        3      name        => 'capture_pini_privs',
        4      type        => DBMS_PRIVILEGE_CAPTURE.g_context,
        5      condition   => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''PINI'''
        6    );
        7  END;
        8  /
      PL/SQL procedure successfully completed.
      
      SQL> EXECUTE DBMS_PRIVILEGE_CAPTURE.enable_capture('capture_pini_privs');
      PL/SQL procedure successfully completed.
      
      Explanation of the above code:

      - In line 3 I set the name of the policy to be "capture_pini_privs"
      - In line 4 I set the type to be DBMS_PRIVILEGE_CAPTURE.g_context to captrue privileges of the sessions defined in line 5
      - In line 5 I used SYS_CONTEXT to specify capture privileges for user "PINI" only

      After we have created the new policy we can verify it via DBA_PRIV_CAPTURES dictionary view that lists all the policies as follows:
      SQL> SELECT TYPE, enabled, context FROM DBA_PRIV_CAPTURES;
      
      TYPE             ENABLED    CONTEXT
      ---------------- ---------- --------------------------------------------------
      CONTEXT          Y          SYS_CONTEXT('USERENV', 'SESSION_USER') = 'PINI'
      

      Note that there are several other types that you can use (in line 4) like capturing privileges of the entire database users except of use SYS (using DBMS_PRIVILEGE_CAPTURE.G_DATABASE), or capturing the privileges of specific sessions with specific roles (DBMS_PRIVILEGE_CAPTURE.G_ROLE_AND_CONTEXT) which requires also to specify the "roles" parameter. You can read more about these options in the official documentation for the DBMS_PRIVILEGE_CAPTURE package.

      Now, after we have created and enabled to policy, the analysis period has been started.
      Next, let's connect with user pini and execute a few commands to test this feature:
      SQL> connect pini/pini
      Connected.
      
      SQL> create table EMP (id number, name varchar2(20), constraint id_pk PRIMARY KEY (id));
      Table created.
      
      SQL> insert into EMP values (1, 'DAVID');
      1 row created.
      
      SQL> commit;
      Commit complete.
      
      SQL> CREATE OR REPLACE FUNCTION return_num_of_emps
        2     RETURN NUMBER IS
        3     num_of_emps NUMBER;
        4     BEGIN
        5        select count(*)
        6        into num_of_emps
        7        from EMP;
        8        RETURN(num_of_emps);
        9      END;
       10  /
      Function created.
      
      SQL> select return_num_of_emps from dual;
      RETURN_NUM_OF_EMPS
      ------------------
                       1
      SQL> select instance_name from v$instance;
      INSTANCE_NAME
      ----------------
      o1264np
      
      SQL> select log_mode from v$database;
      LOG_MODE
      ------------
      NOARCHIVELOG
      Now, I will disable the capture policy and generate the report as follows:
      SQL> execute DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE('capture_pini_privs');
      PL/SQL procedure successfully completed.
      
      SQL> execute DBMS_PRIVILEGE_CAPTURE.generate_result('capture_pini_privs');
      PL/SQL procedure successfully completed.
      
      The output of the report will be printed to various dictionary views like DBA_USED_SYSPRIVS and DBA_USED_OBJPRIVS as you can see in the following demonstration:
      SQL> SELECT SYS_PRIV
        2    FROM DBA_USED_SYSPRIVS
        3   WHERE username = 'PINI';
      
      SYS_PRIV
      ---------------------------------------
      CREATE SESSION
      CREATE TABLE
      CREATE ANY INDEX
      CREATE PROCEDURE
      UNLIMITED TABLESPACE
      
      SQL> SELECT obj_priv, object_owner, object_name
        2    FROM DBA_USED_OBJPRIVS
        3   WHERE username = 'PINI';
      
      OBJ_PRIV                                 OBJECT_OWNER         OBJECT_NAME
      ---------------------------------------- -------------------- ----------------------------------------
      SELECT                                   SYS                  V_$INSTANCE
      SELECT                                   SYS                  V_$DATABASE
      

      Once the analysis has been completed, you can drop the policy. This step is optional and it's goal is to clean to policy-related information from the data-dictionary views. In order to do that use the following syntax:
      SQL> execute DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE ('capture_pini_privs');
      PL/SQL procedure successfully completed.
      


      Summary

      Granting minimum privileges for users is a security best practice, however, many DBAs are struggling with identifying which exact privileges are actually needed and which privileges are unnecessary - and therefore, should be revoked. This feature comes to solve this challenge. I would expect this useful security feature to be available for all Oracle Editions but unfortunately, it's only available for Enterprise Edition with Oracle Database Vault (extra cost option). 

      Monday, December 14, 2015

      Oracle Multiprocess and Multithreaded Architecture

      Introduction

      The Oracle process architecture of Windows operating system is a different process architecture comparing to Linux/Unix operating systems. On Windows, each Oracle instance has one single process that is multithreded, i.e. each background process is a thread within the single "father" process, therefore the "processes" tab in the the task manager will display only one "oracle.exe" process per each instance, as you can see in the following screenshot (taken from one of our Oracle environments):




























      On Linux/Unix on the other hand, each background process is a different OS process so by looking for all the processes you should expect to get a long list of Oracle processes, as you can see the following screenshot (taken from one of our Oracle on Linux environments):






















































      Oracle 12c Multiprocess and Multithreaded Architecture

      By default, the pre-12c Oracle process architecture remains the same in Oracle 12c. However, in Oracle 12c you can enable the multithreaded architecture on Unix/Linux that will cause Oracle processes on Linux/Unix to run as threads - similar to the process architecture on Windows. In order to do so, simply set the THREADED_EXECUTION initialization parameter to TRUE (default value is FALSE). This is a static parameter (SCOPE=SPFILE), therefore requires restarting the instance in order for the changes to take effect.

      Demonstration

      First, I will connect to one of our 12c Oracle Linux environments and set the THREADED_EXECUTION to be TRUE:
      SQL> alter system set THREADED_EXECUTION=true scope=spfile;
      System altered.
      
      SQL> shu immediate;
      Database closed.
      Database dismounted.
      ORACLE instance shut down.
      
      SQL> startup
      Total System Global Area 1073741824 bytes
      Fixed Size                  2932632 bytes
      Variable Size             692060264 bytes
      Database Buffers          373293056 bytes
      Redo Buffers                5455872 bytes
      Database mounted.
      Database opened.
      

      Now I will check how many Oracle processes I have on the Linux environment:













      As you can see, now there are much less processes than before. Most of the background processes now run as threads instead of processes but some of them might still run as operating system processes (like the PMON and the DB Writer processes).

      Important things to keep in mind

      • OS authentication is not allowed with the multithreaded architecture. If you will try to connect via an OS authentication (for example, by connecting with "/ AS SYSDBA") you will encounter "ORA-01017: invalid username/password; logon denied", therefore you must use a password file for connecting with a SYSDBA/SYSOPER user.
      • When the THREADED_EXECUTION parameter is set to TRUE, you must set the DEDICATED_THROUGH_BROKER_LISTENER parameter to ON in your listener.​ora file. When that parameter is set, the listener knows that it should not spawn an OS process when a connect request is received, instead it passes the request to the database so that a database thread is spawned and answers the connection.​ 
      • In RAC environments all nodes must have the same value for the THREADED_EXECUTION parameter.

      Summary

      In this post I've reviewed the process architecture in Linux/Unix vs. Windows and demonstrated the new 12c Multithreaded option. This feature can be useful in order to reduce the number of OS processes in scenarios where you have many Oracle instances on the same machine which may cause a higher overhead as a result of a very high number of OS processes.

      Useful Links


      Wednesday, December 9, 2015

      Oracle 12c ADR enhancements

      Introduction

      Oracle 11g introduced new locations for storing the diagnostics-related logs (e.g. alert log, background trace files, foreground trace files, core dumps, etc.). This feature named ADR (Automatic Diagnostic Repository). The base directory for the ADR is defined by the DIAGNOSTIC_DEST parameter. You can also obtain the specific location of each ADR file type via V$DIAG_INFO as follows:
      SQL> show parameter diagnostic
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      diagnostic_dest                      string      C:\APP\ORACLE
      
      SQL> select name,value from v$diag_info;
      NAME                           VALUE
      ------------------------------ ------------------------------------------------------------
      Diag Enabled                   TRUE
      ADR Base                       C:\APP\ORACLE
      ADR Home                       C:\APP\ORACLE\diag\rdbms\orcl11\orcl11
      Diag Trace                     C:\APP\ORACLE\diag\rdbms\orcl11\orcl11\trace
      Diag Alert                     C:\APP\ORACLE\diag\rdbms\orcl11\orcl11\alert
      Diag Incident                  C:\APP\ORACLE\diag\rdbms\orcl11\orcl11\incident
      Diag Cdump                     C:\app\oracle\diag\rdbms\orcl11\orcl11\cdump
      Health Monitor                 C:\APP\ORACLE\diag\rdbms\orcl11\orcl11\hm
      Default Trace File             C:\APP\ORACLE\diag\rdbms\orcl11\orcl11\trace\orcl11_ora_1010
      
      The ADRCI (ADR Command Interpreter) that was introduced in Oracle 11gR1 is a command line tool that is being installed as part of the Oracle software installation (similar to SQL*PLUS) and its purpose is to view and manage ADR diagnostic data needed for troubleshooting issues. More informatin about the ADRCI is available here.


      DDL Logging

      In Oracle 12c you can enable DDL logging which will audit all the DDL statements. Oracle Database 12c turns DDL logging off by default. The parameter ENABLE_DDL_LOGGING must be set to TRUE to activate DDL logging.​ DDL logs are stored in separate files and directories. DDL logs can be found in the $ADR_HOME/log/ddl. Let's see a quick demonstration of this feature:
      SQL> show parameter enable_ddl_logging
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- -------
      enable_ddl_logging                   boolean     FALSE
      
      SQL> alter system set enable_ddl_logging=true;
      System altered.
      
      SQL> create table test_ddl_logging (id number);
      Table created.
      
      SQL> drop table test_ddl_logging;
      Table dropped.
      
      SQL> alter system set enable_ddl_logging=false;
      System altered.
      
      Now I will connect to the machine via putty in order to view the DDL log file:





      As you can see the log file prints the CREATE TABLE and DROP TABLE commands.
      It's also possible to view the contents of the DDL log file via the ADRCI "SHOW LOG" command which will show you the actual statements and the execution date for each statements that was executed.

      DEBUG Logging

      Some low-severity issues that don't require immediate action can trigger a warning which will be recorded in a debug log file (instead of the alert log). These messages might be needed to diagnose a future problem and the debug information is included as part of the incident Packaging Service (IPS) which can be shipped to the Oracle support when needed.
      The location of the debug log file is $ADR_BASE/diag/rdbms/{DB-name}/{SID}/log/debug

      Useful Links

      Tuesday, December 8, 2015

      Demonstration of Oracle 12c Data Redaction

      Introduction

      In this post I'd like to demonstrate the new Oracle 12c security feature - Data Redaction. 
      Although it was introduced in Oracle 12c, a similar feature named "column masking" has been introduced back in 2003 with Oracle version 10g. Data Redaction allows you to protect confidential data by masking the sensitive information from unauthorized users. The way for Oracle to know if the user is authorized to see the real data is by using the EXEMPT REDACTION POLICY system privilege. If the user doesn't have the EXEMPT REDACTION POLICY system privilege it means that he is unauthorized and therefore if a redaction policy is defined for the data the the user attempts to query, the output will be redacted rather than the real data.

      Data Redaction Policies

      Data Redaction policy for a table or view defines which column/s will be redacted and in which manner. 
      The management of redaction policies is done via the DBMS_REDACT package. 
      For example, you can create a new policy DBMS_REDACT.ADD_POLICY procedure, modify an existing policy using the DBMS_REDACT.ALTER_POLICY, disable a policy using DBMS_REDACT.DISABLE_POLICY and drop a policy using DBMS_REDACT.DROP_POLICY.

      Demonstration

      First, let's create a table and insert a 2 records to the table.
      SQL> create table EMPLOYEE (id number, name varchar2(20), join_date timestamp);
      Table created.
      
      SQL> insert into EMPLOYEE values (1, 'PINI',sysdate);
      1 row created.
      
      SQL> insert into EMPLOYEE values (2, 'DAVID',sysdate);
      1 row created.
      SQL> commit;
      Commit complete.
      
      SQL> select * from EMPLOYREE;
      
              ID NAME                 JOIN_DATE
      ---------- -------------------- ------------------------------
               1 PINI                 08-DEC-15 03.33.27.000000 PM
               2 DAVID                08-DEC-15 03.33.28.000000 PM
      

      Let's start by creating a new redaction policy named "REDACT_EMPLOYEE" for the "Name" column
      SQL> BEGIN
        2     DBMS_REDACT.add_policy (object_name     => 'EMPLOYEE',
        3                             column_name     => 'ID',
        4                             policy_name     => 'REDACT_EMPLOYEE',
        5                             function_type   =>  DBMS_REDACT.full,
        6                             expression      => '1=1');
        7  END;
        8  /
      PL/SQL procedure successfully completed.
      
      SQL> select * from EMPLOYEE;
      
              ID NAME                 JOIN_DATE
      ---------- -------------------- ------------------------------
               0 PINI                 08-DEC-15 03.33.27.000000 PM
               0 DAVID                08-DEC-15 03.33.28.000000 PM
      

      As we can see, the value of the ID column has been changed to 0. You may ask yourself, why 0? How Oracle decides which values to use for the masking? We'll discuss about it in a minute. Now, let's redact the rest of the columns (Name and JOIN_DATE) using the ALTER_POLICY procedure. In order to do that, we'll set the value of "action" parameter to be the DBMS_REDACT.ADD_COLUMN constant, as follows:
      SQL> BEGIN
        2     DBMS_REDACT.alter_policy (object_name     => 'EMPLOYEE',
        3                             column_name     => 'NAME',
        4                             policy_name     => 'REDACT_EMPLOYEE',
        5                             function_type   => DBMS_REDACT.full,
        6                             expression      => '1=1',
        7                             action          => DBMS_REDACT.ADD_COLUMN);
        8  END;
        9  /
      PL/SQL procedure successfully completed.
      
      SQL> BEGIN
        2     DBMS_REDACT.alter_policy (object_name     => 'EMPLOYEE',
        3                             column_name     => 'JOIN_DATE',
        4                             policy_name     => 'REDACT_EMPLOYEE',
        5                             function_type   => DBMS_REDACT.full,
        6                             expression      => '1=1',
        7                             action          => DBMS_REDACT.ADD_COLUMN);
        8  END;
        9  /
      PL/SQL procedure successfully completed.
      
      SQL> select * from EMPLOYEE;
      
              ID NAME                 JOIN_DATE
      ---------- -------------------- ------------------------------
               0                      01-JAN-01 01.00.00.000000 AM
               0                      01-JAN-01 01.00.00.000000 AM
      
      As we can see, Oracle changed all values of the EMPLOYEE table with fixed values and that's because we set the function_type parameter to be DBMS_REDACT.FULL which is by the way, the default behaviour so if you will omit the function_type parameter, it will use a FULL redaction, i.e. it will use fixed values for the data redaction.
      The default redaction values for the FULL redaction can be obtained via the REDACTION_VALUES_FOR_TYPE_FULL dictionary view.

      As you can see, the default value for NUMBER is 0, for characther data types it's a single space and for date-time data types is the first day of January, 2001, which appears as 01-JAN-01.
      In order to change the default masking valuess when using a FULL redaction, you can use the UPDATE_FULL_REDACTION_VALUES Procedure. In addition to the default FULL redaction type, you can also choose to work with other function types:
      • NONE - No redaction
      • PARTIAL - Partial redaction, redact a portion of the column data
      • RANDOM - Random redaction, each query results in a different random value
      • REGEXP - Regular expression based redaction

      For example, you can change the function_type of all the columns to be RANDOM as follows:
      SQL> BEGIN
        2     DBMS_REDACT.alter_policy (object_name     => 'EMPLOYEE',
        3                               column_name     => 'ID',
        4                               policy_name     => 'REDACT_EMPLOYEE',
        5                               function_type   => DBMS_REDACT.RANDOM,
        6                               action          => DBMS_REDACT.MODIFY_COLUMN);
        7  END;
        8  /
      PL/SQL procedure successfully completed.
      
      SQL> BEGIN
        2     DBMS_REDACT.alter_policy (object_name     => 'EMPLOYEE',
        3                               column_name     => 'NAME',
        4                               policy_name     => 'REDACT_EMPLOYEE',
        5                               function_type   => DBMS_REDACT.RANDOM,
        6                               action          => DBMS_REDACT.MODIFY_COLUMN);
        7  END;
        8  /
      PL/SQL procedure successfully completed.
      
      SQL> BEGIN
        2     DBMS_REDACT.alter_policy (object_name     => 'EMPLOYEE',
        3                               column_name     => 'JOIN_DATE',
        4                               policy_name     => 'REDACT_EMPLOYEE',
        5                               function_type   => DBMS_REDACT.RANDOM,
        6                               action          => DBMS_REDACT.MODIFY_COLUMN);
        7  END;
        8  /
      PL/SQL procedure successfully completed.
      
      SQL> select * from EMPLOYEE;
      
              ID NAME                 JOIN_DATE
      ---------- -------------------- ------------------------------
               8 N(#i                 23-AUG-80 05.25.47.000000 AM
               5 SG*cK                11-DEC-90 04.19.26.000000 AM
      
      
      As I've mentioned in the beginning of this post, once a user has the EXEMPT REDACTION POLICY system privilege it means that he is authotized to view the original data. Let's connect with user SYS and grant EXEMPT REDACTION POLICY to user "pini":
      SQL> grant EXEMPT REDACTION POLICY to pini;
      Grant succeeded.
      
      SQL> connect pini/pini@//isrvmrh541:1521/pinidb
      Connected.
      SQL> select * from EMPLOYEE;
      
              ID NAME                 JOIN_DATE
      ---------- -------------------- ------------------------------
               1 PINI                 08-DEC-15 03.33.27.000000 PM
               2 DAVID                08-DEC-15 03.33.28.000000 PM
      
      As you can see, now user "pini" is able to view the original "confidential" data because he has the EXEMPT REDACTION POLICY privilege.

        License

        The data redaction feature is available as part of the "Advanced Security" option. The "Advanced Security" option is an extra cost option to the Enterprise Edition.

        Useful Links