Thursday 22 May 2014

Using TDE in a 12c Data Guard environment

Oracle Database 12c introduces a new set of commands to manage TDE cryptographic keys.
The new ADMINISTER KEY MANAGMENT group of commands enable a security administrator
manage the cryptography, allowing the administrator to create, and maintain the cryptographic keystore.

This is a demonstration of TDE in a 12c multitenant database environment using Active Data Guard.

Overview
We start with a Data Guard configuration which consists of a 12c single-instance CDB on the primary
site, protected by a physical standby. The primary has these tenant databases.

  • hrpdb - An demo HR database, which will contain the encrypted data
  • salespdb - A demo sales database.

The demonstration will progress in stages.

Stage 1: Establish the cryptography infrastructure on the two sites.
This part will demonstrate the procedures involved in creation of the keystore on the databases, key generation, activation, and transport of keys to the physical standby. After that, TDE will be enabled across all container databases on both the sites.

Stage 2: Demonstrate TDE operations with an table containing an encrypted column.
The physical standby will be opened in Active Data Guard mode.
We will then connect to the primary's HRPDB container, and create a table for storing employee information.

This table will contain a column for employee social security numbers, which will be
encrypted transparently using 192 bit AES encryption.

At the standby, once it has applied the redo from the demo table creation, we will demonstrate that we are able to query the encrypted data on the standby database using the TDE environment we established.

Stage 1: Establish the cryptography infrastructure on the two sites.

We start off with a Data Guard configuration consisting of the primary and physical standby in
maximum performance configuration. The databases are distinguished as SCOTT (primary) and
TIGER (standby) by their db_unique_name init parameter value.
Content of the v$dataguard_config view:







DB_UNIQUE_NAME PARENT_DBUN DEST_ROLE CURRENT_SCN CON_ID
-------------- ------ ---------------- ----------- ------
SCOTT NONE PRIMARY DATABASE 1124183 0
TIGER SCOTT PHYSICAL STANDBY 1124170 0



TDE operations require the creation of a cryptographic keystore – the keystore is a protected repository for the cryptographic keys and other cryptography related data. It can be stored on a either on a hard disk as a protected file, or hsm. In this example, we will use a file-based key store - also known as a software keystore. In a multi-database environment, each databases requires its own cryptographic keystore.

Step #1 - As the oracle home owner, create a folder location for the keystore on
the primary host using OS commands.

For example:
$ > mkdir -p /u01/app/oracle/oradata/wallets
Using a text editor, configure the ENCRYPTION_WALLET_LOCATION parameter in sqlnet.ora. The
default location of sqlnet.ora is <ORACLE_HOME>/network/admin.



ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=
/u01/app/oracle/oradata/wallets)))




You should edit the sqlnet.ora pointed to by the TNS_ADMIN environment variable if a TNS_ADMIN environment variable has been configured.

Step #2 - Connect to the root of the primary as sysdba or syskm, and run the
ADMINSTER KEY MANAGEMENT...CREATE KEYSTORE command to create the
keystore

The create keystore command accepts an argument which points to the location of the keystore. The location should be empty, and match the location configured in sqlnet.ora. For all key management operations, you need to supply the keystore password using the IDENTIFIED BY <password> syntax.

ADMINISTER KEY MANAGEMENT
CREATE KEYSTORE '/u01/app/oracle/oradata/wallets' IDENTIFIED BY oracle;






For example:

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/oradata/wallets'
2 IDENTIFIED BY oracle;
keystore altered.
SQL> !ls /u01/app/oracle/oradata/wallets
ewallet.p12
SQL> administer key management set keystore open identified by oracle;
keystore altered.




Notice above, that Oracle has created an empty key store with the name ewallet.p12.
We also open the keystore using the ADMINISTER KEY MANAGEMENT..SET KEYSTORE OPEN.

Note:
1. The keystore belongs to the entire CDB.
2. There is only one keystore per database.
3.The keystore can be open or closed in a given container.
4. It is necessary to open the keystore in root before a pdb can open it or perform TDE operations.

Step #3 - Still connected to the root container of the primary as sysdba or syskm,
generate keys on the primary (use ADMINISTER KEY MANAGEMENT..SET KEY)

The ADMINISTER KEY MANAGEMENT..SET KEY generates a key and also activates it.
It can be used for initial key generation and also for later key generation.

