Oracle 12c In Memory Option – Overview

/* This is the translation of my article in Italian you can find here on the ITalian Oracle User Group web site */

The version 12.1.0.2.0 Oracle RDBMS introduced this new feature based on a data approach different from that we have become accustomed in recent years. The option name suggests that it is an optimization that introduces, trivializing, an advanced data caching. What the name does not show is how much advanced is this specialized caching.

Columnar Databases

Most relational databases on the market, including Oracle, store and manage data by row (tuples for those who are still attached to the terms of the relational model *). Let’s take a classic example: the STUDENT table (with a vision already Oracle oriented, ROWID)

Tab1

The storage of these data takes place per line, so we could represent as

(1: 1001,Rossi,Mario, Ingegneria,Roma1) (2: 1002,Bianchi,Giacomo, Ingegneria,Roma1) (3: 1003,Verdi,Antonio, Ingegneria,Roma1) (4: 1004,Marroni,Giacomo, Ingegneria,Roma2) (5: 1005,Verdi,Nicola, Informatica,Roma3)

From many years on, however, for specialized uses are widely adopted columnar databases, which store data by column.

(1001: 1; 1002: 2; 1003: 3; 1004: 4; 1005: 5) (Rossi: 1; Bianchi: 2; Verdi: 3; Marroni: 4; Verdi: 5) (Mario: 1; Giacomo: 2; Antonio: 3; Giacomo: 4; Nicola: 5) (Ingegneria: 1; Ingegneria: 2; Ingegneria: 3; Ingegneria: 4; Informatica: 5) (Roma1: 1; Roma1: 2; Roma2: 3; Roma2: 4; Roma3: 5)

Looking at the content, a single column seems almost an index, except that in this case there is no the table with rows!

We can note that in storage for column we repeated values and therefore is possible to have a compressed format (and sort that never hurts); the table can be rewritten as

(1001: 1; 1002: 2; 1003: 3; 1004: 4; 1005: 5) (Bianchi: 2; Marroni: 4; Rossi: 1; Verdi: 3,5) (Antonio: 3; Giacomo: 2,4; Mario: 1; Nicola: 5) (Ingegneria: 1,2,3,4; Informatica: 5) (Roma1: 1,2; Roma2: 3,4; Roma3: 5)

Graphically

Tab2

If you imagine having to build statistical indicators over UNIVERSITY column,  data of interest will be only

Col1

By decreasing the size it becomes less costly / expensive to keep the data in memory, and the table itself is already designed to be “sliced” vertically.

If we imagine a table with hundreds of fields (columns) we reduce the size of data to be analyzed to 1/100th and we can also compress the information!

All is not free though. When using columnar storage is usually more expensive handling data by row.

The two storage systems (by rows and by columns)are solutions addressing different needs.

If you think of an OLTP system (for example a classic CRM) that works with many or all of the data of customer’s row, you can imagine that as much is efficient the access to the “row” as much the system is able to withstand high workloads.

If you think of a statistical system (eg analysis of scientific or demographicsdata**) where, once the data is loaded, the calculations are based on columns, the efficient storage of the columns can give huge benefits.

The two worlds, however, are catching up much since often we need calculation of statistical indicators (data warehouse system) from the operational data (CRM) with minimal data latency (something like Zero Latency Enterprise is well known from the past millennium! ). The enormous potential of today hw may allow to meet this demand by implementing functionality in one system rather than integrating systems as quickly as possible.

In Memory Option meets this need by adding to row caching (database buffer cache in the SGA Oracle) a columnar caching (the Column Store) giving to system the capability to handle both types of workload.

After a brief overview of the architecture we will do some tests to see the option on field (virtual!).

In Memory Option Architecture

Let’s see how Oracle has interpreted this challenge in version 12c, making available in version 12.1.0.2.0 the In Memory Option.

SGA-BufferCache

We start from the traditional implementation of Buffer Cache Oracle. As is well known the area is used by server processes to load the oracle blocks needed from the disk and run the requested operation(s) in RAM (are the processes that run the SQL and PL / SQL and respond to our application process). The process background DBWR brings any changed blocks to disk.

SGA-BufferCache-InMemory

With the In Memory Option active in the SGA are allocated other two memory areas that are part of the In Memory Cache: In Memory Column Store (IMCS) where the data of interest are stored in columnar format and TX Journal that serves to keep IMCS aligned to changes in data. Some new backgroud processes (IMCO, SMCO and Wnnn) are in charge to load and maintain the IMCS.

The option can be enabled simply by setting the size of the In Memory Cache using parameter INMEMORY_SIZE. The IMCS is automatically used only for queries and only for objects for which it was configured. All without any impact on the application code, which makes it really attractive from a technical standpoint ***.

The tables (or columns of tables) which have activated the option are loaded to the IMCS with a priority configured during creation of the tables themselves, or subsequently with commands to alter.

To configure a table or part of it so as to be loaded in IMCS it must explicitly indicate for example with ab alter table command

ALTER TABLE SALES INMEMORY;

or partially

ALTER TABLE SALES INMEMORY (amount_sold);

The population of the IMCS with configured objects is done automatically by background processes. It is possible to handle priority with 5 levels (NONE, LOW, MEDIUM, HIGH, CRITICAL) which basically means to impose an order to the objects to be loaded.

It can also be enabled compression in a more or less effective by adding the keyword MEMCOMPRESS followed by level (DML, or QUERY QUERY LOW, HIGH QUERY, or CAPACITY CAPACITY LOW, HIGH CAPACITY). We do a complete example

ALTER TABLE SALES INMEMORY PRIORITY CRITICAL 
  INMEMORY MEMCOMPRESS FOR QUERY HIGH (amount_sold);

to disable IMCS for the table

ALTER TABLE SALES NO INMEMORY;

It can also be configured In Memory Option for materialized views (same parameters and modes of the tables) and add default In Memory configurations at tablespace level (that will be applied to all objects created later in the tablespace).

SGA-BufferCache-InMemory-update

We have said that no changes need to be made at application level and all will work exactly as before. In fact, when the client application requires an UPDATE, the server process that is in charge of execution will act exactly as before (loading data into the buffer cache if not present, use undo segments, etc etc). If the object has an In Memory configuration the transaction also maintains a TX Journal with the modify. Periodically and based on threshold levels, the changes contained in the TX Journal are merged to IMCS.

The objects configured as In Memory are easily verifiable using system views. There are the appropriate columns in the usual views USER_TABLES (and of course also ALL_TABLES, DBA_TABLES)

