Exadata and iPhone

I never thought one day I will find a similarity between Oracle Exadata and iPhone. But it was before Oracle announced X4-2C, the colorful Exadata:)

X4 2C


However, I don't think that X4-2C is cheaper as iPhone 5C is, since it's a limited edition...

Data Redaction in Oracle 12c and 11gR2

Data Redaction is one of the new features of 12c actually, and also it's become available in 11gR2 with 11.2.0.4. Data Redaction is in Advanced Security option of enterprise edition.

What Data Redaction does is basically masking the data on the fly based on the type and expression given. Let's do a demonstration:

First thing to do is to create a policy. Policies are created on tables and a table can have only one policy. If you try to add a policy to table with a policy, you get ORA-28069. Policy can be defined for only a single column at a time but you can add columns to the policy by altering it.

begin
    dbms_redact.add_policy (
        object_schema       => 'DEMO',
        object_name         => 'CUSTOMERS',
        column_name         => 'CNAME',        
        policy_name         => 'customers_pol',
        function_type       => DBMS_REDACT.REGEXP,
        regexp_pattern      => '(\S{3})(\S+)',
        regexp_replace_string   => '\1***',  
        expression          => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''SYS'''
    );
end;
/

Above command creates a policy on table CUSTOMERS in schema DEMO and it masks column CNAME by using regular expressions. Masked values will contain first 3 letters of the name (each name if customer has a middle name) and 3 "*"s.

There are 6 types of redaction; full, partial, regexp, random and none. You can find detail information in Oracle documentation. Please not the expression parameter. What we're saying here is apply this policy to users who are not sys. By default polices applied against users except sys and object owner. So if you also want table owner gets masked values or you want to redact data based on application, such an expression must be used. Expression is a mandatory parameter, you can set it as '1=1' if you have no rule to apply.

begin
    dbms_redact.alter_policy (
        object_schema       => 'DEMO',
        object_name         => 'CUSTOMERS',        
        policy_name         => 'customers_pol',
        action              => DBMS_REDACT.ADD_COLUMN,
        column_name         => 'DOB',
        function_type       => DBMS_REDACT.PARTIAL,
        function_parameters => DBMS_REDACT.REDACT_DATE_EPOCH,
        expression          => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''SYS'''        
    );
end;
/

By altering the policy we've created, we added another column of the table to be masked. Here we used built-in redaction function. This function set all date values to 01-Jan-1970. There are couple of more pre-defined functions such for SSN, e-mail zip code.

begin
    dbms_redact.alter_policy (
        object_schema       => 'DEMO',
        object_name         => 'CUSTOMERS',        
        policy_name         => 'customers_pol',
        action              => DBMS_REDACT.ADD_COLUMN,
        column_name         => 'CID',
        function_type       => DBMS_REDACT.FULL, 
        expression          => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''SYS'''        
    );
end;
/

This time we add CID column to the policy and redacted it fully. So masked values will be displayed as 0. To change a columns redaction type, alter policy again by setting altering action to MODIFY_COLUMN:

begin
    dbms_redact.alter_policy (
        object_schema       => 'DEMO',
        object_name         => 'CUSTOMERS',        
        policy_name         => 'customers_pol',
        action              => DBMS_REDACT.MODIFY_COLUMN,
        column_name         => 'CID',
        function_type       => DBMS_REDACT.RANDOM, 
        expression          => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''SYS'''        
    );
end;
/

Now, we set it to use random masking, random values will be generated for the column. Redacted query output is as below at the end:

SQL> select cid, cname, dob from demo.customers;

CID         CNAME DOB
-----------------------------------------
1311680984   ABD*** AYD***   01-JAN-70
102691765    AHM*** YIL*** 01-JAN-70
819107024  ARI*** DEM***   01-JAN-70
7285271581  AYD*** TUR***   01-JAN-70
22688323660  AZM*** SEV***   01-JAN-70
7508336149  HAC*** ELM***   01-JAN-70
46158355970  HAL*** HAT***   01-JAN-70

Finally, if you need to drop a policy:

begin
    dbms_redact.drop_policy(
        object_schema     => 'DEMO',
        object_name       => 'CUSTOMERS',
        policy_name       => 'customers_pol'
    );        
end;
/

Data redaction is not a complicated way of defining security policies however what I see as a downside is one-to-one relationship between columns and policies: It is not allowed to add another policy/expression for a column, you get ORA-28060 error. So what you need is to create a rule base which has different masking types for different roles on the very same table, you need another tool. Otherwise you have practical data masking tool.