administer key management set key
using tag 'created_on_primary_for_all_pdbs'
identified by oracle with backup
container=all;

For example:







SQL> ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'created_on_primary_for_all_pdbs'
2 IDENTIFIED BY oracle WITH BACKUP CONTAINER=ALL;
keystore altered.





We generate the key with the CONTAINER=ALL modifier. This key will be available for TDE operations across all PDBS. We will use this key for the demonstration.

Note:
Key activation is not possible on the standby database, because it is doing managed recovery.
You need to do it on the primary before transporting the keys to it. If key activation is attempted on a standby, you will hit error ORA- 28388, which says that database is not open in read-write mode.



Optional - Query the key information on primary

Metadata information about the keys available in the currently opened keystore is exposed through the v$encryption_keys view.

The columns key_id, tag, activation_time, are useful for key administration
purposes. Still connected to the primary database, query the view as sysdba or syskm.
For example:



SQL> select key_id, tag, creator, creator_dbid from v$encryption_keys;
KEY_ID TAG
CREATOR CREATOR_DBID
------------------------------------------------------------ ---------------------------------------- -
--------- ------------
Aa4PJO1DBE8Yv3hQVA3F89YAAAAAAAAAAAAAAAAAAAAAAAAAAAAA created_on_primary_for_all_pdbs
SYS 576196403
AWjlhmj0cE/nv4FmnFJ6Gl8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA created_on_primary
SYS 576196403






The query results above show that we have generated two keys.
Each is identified by its key_id and also by the user-created tag.

Step #6 - Copy the primary wallet file ewallet.p12 to a standby  location

For example:
scp /u01/app/oracle/oradata/wallets/ewallet.p12 standby:/home/oracle
oracle@standby's password:
ewallet.p12 100% 5760 5.6KB/s 00:00


Above, the wallet file ewallet.p12 is copied from primary host to the standby host, using unix secure copy.

Step #7 - On standby, create the empty keystore using ADMINSTER..CREATE
KEYSTORE

Caution :
Before attempting this, the sqlnet.ora and keystore location itself should be configured correctly on the standby. For example, this time, run the create keystore command on the standby, ensuring that the keystoref older exists and sqlnet.ora is updated.


SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/oradata/wallets/cdb2'
2 IDENTIFIED BY oracle;

Step #8 - On the standby, do a keystore merge - merge the copy of the primary
keystore with the empty keystore on the standby

This step is critical. For a physical standby, a keystore merge is required. The merge operation, in this specific case, essentially copies the keys from the primary keystore file to the standby keystore file.
Caution:
The standby keystore should be explicitly re-opened after the merge in order that the imported keys become available on the standby. However, the target keystore need not be open for the merge. (See step 9 below)

In the sql below the source keystore is the copied file /home/oracle/ewallet.p12. The target keystore is the keystore which was created in step 6. Note that the command requires the user to specify the location of the source and destination keystores, and also requires the passwords to the respective keystores to be specified on the command line.

SQL> administer key management merge keystore '/home/oracle'
identified by oracle
into existing keystore '/u01/app/oracle/oradata/wallets'
identified by oracle
with backup using 'keystore_merge_bkp';
2 3 4 5
keystore altered.

Step #9 - After merge, explicitly re-open the keystore (on the standby)

This operation is required after a merge - and we use container=all so that the keys can be used in any container.

SQL> administer key management set keystore close identified by oracle container=all;
SQL> administer key management set keystore open identified by oracle container=all;

Stage 2: Demonstrate TDE operations

Step # 1 Open the standby in active data guard mode

The cryptography is now set up as required between the two databases. Now we can prepare to test TDE operations in Active Data Guard mode.

Below, we stop managed recovery on the standby, open the database as well as the pdb of interest in
read-only mode, and restart media recovery again so it can synchronize with the changes on primary in real-time. Note that PDBS always have to be explicitly opened and will not implicitly open when the CBD it started.


SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only;
Database altered.
SQL> alter pluggable database hrpdb open read only;
Pluggable database altered.
SQL> recover managed standby database

Step # 2 - If not already done, on the standby, open keystore in all containers


Normally, the key store would have been re-opened in step 8 above already after the merge operation

Step #3 - Create a table with an encrypted column on primary