INMEMORY
INMEMORY_PRIORITY
INMEMORY_COMPRESSION
INMEMORY_DISTRIBUTE    -- solo per RAC
INMEMORY_DUPLICATE     -- solo per RAC in ambienti ingegnerizzati (Exadata)

and also some new V$ views

V$IM_COLUMN_LEVEL
V$IM_SEGMENTS
V$IM_USER_SEGMENTS
V$INMEMORY_AREA

SGA-BufferCache-InMemory-select

At query time, the server process that is in charge of the execution is able to determine whether or not to use IMCS, and is able to reconstruct the modified part through the TX Journal and also to draw the classic mechanism of Buffer Cache if the information is not all in IMCS. Then it is able to simultaneously exploit both cache to get the best possible outcome in the current situation!

Enabling In Memory Option and first test

To do some test you can install a VM with version 12.1.0.2.0 downloading it from the Oracle website for test / teaching purposes. In the installation also select the sample schemas.

Starting from the data in the sample schema SH, We need to create a table (SALESIMO) with a size of at least 0.5GB to do some tests.

SQL> create table salesimo tablespace testimo as select * from sh.sales;
SQL> insert into salesimo select * from salesimo;
SQL> insert into salesimo select * from salesimo;
SQL> insert into salesimo select * from salesimo;
SQL> insert into salesimo select * from salesimo;
SQL> commit;

SQL> select owner, segment_type, segment_name, sum(bytes)/(1024*1024) MB, count(*) part 
 2   from dba_segments where tablespace_name='TESTIMO'
 3   group by owner, segment_type, segment_name;

OWNER        SEGMENT_TYPE       SEGMENT_NAME       MB   PART
------------ ------------------ ------------------ ---- ------
SYS          TABLE              SALESIMO           560  1

SQL> select count(*) from salesimo;
 COUNT(*)
----------
 14701488

We check the current size of the SGA;

SQL> show sga
Total System Global Area 3221225472 bytes
Fixed Size		    2929552 bytes
Variable Size		 1778388080 bytes
Database Buffers	 1426063360 bytes
Redo Buffers		   13844480 bytes

In the Memory Cache is disabled. We can see how the default parameters setting

SQL> show parameter inmemory
NAME                                          TYPE          VALUE
--------------------------------------------- ------------- ---------
inmemory_clause_default                       string        
inmemory_force                                string        DEFAULT
inmemory_max_populate_servers                 integer       0
inmemory_query                                string        ENABLE
inmemory_size                                 big integer   0
inmemory_trickle_repopulate_servers_percent   integer       1
optimizer_inmemory_aware                      boolean       TRUE

Thinking on In Memory Option the first question that comes is that you can simply increase the size of the buffer cache to get the same effect. The question is legitimate, but of course, for what we have already said, the two caches operate in a substantially different way.

To prove it will perform some queries and act with the RAM comparing executions in Buffer Cache with those in IMCS.

We prepare the instance so that it has enough memory to perform both caching

SQL> alter system set memory_target=2500m scope=spfile;
SQL> alter system set inmemory_size=800m scope=spfile;
SQL> alter system set db_keep_cache_size=800m scope=spfile;

After instance restart we have

SQL> show sga
Total System Global Area 2634022912 bytes
Fixed Size		    2927864 bytes
Variable Size		  922747656 bytes
Database Buffers	  855638016 bytes
Redo Buffers		   13848576 bytes
In-Memory Area		  838860800 bytes

To force the caching table SALESIMO in Buffer Cache and IMCS we use the following commands

SQL> alter table salesimo cache;
SQL> alter table salesimo storage (buffer_pool keep);
SQL> alter table salesimo inmemory;
SQL> select count(*) from salesimo;

Without indexes on the table, the select is performed with a full table scan by loading the table in the Buffer Pool Keep that we have configured. The default priority by In Memory is NONE, ie the table is loaded from the first access, so the select statement also triggers the work of IMCO / SMCO / Wnnn.

Using one of the new V$ we can follow the loading process of SALESIMO

/ * The environment is Multitenant, to which must be added the filter con_id = 3 (the PDB we are working on); the 1MB pool is actually for the data, the 64KB pool is for metadata. * /

SQL> select pool, alloc_bytes, used_bytes, populate_status from v$inmemory_area where con_id=3;
POOL			   ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
1MB POOL		     670040064	  6291456 POPULATING
64KB POOL		     150994944	   262144 POPULATING

The new background processes are doing their job populating IMCS with data from SALESIMO. In about twenty seconds (on a poor VM) the situation is the following

SQL> select pool, alloc_bytes, used_bytes, populate_status from v$inmemory_area where con_id=3;
POOL			   ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
1MB POOL		     670040064	120586240 DONE
64KB POOL		     150994944	  1638400 DONE

and you can already see how SALESIMO, which is 560MB, is stored in IMCS with only 120MB without any compression option.

But as it occupies in the buffer cache? Considering blocking 8K calculation returns a nice 553MB!

SQL> select (8*count(*))/1024 MB
 2   from v$bh 
 3   where objd=(select data_object_id
 4               FROM DBA_OBJECTS 
 5               WHERE object_name = UPPER('SALESIMO')
 6              );

	MB
----------
 553.09375

So even with the same performance, the option In Memory allows me to better exploit the available RAM with an occupation estimated equal to about 20% of that required with the traditional Buffer Cache. This without changing storage parameters and without any changes to the application code.

Let’s see how it behaves in terms of performance, although with a VM, without vector processor, less RAM, small table (only 560MB), the results are unlikely to be evident in terms of time. But we can check the different execution plans and, using statistics extensively, assess the different resource requirements of the plans.

alter session set statistics_level=ALL;

to be able to analyze the access plans and related statistics with

select * from table(dbms_xplan.display_cursor(format=>'TYPICAL +allstats last'));

With alter session commands will guide the optimizer to use / not use the IMCS

alter session set INMEMORY_QUERY=ENABLE;

and

alter session set INMEMORY_QUERY=DISABLE;

We can also monitor the use (or non-use) of the diskrunning before and after the test query a  read statistics report

SQL> SELECT sn.name,ms.value
 2   FROM V$MYSTAT ms, V$STATNAME sn
 3   WHERE ms.STATISTIC#=sn.STATISTIC#
 4   and sn.name in (
 5   'session logical reads',
 6   'physical reads',
 7   'physical reads cache',
 8   'physical reads direct',
 9   'physical reads cache prefetch'
 10  );

and found that there are only logical reads (session logical reads) and no changes to the physical (physical reads%).