SQL Developer Hints to Format Output

SQL Developer may not be the perfect IDE, but it doesn't mean that it has not got any tricks at all. One of those features I'd like mention about can ease data transfer. SQL Developer excepts hints to format output. Here are the outputs for some of those hints; csv, loader, fixed and insert. As names implie; CSV gives rows with columns separated by commas, loader gives data formatted for SQL Loader, fixed returns columns separated by fixed spaces and insert creates insert commands.

select /*csv*/ * from scott.dept;
"DEPTNO","DNAME","LOC"
10,"ACCOUNTING","NEW YORK"
20,"RESEARCH","DALLAS"
30,"SALES","CHICAGO"
40,"OPERATIONS","BOSTON"

select /*loader*/ * from scott.dept;
10|"ACCOUNTING"|"NEW YORK"|
20|"RESEARCH"|"DALLAS"|
30|"SALES"|"CHICAGO"|
40|"OPERATIONS"|"BOSTON"|

select /*fixed*/ * from scott.dept;
"DEPTNO"    "DNAME"           "LOC"                        
"10"        "ACCOUNTING"      "NEW YORK"                   
"20"        "RESEARCH"        "DALLAS"                     
"30"        "SALES"           "CHICAGO"                    
"40"        "OPERATIONS"      "BOSTON"                      