We connect to the to the HRPDB pluggable database as hradmin user.
You can connect to a specific container of a pluggable database by specifying the PDB-specific service name in the SERVICE_NAME attribute in the tnsnames connect string.
The hradmin user is local user who exists in the HRPDB pluggable database, with storage quota on the USERS tablespace, which is also his default tablespace in the pdb. We create a test table named employee, it will have just two columns, an employee name and an encrypted column for storing his Social Security number. We also insert a couple of test emplyee records and commit.


SQL> create table employee ( name varchar(50), ssn varchar(50) encrypt using 'AES192');
Table created.
SQL> insert into hradmin.employee values ('emp1','123-456-001');
1 row created.
SQL> insert into hradmin.employee values('emp2','123-456-002');
1 row created.
SQL> commit;

Step # 4 - Check that you are able to query the newly created data on the standby


Create a tnsnames connect string on the standby which points to the standby hrpdb service.
Connect from sqlplus to the hrpdb container and query the table. We are able to see the data in plaintext on the standby due to TDE.

SQL> select * from employee;
NAME SSN
-------------------- --------------------
emp1 123-456-001
emp2 123-456-002

Step 5 # Verify that you cannot view the encrypted data with the keystore closed.

This is a kind of verification to make sure the data was indeed viewable in Step 10 due to TDE. On the standby, we will explicitly close the keystore and req-qury the table to make sure that the data is not accessible when TDE is deactivated. Log on as sysdba to the standby and close the key store in the hrpdb container

SQL> alter session set container=hrpdb;
Session altered.
SQL> administer key management set keystore close identified by oracle;
keystore altered.

Now, we connect as hradmin user on standby and query the table.
Note the ez-connect syntax used here to connect to a specific service in a pluggable database.

SQL> conn hradmin/xxx@localhost:1521/hrpdb.example.com
Connected.
SQL> select * from hradmin.employee;
select * from hradmin.employee
*
ERROR at line 1:
ORA-28365: wallet is not open



Conclusion:

Oracle Database 12c has new syntax for cryptographic key management, a group of ADMINISTER KEY MANAGEMENT commands. Above, we have seen the use of this group of commands to create a keystore, generate and activate keys.

We have seen one example of how keys can be transported from one site and made available at anotherto support operational needs.

We have seen how TDE can be configured to work in an Active Data Guard environment, so that a user or application can query and access protected data on a standby, even as its being modified on the primary.

Similar key management operations are also required when working with TDE-enabled
tables/tablespaces and doing database recovery, export/import, tablespace point-in-time recovery, etc.

Because TDE operations implicitly perform the cryptography, its important for the correct keys to be configured beforehand, in the environment which may require a TDE operation.

Monday 5 May 2014

Adaptive query optimization - SQL execution enhancement in Oracle Database 12c

Oracle Database 12c has ability to automatically detect when an SQL plan performed sub-optimally, and make corrections for subsequent invocations. This behavior is part of its adaptive query optimization feature set, which includes adaptive plans, adaptive statistics, and sql plan directives,

Here, we discuss one aspect of adaptive statistics, which is known as adaptive re-optimization.

Following the initial execution of a query, Oracle analyzes run-time metrics like actual volume and quality of data present in the tables, and compares it against the assumptions/estimates made at the time of initial plan generation. If any significant differences are found, it will attempt to make corrections by marking the query as re-optimizable.

Re-optimizable means, for the next invocation it will not re-use the original cursor (and plan).
Instead, it will use the execution information collected previously, and if necessary, put the sql through a re-optimization process, generating a different, and a potentially better performing plan.

This process can repeat over multiple times, with Oracle making adjustments to the plan as it learns more about the nature of the data in the database from each execution. This feature is known as adaptive re-optimization. Following such a re-optimization, Oracle Database 12c creates Sql Plan Directives in the data dictionary, which are the results of its learnings about the data from previous executions, and are the potential input to future invocations or future statistics gathering efforts.


We will see a small example of adaptive re-optimization occurring on in 12c, due to a table which has had its optimizer statistics set to incorrect values on purpose.

Scenario
Container db
SALESPDB
Demo table schema/owner
SALES.SALES


Step # 1. First, create a table called sales in the sales schema of the SALESPDB container.

Make sure statistics_level is set to ALL.


[oracle@laboms ~]$ sqlplus sales/sales@localhost/salespdb

SQL*Plus: Release 12.1.0.1.0 Production on Mon May 5 17:58:47 2014

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

Last Successful login time: Mon May 05 2014 17:40:56 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options