Case 1) Count lines with *

SQL> select count(*) from salesimo;

COUNT(*)
----------
  14701488

In Buffer Cache (pool Keep)

Elapsed: 00:00:00.30
14:05:27 SQL> select * from table(dbms_xplan.display_cursor(format=>'TYPICAL +allstats'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |      1 |        | 19399 (100)|      1 |   70812 |
|   1 |  SORT AGGREGATE    |          |      1 |      1 |            |      1 |   70812 |
|   2 |   TABLE ACCESS FULL| SALESIMO |      1 |     14M| 19399   (1)|     14M|   70812 |
-----------------------------------------------------------------------------------------

In IMCS

Elapsed: 00:00:00.19

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |   760 (100)|      1 |      10 |
|   1 |  SORT AGGREGATE             |          |      1 |      1 |            |      1 |      10 |
|   2 |   TABLE ACCESS INMEMORY FULL| SALESIMO |      1 |     14M|   760   (2)|     14M|      10 |
--------------------------------------------------------------------------------------------------

Although numerically version IMCS has “saved” a third of the time, it is difficult to draw conclusions only with the times. Let’s look at the column buffers that indicates the number of buffers accessed from execution. We collect some other data and then do some consideration.

2) Aggregate simple

SQL> select prod_id, sum(amount_sold) from salesimo group by prod_id;
...
72 rows selected.

In Buffer Cache (pool Keep)

Elapsed: 00:00:03.08

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |      1 |        |       | 19824 (100)|     72 |   70812 |
|   1 |  HASH GROUP BY     |          |      1 |     14M|   350M| 19824   (3)|     72 |   70812 |
|   2 |   TABLE ACCESS FULL| SALESIMO |      1 |     14M|   350M| 19442   (1)|     14M|   70812 |
-------------------------------------------------------------------------------------------------

In IMCS

Elapsed: 00:00:01.38

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |       |  1215 (100)|     72 |      10 |
|   1 |  HASH GROUP BY              |          |      1 |     14M|   350M|  1215  (39)|     72 |      10 |
|   2 |   TABLE ACCESS INMEMORY FULL| SALESIMO |      1 |     14M|   350M|   832  (11)|     14M|      10 |
----------------------------------------------------------------------------------------------------------

In this case, the performance gap is most evident, although it remains difficult to assess the actual VM on a small advantage.

Let’s go a bit ‘in more detail. We perform operations on individual fields and add filter conditions.

3) Count the elements of a column

SQL> select count(prod_id) from salesimo;

COUNT(PROD_ID)
--------------
      14701488

In Buffer Cache (pool Keep)

Elapsed: 00:00:00.28

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |      1 |        | 19399 (100)|      1 |   70812 |
|   1 |  SORT AGGREGATE    |          |      1 |      1 |            |      1 |   70812 |
|   2 |   TABLE ACCESS FULL| SALESIMO |      1 |     14M| 19399   (1)|     14M|   70812 |
-----------------------------------------------------------------------------------------

With Index (subject however to normal Buffer Cache caching, query repeated several times to promote the mechanism)

Elapsed: 00:00:03.07

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |  8110 (100)|      1 |	 29778 |
|   1 |  SORT AGGREGATE       |      |      1 |      1 |            |      1 |	 29778 |
|   2 |   INDEX FAST FULL SCAN| I1   |      1 |     14M|  8110   (1)|     14M|	 29778 |
----------------------------------------------------------------------------------------

IMCS

Elapsed: 00:00:00.19

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |   760 (100)|      1 |      10 |
|   1 |  SORT AGGREGATE             |          |      1 |      1 |            |      1 |      10 |
|   2 |   TABLE ACCESS INMEMORY FULL| SALESIMO |      1 |     14M|   760   (2)|     14M|      10 |
--------------------------------------------------------------------------------------------------

4) Simple aggregate filter on the column and on the count aggregate

SQL> select prod_id, sum(amount_sold) 
 1   from salesimo 
 2   where prod_id between 132 and 143
 3   group by prod_id
 4   having count(*) > 20;
...

12 rows selected.

In Buffer Cache (pool Keep)

Elapsed: 00:00:01.00

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |      1 |        |       | 19451 (100)|     12 |   70812 |
|*  1 |  FILTER             |          |      1 |        |       |            |     12 |   70812 |
|   2 |   HASH GROUP BY     |          |      1 |   1215K|    30M| 19451   (1)|     12 |   70812 |
|*  3 |    TABLE ACCESS FULL| SALESIMO |      1 |   1215K|    30M| 19422   (1)|   1806K|   70812 |
--------------------------------------------------------------------------------------------------

Index (subject however to normal caching Buffer Cache, query reiterated several times to help the mechanism)

Elapsed: 00:00:01.92

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        |       | 18572 (100)|     12 |   26332 |
|*  1 |  FILTER                       |          |      1 |        |       |            |     12 |   26332 |
|   2 |   SORT GROUP BY NOSORT        |          |      1 |   1215K|    30M| 18572   (1)|     12 |   26332 |
|   3 |    TABLE ACCESS BY INDEX ROWID| SALESIMO |      1 |   1215K|    30M| 18572   (1)|   1806K|   26332 |
|*  4 |     INDEX RANGE SCAN          | I1       |      1 |   1215K|       |  2568   (1)|   1806K|    3783 |
------------------------------------------------------------------------------------------------------------

IMCS

Elapsed: 00:00:00.35

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |       |   878 (100)|     12 |      10 |
|*  1 |  FILTER                      |          |      1 |        |       |            |     12 |      10 |
|   2 |   HASH GROUP BY              |          |      1 |   1215K|    30M|   878  (15)|     12 |      10 |
|*  3 |    TABLE ACCESS INMEMORY FULL| SALESIMO |      1 |   1215K|    30M|   849  (12)|   1806K|      10 |
-----------------------------------------------------------------------------------------------------------

From the data collected

In summary, given the different size of the buffers (8k against 1M) and unifying the M

 Caso   |   1   |   2   |    3   |   4   |
Keep    | 566M  | 566M  |  566M  | 566M  |
Indice  |       |       |  238M  | 210M  |
IMCS    |  10M  |  10M  |   10M  |  10M  |

ignoring the times affected by the VM, the processor, the stages of sort and hash, if you look at the buffers beds to identify rows, the full table scan in Memory needs to read data an order of magnitude lower to meet this type of query

Conclusions