select /*insert*/ * from scott.dept;
REM INSERTING into scott.dept
SET DEFINE OFF;
Insert into scott.dept (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
Insert into scott.dept (DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');
Insert into scott.dept (DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');
Insert into scott.dept (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');

It's also possible to get output in html or xml format which can really be handy for a quick report. As you can see below, html format includes even a search box to make searches in content.


Please note that hints are used in lower case and no space left between the hint and *s.

BEA-337 Incidents on OMS 12c

Lately I noticed that free disk space on my 12c Cloud Control server was reducing for some amount. When I investigated, I've figured it out that there were daily incidents of BEA-337 errors with around 40 MB of log  files in /u01/app/Oracle/Middleware/oms12c/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/adr/diag /ofm/GCDomain/EMGC_OMS1/incident directory.

Problem Key: BEA-337 [WebLogicServer]
Error Message Id: BEA-337

Description
-----------
Incident detected using watch rule "StuckThread":
Watch ServerName:       EMGC_OMS1
Watch RuleType:         Log
Watch Rule:             (SEVERITY = 'Error') AND ((MSGID = 'WL-000337') OR (MSGID = 'BEA-000337'))
Watch DomainName:       GCDomain
Watch Data:
   SERVER : EMGC_OMS1
   MESSAGE : [STUCK] ExecuteThread: '7' for queue: 'weblogic.kernel.Default (self-tuning)' has been busy for "611" seconds working on the request "weblogic.servlet.internal.ServletRequestImpl@70733a9e[
POST /em/websvcs/emws/ConsoleJobStepExecutorService HTTP/1.1

As you can see, incidents was caused due to a stuck thread watch rule. It was triggered because of "Refresh From My Oracle Support" job which is running more than 600 seconds. First thing I tried was rescheduling the job. From OEM, I navigated to jobs and found the job through advanced search by selecting ”Refresh From My Oracle Support” from the job type drop down and rescheduling it to some less busy time.

However it didn't work out the problem. So I decided to increase the parameter controlling stuck thread watch rule which is StuckThreadMaxTime. It's default value is 600 (seconds) and my job takes 13.5 minutes to complete. 

To alter the parameter:

1. Log on to WebLogic administration console through EMGC_OMS1 target on OMS or directly navigating to https://<host>:7200/console 

2. Click on EMGC_OMS1 under GCDomain->Environment -> Servers. Find "Stuck Thread Max Time" setting on Configuration/Tuning tab. I've increased it to 1200 seconds to cover job duration.


3. Save the configuration and then restart EMGC_OMS1 server from WebLogic administration console or from server console.

This workaround did the job.

Is Database 12c Supported on Exadata?

Answer is yes, even you can see 12c is listed under supported versions in document [888828.1].

However, there is a couple of buts (you should see this coming). Since latest  Exadata Storage Software (version 11.2.3.2.1) does not have new offload libraries, smart scan offload filtering and storage indexes become disabled with 12c. Smart scans are initiated but nodes get back blocks instead of rows and columns in the projection list. Also IORM plans are not enforced and inter-database plans are disabled on cells. And finally, cell metrics display 12c databases under OTHER_DATABASE.

So, it seems better to wait until next Exadata update and probably 12c patch set update to upgrade your Exadata. At least, to be able upgrade to a fully functional state. Meanwhile, you'd better upgrade to 11.2.0.3 if you haven't yet because upgrading from 11.2.0.1 is not supported.

Installing Single Instance Oracle 12c Database on Linux

As you are all probably aware, Oracle released new database version named 12c a few days ago, on 25th of June to be exact. It's the time of reading and researching new features, testing and developing. So I'm starting with this entry and installing a single instance. I intend to keep writing as I try more complex installations and upgrades.

OK, lets's start. I'll install a single instance 12c database on Oracle Linux 6.4 64-bit. As storage, NAS will be used, so no ASM therefore no GI will be installed. First step after the Linux installation is completing operating system configuration. Easiest way of achieving this is installing pre-install package. In 12c documentation it's said to run the command below as root to install package:

$ yum install oracle-rdbms-server-12cR1-preinstall

However mentioned package cannot be found on ULN. Instead install previous version's pre-install package. It does the job as well:

$ yum install oracle-rdbms-server-11gR2-preinstall

Create installation directory and grant required permissions:

$ mkdir /u01
$ chown oracle:oinstall /u01
$ chmod 775 /u01

As oracle, which is created by pre-install package, run the installer from the directory where you unzipped two installation zip files:

$ ./runInstaller

Graphical installation starts:


Skip updates


You can choose to create a database right after software installation or you can choose to install software first then run DBCA to create the database.


Select system class:


Select GI option:


I choose to follow advanced installation path to see what is there:


Select language(s):


Select database edition to install:


Specify installation paths:


Specify inventory directory:


Select database type:


Specify database name. As a new feature, you can choose to create a multi-tenant database.


Configure database by specifying memory options, character set and sample schema installation:


Specify storage, I set the path of mount point of NAS disk:


Specify Cloud Control details if you'll manage the instance through it:


Choose to use FRA and set path -or ASM- for FRA:


Set passwords for built-in users:


Set system groups:


Investigate summary and if there's anything you need to chance:


Installation starts. Run the root scripts when prompted:


12c DBCA

If you choose to install software only during installation or installation failed to create the database - as I've experienced - you can run database configuration assistant (DBCA) which is located under ORACLE_HOME/bin:

$ cd /u01/app/oracle/product/12.1.0/dbhome_1/bin
$ ./dbca

Graphical installation starts:


I choose to follow advanced installation path to see what is there:


Select the template of database type:


Set database names including container database:


Choose to configure database with EM Database Express (previous version's dbconsole) or with Cloud Control:


Set passwords of built-in user:


Set listener configuration, leave it as default:


Set storage specifications:


Set database options:


Set initialization parameters for memory management, number of Oracle processes, character set and connection mode:






After pre-requisite check installation starts. When finished, necessary information is displayed:


Enjoy and discover your first 12c database...

Performance Improvement for ODP.Net OracleDataAdapter

Recently I faced with a performance complaint for a query. Query was simple as it could be, just a "select * from some_table". When I digged out the problem with my fellow developer, we discovered that almost all time was spent while the data adapter fills the data table. Table had a little more than 155K rows and was size of 31 MB. Yet, it took more than 5 minutes to complete for data adapter to fill data table.

The cause of the problem was the number of round trips client need to accomplish to get all the rows from database. If you can reduce the number of round trips, you can increase the fetch size so that in each turn command object will fetch more rows from database. Here is how it's impletemented:

using (OracleConnection conn = new OracleConnection())
{
     OracleCommand comm = new OracleCommand();
     comm.Connection = conn;
     comm.FetchSize = comm.FetchSize * 8;
     comm.CommandText = "select * from some_table";

     try
     {
          conn.Open();
          OracleDataAdapter adap = new OracleDataAdapter(comm);
          System.Data.DataTable dt = new System.Data.DataTable();
          adap.Fill(dt);
     }
     finally
     {
          conn.Close();
     }
}

Notice the line with blue font, fetch size of command object increased by 8 times its default which is 128 KB at ODP.Net 11.2.0.3. OracleDataReader also has the FetchSize property. By increasing fetch size, you increase the cache size in memory to fetch rows.

What we gained is up to 96% performance improvement. Here are some timings with different fetch sizes:

Fetch SizeTiming (MI:SS.FF3)
Default (128 KB)
05:20.290
Default x 8 (1 MB)
00:52.941
Default x 32 (4 MB)
00:26.008
Default x 64 (8 MB)
00:12.409

It's a easy way to improve your application's performance, isn't it.