SQL> alter session set statistics_level='ALL';

Session altered.

SQL> show con_name

CON_NAME
------------------------------
SALESPDB

SQL > create table sales ( time_id date, region_id number(3), amt number(5));
       
Table created.




Step # 2. Populate sales table with some test data. We create about 50000 rows.


SQL> insert into sales
2   select to_date('01012000','ddmmyyyy') + rownum, 1, 100 from dual
3   connect by level<=50000;
              
50000 rows created
SQL > commit;
              
Commit complete.

Step # 3. Set the table statistics per to some incorrect values.
Run the following pl/sql block to set the number of rows to 1 million.


begin
        dbms_stats.set_table_stats(ownname=>'SALES',tabname=>'SALES',
           numrows=>1000000);
end;
/

Step # 6. Issue this query in the SALES schema:
This query lists the table contents for a date range. In this case, about 1946 rows are returned.


select * from sales where region_id in (1,2) and time_id between
to_date('01012014','ddmmyyyy') and to_date('30042019','ddmmyyyy');

TIME_ID    REGION_ID        AMT
--------- ---------- ----------
20-OCT-18          1        100
21-OCT-18          1        100
22-OCT-18          1        100
23-OCT-18          1        100
24-OCT-18          1        100
25-OCT-18          1        100
26-OCT-18          1        100
27-OCT-18          1        100
28-OCT-18          1        100
29-OCT-18          1        100

1946 rows selected.

Step # 7.  Check the plan generated.

You may need to start another command shell window and connect to the database as sysdba to query v$sql. Determine the sql id of the above sql using v$sql view. It was '978mxpdcdrnrm'. In the DBMS_XPLAN call, the 3rd parameter is set to +ALLSTATS which enable reporting of sql plan statistics. Note that it its estimate is inaccurate (projected 25 rows v/s 1946 rows actual).

1* select * from table(dbms_xplan.display_cursor('978mxpdcdrnrm',0,'+ALLSTATS'))
SQL> /
SQL_ID  978mxpdcdrnrm, child number 0
-------------------------------------
select * from sales where region_id in (1,2) and time_id between
to_date('01012014','ddmmyyyy') and to_date('30042019','ddmmyyyy')

Plan hash value: 781590677