Ease of use, no impact on the application, compression of the storage space, flexibility of optimizer in using it even partially, all that make the option comes out the winner of this first analysis and it laying the foundation for adding some real time analysis capabilities to operational systems or considerably speeding complex phases of data analysis.

We will definitely do other tests on this option to explore other aspects omitted here as in memory storage indexes, evaluation of the different methods of compression and vectorial CPU utilization.

References

Oracle docs  http://www.oracle.com/technetwork/database/in-memory/documentation/index.html

Syntax  http://docs.oracle.com/database/121/SQLRF/statements_7002.htm#CEGBCAAE

A short video https://www.youtube.com/watch?v=fMW2-TDheec

Notes

* Here 2 wiki pages on modello relazionale and RDBMS

**Quote nonrandom ! Qui find a wiki on columnar database. In the historical notes found TAXIR and RAPID, the first examples of this approach just dedicated to different scientific and demographic.

***But beware, in terms of the license, as many of the new features of the 12c, has its additional cost rispettto license EE. Even Multitenant, ILM, Vault, etc have their additional cost.

Storage server 12.1.1

I saw on comments some problem with this release. I’m curious. And I must test something for an evolution on a project. The decision is take. Simulator 12c is starting.

I used my previous post as guide and it was quite simple. I only had one big issue, but it is not related with release or the guide itself. I used “-” and “_” in cell name !!

I spent a lot of time with strange errors because firstly I choose cell12-1 as machine name and cell name. When I created the cell with cellcli command I immediately had an error and it was clear that cellname was the problem.

So I changed cellname in cell12_1 (and hostname, …), the command worked with errors on flash’s configuration. From that point on only errors restarting services.

I stopped all, removed cell*rpm, changed all network configuration with cell12, restarted all, reinstalled cell*rpm and finally I get the cell up and running.

Here the steps (without the fight!)

All starts with a fresh install of a VirtualBox VM with Oracle Linux 5.9. The steps are the same as previous post, except for the ISO (from http://edelivery.oracle.com) and my new false InfiniBand network 192.168.50.xx (vboxnet3 host only adapter): cell12 take 192.168.50.10.

From eDelivery I need also cell software (V42777-01.zip). Into this huge zip, there is a tar file (cellImageMaker_12.1.1.1.0_LINUX.X64_131219-1.x86_64.tar).
Into the tar file I need to extract just 2 files:
– dl180/boot/cellbits/cell.bin
– dl180/boot/cellbits/cellrpms.tbz
cell.bin contains the cell software rpm and it needs to be extracted from a shell with unzip due a custom file header.
From cellrpms.tbz I need only jdk-1.7.0_25-fcs.x86_64.rpm

As usual I put all I need at install time outside my VM, on a shared folder through VirtualBox feature. In cell12 the shared folder is mounted on /media/sf_OS. From the host system I extracted only the 2 files I need to handle into cell12 (cell.bin and jdk-1.7.0_25-fcs.x86_64.rpm). Then

[root@cell12 ~]# cd /media/sf_OS/
[root@cell12 sf_OS]# ls
cell.bin  jdk-1.7.0_25-fcs.x86_64.rpm
[root@cell12 sf_OS]# unzip cell.bin
Archive:  cell.bin
warning [cell.bin]:  20118 extra bytes at beginning or within zipfile
(attempting to process anyway)
inflating: cell-12.1.1.1.0_LINUX.X64_131219-1.x86_64.rpm

Note: differently from 11.2 sw, here it isn’t jdk1.5, but we still need that and I already take from cellrpms.tbz.

This is /etc/host

[root@cell12 sf_OS]# more /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1       cell12        localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6

Now, following my guide, I create /var/log/oracle (I’m not sure it’s still needed, but I didn’t test)

[root@cell12 sf_OS]# mkdir /var/log/oracle
[root@cell12 sf_OS]# chmod 775 /var/log/oracle

[root@cell12 sf_OS]# rpm -ivh jdk-1.7.0_25-fcs.x86_64.rpm
warning: jdk-1.7.0_25-fcs.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing...                ########################################### [100%]
1:jdk                    ########################################### [100%]
Unpacking JAR files...
rt.jar...
jsse.jar...
charsets.jar...
tools.jar...
localedata.jar...

 

[root@cell12 sf_OS]# rpm -ivh cell-12.1.1.1.0_LINUX.X64_131219-1.x86_64.rpm
Preparing...                ########################################### [100%]
Pre Installation steps in progress ...
1:cell                   ########################################### [100%]
Post Installation steps in progress ...
Set cellusers group for /opt/oracle/cell12.1.1.1.0_LINUX.X64_131219/cellsrv/deploy/log directory
Set 775 permissions for /opt/oracle/cell12.1.1.1.0_LINUX.X64_131219/cellsrv/deploy/log directory
/opt/oracle/cell12.1.1.1.0_LINUX.X64_131219/cellsrv/deploy
/opt/oracle/cell12.1.1.1.0_LINUX.X64_131219/cellsrv/deploy
/opt/oracle/cell12.1.1.1.0_LINUX.X64_131219
Installation SUCCESSFUL.
Starting RS and MS... as user celladmin
Done. Please Login as user celladmin and create cell to startup CELLSRV to complete cell configuration.
If this is a manual installation, please stop and restart ExaWatcher to pick up newly installed binaries.
You can run "/opt/oracle.ExaWatcher/ExaWatcher.sh --stop" and then "/opt/oracle.ExaWatcher/ExaWatcher.sh --fromconf" to stop and restart ExaWatcher.
Logout and then re-login to use the new cell environment.

Now reboot and fix DISPLAY problem (login in a safe session, edit /etc/bashrc adding export DISPLAY=:0, exit and login again).

I already created 12 (524MB) + 6 (413MB) disks to emulate standard and flash disks. Those are visible from cell12 as (last 2 are system disks)

EDIT: during database node installation I realized that 12c requests more space than 11g. So I created the cell with 1GB celldisk. So I suggest to use 1GB in place of 500MB. Then here you should find 1GB disks.

[root@cell12 sf_OS]# fdisk -l 2>/dev/null | grep "B,"
Disk /dev/sda: 26.8 GB, 26843545600 bytes
Disk /dev/sdb: 524 MB, 524288000 bytes
Disk /dev/sdc: 524 MB, 524288000 bytes
Disk /dev/sdd: 524 MB, 524288000 bytes
Disk /dev/sde: 524 MB, 524288000 bytes
Disk /dev/sdf: 524 MB, 524288000 bytes
Disk /dev/sdg: 524 MB, 524288000 bytes
Disk /dev/sdh: 524 MB, 524288000 bytes
Disk /dev/sdi: 524 MB, 524288000 bytes
Disk /dev/sdj: 524 MB, 524288000 bytes
Disk /dev/sdk: 524 MB, 524288000 bytes
Disk /dev/sdl: 524 MB, 524288000 bytes
Disk /dev/sdm: 524 MB, 524288000 bytes
Disk /dev/sdn: 419 MB, 419430400 bytes
Disk /dev/sdo: 419 MB, 419430400 bytes
Disk /dev/sdp: 419 MB, 419430400 bytes
Disk /dev/sdq: 419 MB, 419430400 bytes
Disk /dev/sdr: 419 MB, 419430400 bytes
Disk /dev/sds: 419 MB, 419430400 bytes
Disk /dev/dm-0: 22.5 GB, 22515023872 bytes
Disk /dev/dm-1: 4194 MB, 4194304000 bytes

Storage cell software search disk under $T_WORK

[root@cell12 ~]# env | grep disk
T_WORK=/opt/oracle/cell12.1.1.1.0_LINUX.X64_131219/disks

So, I make them visible as

[root@cell12 ~]# cd /opt/oracle/cell12.1.1.1.0_LINUX.X64_131219
[root@cell12 cell12.1.1.1.0_LINUX.X64_131219]# mkdir disks
[root@cell12 cell12.1.1.1.0_LINUX.X64_131219]# mkdir disks/raw
[root@cell12 cell12.1.1.1.0_LINUX.X64_131219]# cd disks/raw/

Create and run symbolic link commands for standard disks

[root@cell12 raw]# fdisk -l 2>/dev/null | grep "524 MB" | awk '{ printf "%s%02d\n", "ln -s "$2" cell12_DISK", NR }'|sed "s/://"
ln -s /dev/sdb cell12_DISK01
ln -s /dev/sdc cell12_DISK02
ln -s /dev/sdd cell12_DISK03
ln -s /dev/sde cell12_DISK04
ln -s /dev/sdf cell12_DISK05
ln -s /dev/sdg cell12_DISK06
ln -s /dev/sdh cell12_DISK07
ln -s /dev/sdi cell12_DISK08
ln -s /dev/sdj cell12_DISK09
ln -s /dev/sdk cell12_DISK10
ln -s /dev/sdl cell12_DISK11
ln -s /dev/sdm cell12_DISK12

and for flash disks

[root@cell12 raw]# fdisk -l 2>/dev/null | grep "419 MB" | awk '{ printf "%s%02d\n", "ln -s "$2" cell12_FLASH", NR }'|sed "s/://"
ln -s /dev/sdn cell12_FLASH01
ln -s /dev/sdo cell12_FLASH02
ln -s /dev/sdp cell12_FLASH03
ln -s /dev/sdq cell12_FLASH04
ln -s /dev/sdr cell12_FLASH05
ln -s /dev/sds cell12_FLASH06

Before creating cell with cellcli, I need some more settings in sysctl.conf

[root@cell12 raw]# more /etc/sysctl.conf
.....
fs.file-max = 65536
fs.aio-max-nr=50000000

net.core.rmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=2097152

[root@cell12 raw]# sysctl -p

and edit the /etc/security/limits.conf files and add/set

* soft nofile 65536
* hard nofile 65536

Also I need to enable data transport RDS over InfiniBand (RDS_TCP over false InfiniBand)

[root@cell12 raw]# lsmod | grep rds
[root@cell12 raw]# modprobe rds
[root@cell12 raw]# modprobe rds_tcp
[root@cell12 raw]# modprobe rds_rdma
[root@cell12 raw]# lsmod | grep rds
rds_rdma               82580  0
rds_tcp                10455  0
rds                    96059  2 rds_rdma,rds_tcp
rdma_cm                36793  2 rds_rdma,ib_iser
ib_core                66577  7 rds_rdma,ib_iser,rdma_cm,ib_cm,iw_cm,ib_sa,ib_mad

and make that permanent editing/creating rds.conf

[root@cell12 ~]# more /etc/modprobe.d/rds.conf
install rds /sbin/modprobe –ignore-install rds && /sbin/modprobe rds_tcp && /sbin/modprobe rds_rdma

[EDIT: pay attention to double “–” that sometimes became a single char “–”]

Now, with celladmin user I’ll restart services, create cell and disks

[celladmin@cell12 ~]$ cellcli -e alter cell restart services all
Stopping the RS, CELLSRV, and MS services...
The SHUTDOWN of services was successful.
Starting the RS, CELLSRV, and MS services...
Getting the state of RS services...  running
Starting CELLSRV services...
The STARTUP of CELLSRV services was not successful.
CELL-01553: Incorrect IP in cellinit.ora. IP is invalid or has incorrect/missing netmask. Please refer to RS incident log.
Starting MS services...
The STARTUP of MS services was successful.

(error is quite normal: the cell isn’t initialized as IP and name)

[celladmin@cell12 ~]$ cellcli -e create cell cell12 interconnect1=eth1
Cell cell12 successfully created
Starting CELLSRV services…
The STARTUP of CELLSRV services was successful.
Flash cell disks, FlashCache, and FlashLog will be created…
CellDisk FD_00_cell12 successfully created
CellDisk FD_01_cell12 successfully created
CellDisk FD_02_cell12 successfully created
CellDisk FD_03_cell12 successfully created
CellDisk FD_04_cell12 successfully created
CellDisk FD_05_cell12 successfully created
Flash log cell12_FLASHLOG successfully created
Flash cache cell12_FLASHCACHE successfully created

(I’m not sure why Flash components are auto configured, but it can be modified later if needed)
Configure cell disks

[celladmin@cell12 ~]$ cellcli -e create celldisk all
CellDisk CD_DISK01_cell12 successfully created
CellDisk CD_DISK02_cell12 successfully created
CellDisk CD_DISK03_cell12 successfully created
CellDisk CD_DISK04_cell12 successfully created
CellDisk CD_DISK05_cell12 successfully created
CellDisk CD_DISK06_cell12 successfully created
CellDisk CD_DISK07_cell12 successfully created
CellDisk CD_DISK08_cell12 successfully created
CellDisk CD_DISK09_cell12 successfully created
CellDisk CD_DISK10_cell12 successfully created
CellDisk CD_DISK11_cell12 successfully created
CellDisk CD_DISK12_cell12 successfully created

and grid disks

[celladmin@cell12 ~]$ cellcli -e create griddisk all harddisk prefix=DATA
GridDisk DATA_CD_DISK01_cell12 successfully created
GridDisk DATA_CD_DISK02_cell12 successfully created
GridDisk DATA_CD_DISK03_cell12 successfully created
GridDisk DATA_CD_DISK04_cell12 successfully created
GridDisk DATA_CD_DISK05_cell12 successfully created
GridDisk DATA_CD_DISK06_cell12 successfully created
GridDisk DATA_CD_DISK07_cell12 successfully created
GridDisk DATA_CD_DISK08_cell12 successfully created
GridDisk DATA_CD_DISK09_cell12 successfully created
GridDisk DATA_CD_DISK10_cell12 successfully created
GridDisk DATA_CD_DISK11_cell12 successfully created
GridDisk DATA_CD_DISK12_cell12 successfully created

Done.

I also suggest to set default level to 3 into /etc/inittab to avoid GUI overhead on storage cell.

/*+ esp */

Let me add this …

So, after some study … I got it !!

Image

I can say that creating the simulator helped a lot on all topics related to disk an cluster in Exadata context. What I couldn’t well test (and answers reflected that) is all stuff related to real hardware, ports, EM configurations (docs are quite confused about OMS, Agent, Agent Plugin).

 

But, by the way, I hit the target!

 

/*+ esp */

Install OGG on DBFS

In this post I created some DBFS file systems to share sw and data between nodes of an Exadata. Such file systems enable the possibility of a HA configuration of the application in that environment.

I know it is quite impossible to guess the application from file systems names (ogg_home1, ogg_trail1, ogg_home2, ogg_trail2 !! :-) ), so I’m here to unravel the mystery.