-------------------------------------------------------------------------------------
| Id  | Operation         | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |      1 |        |   1946 |00:00:00.02 |     337 |
|*  1 |  TABLE ACCESS FULL| SALES |      1 |     25 |   1946 |00:00:00.02 |     337 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter((INTERNAL_FUNCTION("REGION_ID") AND "TIME_ID">=TO_DATE('
              2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TIME_ID"<=TO_DATE('
              2019-04-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))


21 rows selected.                             
                                   
During execution of the SQL, the optimizer encountered cardinality mis-match due to the large number of rows hard-coded into the optimizer stats. Due to the statistics feedback mechanism, the optimizer would have also created a number of  sql plan directives against this table. Let's see next, the evidence of plan directive creation

Step # 8. Force a write of of any generated sql plan directives to disk.
This is done so that  it can be queried later from SqlPlus.
Note:
  1. We are doing this as sysdba, user sales does not have required privileges.
  2. We have to make sure to connect to the container which contains the SALES table. Note that some performance metadata are stored in the pluggable database and not in the container.
           

1  begin
2   dbms_spd.flush_sql_plan_directive();
3* end;
SQL> /

PL/SQL procedure successfully completed.

Step # 9. Re-execute the query in the original session and check v$sql. 

           
Checking v$sql after repeat execution, we include the IS_REOPTIMIZABLE column, it indicates which SQL are subject to re-optimization due to adaptive statistics feature.

select sql_id, child_number, is_reoptimizable, sql_text from v$sql where  sql_id='978mxpdcdrnrm'

SQL_ID          CHILD_NB SQL_TEXT                                                   IS_REOPTIMIZABLE
------------- -------- ------------------------------------------------------------ ----------------
978mxpdcdrnrm        0 select * from sales where region_id in (1,2) and time_id bet Y
                       ween to_date('01012014','ddmmyyyy') and to_date('30042019','
                       ddmmyyyy')

978mxpdcdrnrm        1 select * from sales where region_id in (1,2) and time_id bet N
                       ween to_date('01012014','ddmmyyyy') and to_date('30042019','

                       ddmmyyyy')

There are 2 cursors for the sql and one of them is marked as re-optimizable (third column, its shown as ‘Y’). That would be the sql from the original invocation (child_number=0), which hit the incorrect stats condition.

Examining the plan for the new cursor, having child_number=1, (this is no longer re-optimizable, is_reoptimizable='N for this one) we get

1* select * from table(dbms_xplan.display_cursor('978mxpdcdrnrm',1,'+ALLSTATS'))
SQL> /
SQL_ID  978mxpdcdrnrm, child number 1
-------------------------------------
select * from sales where region_id in (1,2) and time_id between
to_date('01012014','ddmmyyyy') and to_date('30042019','ddmmyyyy')

Plan hash value: 781590677

-------------------------------------------------------------------------------------
| Id  | Operation         | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |      1 |        |   1946 |00:00:00.02 |     333 |
|*  1 |  TABLE ACCESS FULL| SALES |      1 |   1946 |   1946 |00:00:00.02 |     333 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter((INTERNAL_FUNCTION("REGION_ID") AND "TIME_ID">=TO_DATE('
              2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TIME_ID"<=TO_DATE('
              2019-04-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

Note
-----
   - statistics feedback used for this statement


25 rows selected.
                                         

The Note section has a remark that statistics feedback was used for this plan. The optimizer detected the bad statistics during first execution, and marked the original sql as re-optimizable. During the second invocation, the optimizer would have done a dynamic sampling of the data instead of relying on the statistics.

Looking at the plan, now, the estimated number of rows returned is now accurate (1946 v/s 25 before).

Further, an SQL plan directive was also created. Let us see what the SQL Plan directive asked the optimizer to do.

Below, we will query the directives created, to see what they instruct the optimizer
Run the following script as sysdba:

column owner format a10
column object_name format a15
column reason format a40 wrapped
column type format a20
set lines 300

SELECT  o.OWNER, o.OBJECT_NAME,
       d.TYPE, d.REASON
FROM   DBA_SQL_PLAN_DIRECTIVES d, DBA_SQL_PLAN_DIR_OBJECTS o
WHERE  d.DIRECTIVE_ID=o.DIRECTIVE_ID
AND    o.OWNER IN ('SALES');

Note:
The database connection is made to the pluggable database, not the CDB. Because, in the query, we are referring to the DBA_* views and not the CDB_* views. If connected to the container CDB$ROOT, you’d have to query the CDB_SQL_PLAN_* views.
             

[oracle@laboms ~]$ sqlplus sys/oracle@localhost/salespdb as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon May 5 17:41:40 2014

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> column owner format a10
column object_name format a15
column reason format a40 wrapped
column type format a20
set lines 300

SELECT  o.OWNER, o.OBJECT_NAME,
       d.TYPE, d.REASON
FROM   DBA_SQL_PLAN_DIRECTIVES d, DBA_SQL_PLAN_DIR_OBJECTS o
WHERE  d.DIRECTIVE_ID=o.DIRECTIVE_ID
AND    o.OWNER IN ('SALES')SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5  ;

OWNER      OBJECT_NAME     TYPE                 REASON
---------- --------------- -------------------- ----------------------------------------
SALES      SALES           DYNAMIC_SAMPLING     SINGLE TABLE CARDINALITY MISESTIMATE
SALES      SALES           DYNAMIC_SAMPLING     SINGLE TABLE CARDINALITY MISESTIMATE
SALES      SALES           DYNAMIC_SAMPLING     SINGLE TABLE CARDINALITY MISESTIMATE

SQL>

The optimizer has created a number of Sql Plan directives, which instructs that dynamic sampling should be done on the sales table due to cardinality mis-estimate. This causes the optimizer to ignore the inaccurate statistics stored in the dictionary and sample the data on the fly, which as we saw, resulted in a more accurate row count estimate (though not a modified plan). Sql Plans and access methods may have also changed (it didnt in this case), bringing about a noticeable improvement in later executions.

Oracle has attempted to recover from the inaccurate statistics condition in the dictionary, and do a dynamic sampling for 2nd execution. Oracle 12c is thus able to generate adaptive sql plans by statistics feedback.

If you want to off this behavior, set OPTIMIZER_ADAPTIVE_REPORTING_ONLY=TRUE. Setting it to TRUE still lets internal feedback reporting will take place, but Oracle will not generate and use new plans based on the reporting.