I’m going to install Oracle Golden Gate (OGG) on both ogg_home1 and ogg_home2 that will use respectively ogg_trail1 and ogg_trail2 for trail files (i.e. data to load into my RAC db through repcat process).

I played with OGG during an Oracle Developer Day in Rome. I used a virtual appliance built by Oracle Experts. I didn’t realize how is simple to install OGG. It is like a portable application on a pendrive !!

Into my usual path based on VirtualBox Shared Folder I downloaded from this page, after accepting Oracle Software Policies, “Oracle GoldenGate V11.2.1.0.1 for Oracle 11g on Linux x86-64″ (86 MB).

[oracle@exadb1 ~]$ cd /media/sf_OS/
[oracle@exadb1 sf_OS]$ ll
total 87112
-rwxrwx--- 1 root vboxsf 89186858 Feb  3 16:51 ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

Unzipping

[oracle@exadb1 sf_OS]$ unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
Archive:  ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar
inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
inflating: Oracle GoldenGate 11.2.1.0.1 README.txt
inflating: Oracle GoldenGate 11.2.1.0.1 README.doc

Untaring

[oracle@exadb1 sf_OS]$ mkdir ogginst; cd ogginst
[oracle@exadb1 ogginst]$ tar xvf ../fbo_ggs_Linux_x64_ora11g_64bit.tar
...

Now the install process has three steps: copy sw files, set environment, configure directories.
Obviously to use OGG I’ll need more configurations on source and target systems, but this is out of scope actually.

First home on first node (ogg_home1 on exadb1)
Copy SW files:

[oracle@exadb1 ~]$ cd /exa/app
[oracle@exadb1 app]$ dbfs_client -o allow_root dbfs_usr_ogg1@exadb /exa/app/fs_ogg1 < pwd_ogg1 &
[oracle@exadb1 app]$ cp -pR /media/sf_OS/ogginst/* /exa/app/fs_ogg1/ogg_home1/

Set Environment:

[oracle@exadb1 app]$ cd /exa/app/fs_ogg1/ogg_home1
[oracle@exadb1 ogg_home1]$ vi ogg_env1
...
[oracle@exadb1 ogg_home1]$ more ogg_env1
OGG_HOME=/exa/app/fs_ogg1/ogg_home1
export OGG_HOME

[oracle@exadb1 ogg_home1]$ . ogg_env1
[oracle@exadb1 ogg_home1]$ ln -s /exa/app/fs_ogg1/ogg_trail1 dirda

Configure Directories:

[oracle@exadb1 ogg_home1]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (exadb1) 1> create subdirs
Creating subdirectories under current directory /exa/app/fs_ogg1/ogg_home1
Parameter files                /exa/app/fs_ogg1/ogg_home1/dirprm: already exists
Report files                   /exa/app/fs_ogg1/ogg_home1/dirrpt: created
Checkpoint files               /exa/app/fs_ogg1/ogg_home1/dirchk: created
Process status files           /exa/app/fs_ogg1/ogg_home1/dirpcs: created
SQL script files               /exa/app/fs_ogg1/ogg_home1/dirsql: created
Database definitions files     /exa/app/fs_ogg1/ogg_home1/dirdef: created
Extract data files             /exa/app/fs_ogg1/ogg_home1/dirdat: already exists
Temporary files                /exa/app/fs_ogg1/ogg_home1/dirtmp: created
Stdout files                   /exa/app/fs_ogg1/ogg_home1/dirout: created
GGSCI (exadb1) 2> exit

All the same for second home on second node (ogg_home2 on exadb2)

[oracle@exadb2 ~]$ cd /exa/app
[oracle@exadb2 app]$ dbfs_client -o allow_root dbfs_usr_ogg2@exadb /exa/app/fs_ogg2 < pwd_ogg2 &
[oracle@exadb2 app]$ cp -pR /media/sf_OS/ogginst/* /exa/app/fs_ogg2/ogg_home2/

[oracle@exadb2 app]$ cd /exa/app/fs_ogg2/ogg_home2
[oracle@exadb2 ogg_home2]$ vi ogg_env2
...
[oracle@exadb2 ogg_home2]$ more ogg_env2
OGG_HOME=/exa/app/fs_ogg2/ogg_home2
export OGG_HOME

[oracle@exadb2 ogg_home2]$ . ogg_env2
[oracle@exadb2 ogg_home2]$ ln -s /exa/app/fs_ogg2/ogg_trail2 dirdat

[oracle@exadb2 ogg_home2]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (exadb2) 1> create subdirs
Creating subdirectories under current directory /exa/app/fs_ogg2/ogg_home2
Parameter files                /exa/app/fs_ogg2/ogg_home2/dirprm: already exists
Report files                   /exa/app/fs_ogg2/ogg_home2/dirrpt: created
Checkpoint files               /exa/app/fs_ogg2/ogg_home2/dirchk: created
Process status files           /exa/app/fs_ogg2/ogg_home2/dirpcs: created
SQL script files               /exa/app/fs_ogg2/ogg_home2/dirsql: created
Database definitions files     /exa/app/fs_ogg2/ogg_home2/dirdef: created
Extract data files             /exa/app/fs_ogg2/ogg_home2/dirdat: already exists
Temporary files                /exa/app/fs_ogg2/ogg_home2/dirtmp: created
Stdout files                   /exa/app/fs_ogg2/ogg_home2/dirout: created
GGSCI (exadb2) 2> exit

Mission Complete. Next step will be create 2 custom cluster apps to manage OGG independently from the active node/nodes.

/*+ esp */

Exadata Simulators

I haven’t so much RAM and power to run a quarter Exadata Simulator, but I need something to practice with Exadata concepts and commands.

So I tried 2 different configurations of Exadata Simulator to find the one that will satisfy my needs.

Here I add some schemas about those configurations.

First confguration: 1 database node with 2 storage cells. Here the post and down the schema.

ExaSim

Second confguration: 2 database nodes with 1 storage cell. Here the post and down the schema.

ExaSimRAC

For who needs a brief introduction to Exadata I suggest this and related and obviously Oracle docs, especially Oracle Learning Library (account needed).

/*+ esp */

Move from unsupported version

Let me know your ideas about that.
I have mine and I’m starting to test it, but I’m curious.

DBFS on Exadata (but also not !)

For a project in which I’m involved I need to share files between 2 db nodes of an Exadata machine.

But on Exadata there are 2 kind of storage:
– local to db node, tipically OS, DBMS SW and similar
– exadata storage cell, usable only through iDB protocol (yes, I know those are linux machines then technically I could force those machines to share space in some way, but it doesn’t seem correct)
Both cannot be used as is because files should be accessible from db nodes even in case of single machine fault.

DBFS seems to be the right solution for my problem:
– created into the RAC database, then accessible from both machines
– based on ASM (in this case, Exadata), then I can add data protection at diskgroup level
– mountable as a starndard file system (through fuse) on Linux

Let try on the simulator to test how the solution is hard to implement.

Mount points/file systems … Let me detail the request:
On each machine I need 1 file system for SW and 1 file system for data:
– node 1 (exadb1): ogg_home1, ogg_trail1
– node 2 (exadb2): ogg_home2, ogg_trail2
SW and data of each node must be visible from the other one.

DBFS … Let me overview how it works:
The description from Oracle docs is quite clear
“In DBFS, the server is the Oracle Database. Files are stored as SecureFiles LOBs in a database table. A set of PL/SQL procedures implement the file system access primitives such as create, open, read, write, and list directory. The implementation of the file system in the database is called the DBFS Content Store. The DBFS Content Store allows each database user to create one or more file systems that can be mounted by clients. Each file system has its own dedicated tables that hold the file system content”

What isn’t immediately clear here is that some commands handle simultaneously all DBFS file systems belonging to a db user, for example the client side program for Linux (something similar to a mount command).

Then I need:
– 2 database users (dbfs_usr_ogg1 and dbfs_usr_ogg2)
– 2 dbfs (ogg_home1, ogg_trail1) under dbfs_usr_ogg1’s schema
– 2 dbfs (ogg_home2, ogg_trail2) under dbfs_usr_ogg2’s schema
– 2 mount points (/exa/app/fs_ogg1, /exa/app/fs_ogg2) on both machines (exadb1, exadb2)

When mounted, I’ll have access to
– /exa/app/fs_ogg1/ogg_home1 and /exa/app/fs_ogg1/ogg_trail1 usually on exadb1
– /exa/app/fs_ogg2/ogg_home2 and /exa/app/fs_ogg2/ogg_trail2 usually on exadb2
First of all I need to update my db nodes with the latest release of fuse. I’ll show on exadb1, but I must execute on both (exadb1 exadb2)

[root@exadb1 ~]# yum install fuse fuse-libs
...
Setting up Install Process
Resolving Dependencies
...
Total download size: 228 k
Is this ok [y/N]: Y
...
Installed:
fuse.x86_64 0:2.7.4-8.0.5.el5                       fuse-libs.i386 0:2.7.4-8.0.5.el5                       fuse-libs.x86_64 0:2.7.4-8.0.5.el5
Complete!

In order to use fuse with dbfs_client command and mount file systems on regular path, some dynamic libraries must be linked

[root@exadb1 app]# echo "/usr/local/lib" >> /etc/ld.so.conf.d/usr_local_lib.conf
[root@exadb1 app]# cd /usr/local/lib
[root@exadb1 lib]# export ORACLE_HOME=/exa/app/oracle/product/11.2.0/dbhome_1
[root@exadb1 lib]# ln -s $ORACLE_HOME/lib/libclntsh.so.11.1 libclntsh.so.11.1
[root@exadb1 lib]# ln -s $ORACLE_HOME/lib/libnnz11.so libnnz11.so
[root@exadb1 lib]# ln -s /lib64/libfuse.so.2.7.4 libfuse.so
[root@exadb1 lib]# ldconfig
[root@exadb1 lib]# ln -s $ORACLE_HOME/bin/dbfs_client /sbin/mount.dbfs

Then prepare user oracle and mount points.
Oracle user must be in the fuse group (created by fuse pkg install)

usermod -a -G fuse oracle

Mount points

[root@exadb1 ~]# cd /exa/app
[root@exadb1 app]# mkdir fs_ogg1 fs_ogg2
[root@exadb1 app]# chown oracle:oinstall fs_ogg1 fs_ogg2

End of work on machines. Now into the RAC database (EXADB) create users, DBFS content stores and file systems.

[oracle@exadb1 ~]$ sqlplus / as sysdba
...
SQL> CREATE TABLESPACE dbfs_ts_ogg1;SQL> col tablespace_name for a20
SQL> col file_name for a50
SQL> set lines 200
SQL> select TABLESPACE_NAME, FILE_NAME, BYTES from dba_data_files where tablespace_name like 'DBFS_TS%';

TABLESPACE_NAME      FILE_NAME                                               BYTES
-------------------- -------------------------------------------------- ----------
DBFS_TS_OGG1         +DATA/exadb/datafile/dbfs_ts_ogg1.269.838565847     104857600

SQL> CREATE USER dbfs_usr_ogg1 IDENTIFIED BY dbfs_usr_ogg1
DEFAULT TABLESPACE dbfs_ts_ogg1 QUOTA UNLIMITED ON dbfs_ts_ogg1;
User created.

SQL> GRANT CREATE SESSION, RESOURCE, CREATE VIEW, DBFS_ROLE TO dbfs_usr_ogg1;
Grant succeeded.

SQL> connect dbfs_usr_ogg1/dbfs_usr_ogg1
Connected.

SQL> @?/rdbms/admin/dbfs_create_filesystem.sql dbfs_ts_ogg1 ogg_home1
No errors.
--------
CREATE STORE:
begin dbms_dbfs_sfs.createFilesystem(store_name => 'FS_OGG_HOME1', tbl_name =>
'T_OGG_HOME1', tbl_tbs => 'dbfs_ts_ogg1', lob_tbs => 'dbfs_ts_ogg1',
do_partition => false, partition_key => 1, do_compress => false, compression =>
'', do_dedup => false, do_encrypt => false); end;
--------
REGISTER STORE:
begin dbms_dbfs_content.registerStore(store_name=> 'FS_OGG_HOME1', provider_name
=> 'sample1', provider_package => 'dbms_dbfs_sfs'); end;
--------
MOUNT STORE:
begin dbms_dbfs_content.mountStore(store_name=>'FS_OGG_HOME1',
store_mount=>'ogg_home1'); end;
--------
CHMOD STORE:
declare m integer; begin m := dbms_fuse.fs_chmod('/ogg_home1', 16895); end;
No errors.

SQL> @?/rdbms/admin/dbfs_create_filesystem.sql dbfs_ts_ogg1 ogg_trail1
No errors.
--------
CREATE STORE:
begin dbms_dbfs_sfs.createFilesystem(store_name => 'FS_OGG_TRAIL1', tbl_name =>
'T_OGG_TRAIL1', tbl_tbs => 'dbfs_ts_ogg1', lob_tbs => 'dbfs_ts_ogg1',
do_partition => false, partition_key => 1, do_compress => false, compression =>
'', do_dedup => false, do_encrypt => false); end;
--------
REGISTER STORE:
begin dbms_dbfs_content.registerStore(store_name=> 'FS_OGG_TRAIL1',
provider_name => 'sample1', provider_package => 'dbms_dbfs_sfs'); end;
--------
MOUNT STORE:
begin dbms_dbfs_content.mountStore(store_name=>'FS_OGG_TRAIL1',
store_mount=>'ogg_trail1'); end;
--------
CHMOD STORE:
declare m integer; begin m := dbms_fuse.fs_chmod('/ogg_trail1', 16895); end;
No errors.

Same steps for second user and related file systems (I’ll omit some messages)

SQL> connect / as sysdba
SQL> CREATE TABLESPACE dbfs_ts_ogg2;SQL> col tablespace_name for a20
SQL> col file_name for a50
SQL> set lines 200
SQL> select TABLESPACE_NAME, FILE_NAME, BYTES from dba_data_files where tablespace_name like 'DBFS_TS%';

TABLESPACE_NAME      FILE_NAME                                               BYTES
-------------------- -------------------------------------------------- ----------
DBFS_TS_OGG1         +DATA/exadb/datafile/dbfs_ts_ogg1.269.838565847     104857600
DBFS_TS_OGG2         +DATA/exadb/datafile/dbfs_ts_ogg2.270.838566105     104857600

SQL> CREATE USER dbfs_usr_ogg2 IDENTIFIED BY dbfs_usr_ogg2
DEFAULT TABLESPACE dbfs_ts_ogg2 QUOTA UNLIMITED ON dbfs_ts_ogg2;

SQL> GRANT CREATE SESSION, RESOURCE, CREATE VIEW, DBFS_ROLE TO dbfs_usr_ogg2;

SQL> connect dbfs_usr_ogg2/dbfs_usr_ogg2

SQL> @?/rdbms/admin/dbfs_create_filesystem.sql dbfs_ts_ogg2 ogg_home2
...
SQL> @?/rdbms/admin/dbfs_create_filesystem.sql dbfs_ts_ogg2 ogg_trail2
...

In order to use these file systems I must mount them using dbfs_client command from oracle users.
For my first try I’m satisfied with a simple password authentication, but for the real system I’ll must use a wallet.

echo "dbfs_usr_ogg1" > pwd_fs_ogg1
echo "dbfs_usr_ogg2" > pwd_fs_ogg2nohup dbfs_client dbfs_usr_ogg1@exadb /exa/app/fs_ogg1 < pwd_fs_ogg1 &
nohup dbfs_client dbfs_usr_ogg2@exadb /exa/app/fs_ogg2 < pwd_fs_ogg2 &

ls -la /exa/app/fs_ogg1/ogg_home1/
ls -la /exa/app/fs_ogg1/ogg_trail1/
ls -la /exa/app/fs_ogg2/ogg_home2/
ls -la /exa/app/fs_ogg2/ogg_trail2/

Just to try use these file systems I downloaded OGG 11.2 from here (into my usual path and use it through VirtualBox Shared Folder) and put the SW into the 2 SW homes

[oracle@exadb1 ~]$ cd /media/sf_OS/ogg
[oracle@exadb1 ogg]$ ll
total 87112
-rwxrwx--- 1 root vboxsf 89186858 Feb  3 16:51 ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[oracle@exadb1 ogg]$ unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
Archive:  ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar
inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
inflating: Oracle GoldenGate 11.2.1.0.1 README.txt
inflating: Oracle GoldenGate 11.2.1.0.1 README.doc[oracle@exadb1 ogg]$ cd /exa/app/fs_ogg1/ogg_home1
[oracle@exadb1 ogg_home1]$ tar -xvf /media/sf_OS/ogg/fbo_ggs_Linux_x64_ora11g_64bit.tar

[oracle@exadb1 ogg_home1]$ cd /exa/app/fs_ogg2/ogg_home2
[oracle@exadb1 ogg_home2]$ tar -xvf /media/sf_OS/ogg/fbo_ggs_Linux_x64_ora11g_64bit.tar

[oracle@exadb1 ogg_home2]$ ls -la /exa/app/fs_ogg1/ogg_home1/
[oracle@exadb1 ogg_home2]$ ls -la /exa/app/fs_ogg2/ogg_home2/

For a complete test create password files and run dbfs_client commands on the second machine.
I’m now able to work on the same file system from both machines (that is more than I needed !)

I didn’t find any typical exadata topic here. So this is applicable in every similar conditions (rdbms 11.2 RAC or not, linux client machine).

Next step will be add 2 custom OGG applications to the cluster !!

/*+ esp */