Oracle 12c In Memory Option – Overview

Posted on 29/11/2014 by dbaesp

/* 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)

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

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

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

Posted on 16/03/2014 by dbaesp

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 …

Posted on 28/02/2014 by dbaesp

So, after some study … I got it !!

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

Posted on 14/02/2014 by dbaesp

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 &lt; pwd_ogg1 &amp;
[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&gt; 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&gt; 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 &lt; pwd_ogg2 &amp;
[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&gt; 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&gt; exit

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

/*+ esp */

Exadata Simulators

Posted on 12/02/2014 by dbaesp

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.

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

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

Posted on 05/02/2014 by dbaesp

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 !)

Posted on 04/02/2014 by dbaesp

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 */

Oracle rdbms 12c – first look (for me)

Posted on 30/01/2014 by dbaesp

I was curious about 12c, but I spent a lot of time on Exadata and I had no time for that.
At least I must prepare an environment (very simple) to be able to test something !!

Let me try.
I already have a VM with Oracle 6 because I installed for that months ago.

I’m surprised: the whole process now is really trivial. Actually is more difficult to install Flash player than Oracle 12c!

I’ll add a few comments and let screenshots show the sequence.

First of all, as root I bring the VM up to date

[root@oratest12 ~]# yum update

Than all that I need to modify on my VM to install 12c is completed with

[root@oratest12 ~]# yum install oracle-rdbms-server-12*

After that I have system parameters, users and groups configured.

My /etc/hosts looks like

[root@oratest12 ~]# more /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 oratest12.oragroup.local
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

As usual I use VirtualBox Shared Folders as temporary storage for installation SW, that can be found on OTN.
After login and agreement on Oracle Policies, the 2 zip files for Oracle 12c 12.1.0.1.0 can be downloaded from here.

My shared folder path is mounted on /media/sf_STAGE and I put zip files under /media/sf_STAGE/Linux64
[Note: I mistyped “mounted” and I wrote “mojited” … the Freud perspective could be interesting !!]

As root user I do

[root@oratest12 ~]# cd /media/sf_STAGE/Linux64/
[root@oratest12 Linux64]# unzip linuxamd64_12c_database_1of2.zip
…
[root@oratest12 Linux64]# unzip linuxamd64_12c_database_2of2.zip
…

Now I modify oracle users to make him able to use the shared folder

[root@oratest12 ~]# usermod -g oinstall -G dba,vboxsf oracle

and enable X-connection to display manager for oracle user

root@oratest12 ~]# xhost +
access control disabled, clients can connect from any host

Ready

[root@oratest12 ~]# su – oracle
[oracle@oratest12 ~]$ cd /media/sf_STAGE/Linux64/database/
[oracle@oratest12 database]$ ./runInstaller
Starting Oracle Universal Installer…Checking Temp space: must be greater than 500 MB.   Actual 10053 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 4031 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2014-01-29_03-55-15PM. Please wait …

No updates from support

Skip SW updates

Server Class

Advanced install … I’m not sure, but I could configure better

Enterprise laptop …

Defaults

With sample pluggable database (I need extra storage space)

With sample schemas

Default

No cloud EM

Some more space, but I could test more

oracle 🙂

Defaults

And go!

Usual steps as root

[root@oratest12 ~]# /home/oracle/app/oraInventory/orainstRoot.sh
Changing permissions of /home/oracle/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.Changing groupname of /home/oracle/app/oraInventory to oinstall.
The execution of the script is complete.

and

[root@oratest12 ~]# /home/oracle/app/oracle/product/12.1.0/dbhome_1/root.sh
Performing root user operation for Oracle 12cThe following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /home/oracle/app/oracle/product/12.1.0/dbhome_1Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin …
Copying oraenv to /usr/local/bin …
Copying coraenv to /usr/local/bin …Creating /etc/oratab file…
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.

Configuration Assistants at work

DONE!!

Unlock all users for future test … (Passwords Management button)

End of installation

First look to Oracle 12c up and ready with its EM console.

/*+ esp */

Exadata Simulator, 2way RAC + 1 storage cell

Posted on 25/01/2014 by dbaesp

To build a more useful simulator for studying, due lack of memory of my laptop, I must create a single cell – single db configuration. But I can try building a 2 way RAC on a single cell and then use just one db node for studying.
Let’s start.

To build the storage cell node I can use exactly the same steps as I did in previous post
Exadata Virtual Test Environment for OCE Prep – storage cell node
By the way I already have the storage node from my previous simulator, than I’ll drop and recreate the cell from cellcli command line.

/*
cellcli -e drop cell force
cellcli -e create cell stocell1 interconnect1=eth1
cellcli -e create celldisk all
cellcli -e create griddisk all hardisk prefix=DATA
*/

To build the first db node I start as I did previously cloning storage cell node as in
Clone the VM from storage cell node to db node
Including Cluster Verification Utility (I’m not sure I cited in that post)

cd /media/sf_OS/grid/rpm
rpm -Uvh cvuqdisk*

and users/path/oracle common settings

passwd grid
passwd oracle

mkdir -p /exa/app/grid /exa/app/oracle
chown -R grid:oinstall /exa/app
chown oracle:oinstall /exa/app/oracle
chmod 775 /exa/app

[root@exadb1 ~]# more /etc/oracle/cell/network-config/cellinit.ora
ipaddress1 = 192.168.10.30/24 #InfiniBand IP/network of exadb1

[root@exadb1 ~]# more /etc/oracle/cell/network-config/cellip.ora
cell = “192.168.10.20” #InfiniBand IP of stocell1

(Copy & Paste from web page changes the ” char; pay attention)

[root@exadb1 ~]# more /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1       stocell1        localhost.localdomain localhost
#InfiniBand IPs
192.168.10.20 stocell1      stocell1.exatest
192.168.10.30 exadb1-ib exadb1-ib.exatest
192.168.10.31 exadb2-ib exadb2-ib.exatest
#Public IPs
192.168.20.30 exadb1      exadb1.exatest
192.168.20.31 exadb2      exadb2.exatest
192.168.20.130 exadb1-vip              exadb1-vip.exatest
192.168.20.131 exadb2-vip              exadb2-vip.exatest
192.168.20.100 exadb-cluster-scan      exadb-cluster-scan.exatest

Then I cloned db node exadb1 to exadb2 using again Virtualbox feature and differentiate networks through network manager and /etc/sysconfig/network-scripts configurations consistently with names and IPs reported above.
Also the cellinit.ora file must be edited

[root@exadb2 ~]# more /etc/oracle/cell/network-config/cellinit.ora
ipaddress1 = 192.168.10.31/24 #InfiniBand IP/network of exadb2

The ntpd configuration I chose (stocell1 ntpd server, exadb? ntpd clients) is a bit different from the previous one (stocell? ntpd clients, exadb1 ntpd server), but I think it is not mandatory for a non production environment. Here ntp configuration for the three machines

[root@exadb1 ~]# more /etc/ntp.conf
server 192.168.10.20 #stocell1
server 127.127.1.0 # local clock
fudge 127.127.1.0 stratum 10
driftfile /var/lib/ntp/drift

[root@exadb2 ~]# more /etc/ntp.conf
server 192.168.10.20 #stocell1
server 127.127.1.0 # local clock
fudge 127.127.1.0 stratum 10
driftfile /var/lib/ntp/drift

[root@stocell1 ~]# more /etc/ntp.conf
server 127.127.1.0 # local clock
fudge 127.127.1.0 stratum 10
driftfile /var/lib/ntp/drift

On all machines

#more /etc/sysconfig/ntpd
# Drop root to id ‘ntp:ntp’ by default.
OPTIONS=”-x -u ntp:ntp -p /var/run/ntpd.pid”# Set to ‘yes’ to sync hw clock after successful ntpdate
SYNC_HWCLOCK=no# Additional options for ntpdate
NTPDATE_OPTIONS=”-t 10″

chkconfig ntpd on
service ntpd start

Before proceed check and double check networks (config, ping, ssh/telnet) 192.168.10.* and 192.168.20.* and hostname resolutions (cloning tend to maintain old network names and/or mac addresses).

Reinstall Virtualbox Guest Addiction (every patch or similar I need to do this if I want to copy to/from VM …)

Ready to install Grid Infrastructure for a Cluster.

I use as usual a shared Virtualbox folder where I put grid and database installation software (same path and same versions of previous simulator). I already have all I’ll need under /media/sf_OS, visible from all machines.

Check disk availability before start installation: kfod utility
As grid user I check on both nodes

export LD_LIBRARY_PATH=/media/sf_OS/grid/stage/ext/lib
/media/sf_OS/grid/stage/ext/bin/kfod disks=all op=disks

[root@exadb1 ~]# /media/sf_OS/grid/stage/ext/bin/kfod disks=all op=disks
——————————————————————————–
Disk          Size Path                                     User     Group
================================================================================
1:        448 Mb o/192.168.10.20/DATA_CD_DISK01_stocell1 <unknown> <unknown>
2:        448 Mb o/192.168.10.20/DATA_CD_DISK02_stocell1 <unknown> <unknown>
3:        448 Mb o/192.168.10.20/DATA_CD_DISK03_stocell1 <unknown> <unknown>
4:        448 Mb o/192.168.10.20/DATA_CD_DISK04_stocell1 <unknown> <unknown>
5:        448 Mb o/192.168.10.20/DATA_CD_DISK05_stocell1 <unknown> <unknown>
6:        448 Mb o/192.168.10.20/DATA_CD_DISK06_stocell1 <unknown> <unknown>
7:        448 Mb o/192.168.10.20/DATA_CD_DISK07_stocell1 <unknown> <unknown>
8:        448 Mb o/192.168.10.20/DATA_CD_DISK08_stocell1 <unknown> <unknown>
9:        448 Mb o/192.168.10.20/DATA_CD_DISK09_stocell1 <unknown> <unknown>
10:        448 Mb o/192.168.10.20/DATA_CD_DISK10_stocell1 <unknown> <unknown>
11:        448 Mb o/192.168.10.20/DATA_CD_DISK11_stocell1 <unknown> <unknown>
12:        448 Mb o/192.168.10.20/DATA_CD_DISK12_stocell1 <unknown> <unknown>

[root@exadb2 tmp]# /media/sf_OS/grid/stage/ext/bin/kfod disks=all op=disks
——————————————————————————–
Disk          Size Path                                     User     Group
================================================================================
1:        448 Mb o/192.168.10.20/DATA_CD_DISK01_stocell1 <unknown> <unknown>
2:        448 Mb o/192.168.10.20/DATA_CD_DISK02_stocell1 <unknown> <unknown>
3:        448 Mb o/192.168.10.20/DATA_CD_DISK03_stocell1 <unknown> <unknown>
4:        448 Mb o/192.168.10.20/DATA_CD_DISK04_stocell1 <unknown> <unknown>
5:        448 Mb o/192.168.10.20/DATA_CD_DISK05_stocell1 <unknown> <unknown>
6:        448 Mb o/192.168.10.20/DATA_CD_DISK06_stocell1 <unknown> <unknown>
7:        448 Mb o/192.168.10.20/DATA_CD_DISK07_stocell1 <unknown> <unknown>
8:        448 Mb o/192.168.10.20/DATA_CD_DISK08_stocell1 <unknown> <unknown>
9:        448 Mb o/192.168.10.20/DATA_CD_DISK09_stocell1 <unknown> <unknown>
10:        448 Mb o/192.168.10.20/DATA_CD_DISK10_stocell1 <unknown> <unknown>
11:        448 Mb o/192.168.10.20/DATA_CD_DISK11_stocell1 <unknown> <unknown>
12:        448 Mb o/192.168.10.20/DATA_CD_DISK12_stocell1 <unknown> <unknown>

As user root do

[root@exadb1 ~]# xhost +
[root@exadb1 ~]# su – grid
[grid@exadb1 ~]$ cd /media/sf_OS/grid/
[grid@exadb1 grid]$ ./runInstaller

EXADB1 – node 1 RAC

[root@exadb1 ~]# /exa/app/oraInventory/orainstRoot.sh
Changing permissions of /exa/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /exa/app/oraInventory to oinstall.
The execution of the script is complete.

[root@exadb1 ~]# /exa/app/11.2.0/grid/root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
ORACLE_OWNER= grid
ORACLE_HOME= /exa/app/11.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of “dbhome” have not changed. No need to overwrite.
The contents of “oraenv” have not changed. No need to overwrite.
The contents of “coraenv” have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /exa/app/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
User ignored Prerequisites during installation
Installing Trace File Analyzer
OLR initialization – successful
root wallet
root wallet cert
root cert export
peer wallet
profile reader wallet
pa wallet
peer wallet keys
pa wallet keys
peer cert request
pa cert request
peer cert
pa cert
peer root cert TP
profile reader root cert TP
pa root cert TP
peer pa cert TP
pa peer cert TP
profile reader pa cert TP
profile reader peer cert TP
peer user cert
pa user cert
Adding Clusterware entries to inittab
CRS-2672: Attempting to start ‘ora.mdnsd’ on ‘exadb1’
CRS-2676: Start of ‘ora.mdnsd’ on ‘exadb1’ succeeded
CRS-2672: Attempting to start ‘ora.gpnpd’ on ‘exadb1’
CRS-2676: Start of ‘ora.gpnpd’ on ‘exadb1’ succeeded
CRS-2672: Attempting to start ‘ora.cssdmonitor’ on ‘exadb1’
CRS-2672: Attempting to start ‘ora.gipcd’ on ‘exadb1’
CRS-2676: Start of ‘ora.gipcd’ on ‘exadb1’ succeeded
CRS-2676: Start of ‘ora.cssdmonitor’ on ‘exadb1’ succeeded
CRS-2672: Attempting to start ‘ora.cssd’ on ‘exadb1’
CRS-2672: Attempting to start ‘ora.diskmon’ on ‘exadb1’
CRS-2676: Start of ‘ora.diskmon’ on ‘exadb1’ succeeded
CRS-2676: Start of ‘ora.cssd’ on ‘exadb1’ succeeded

ASM created and started successfully.

Disk Group MGMT created successfully.

clscfg: -install mode specified
Successfully accumulated necessary OCR keys.
Creating OCR keys for user ‘root’, privgrp ‘root’..
Operation successful.
CRS-4256: Updating the profile
Successful addition of voting disk 5bcae5728f0a4f5fbfc6a734e7388e72.
Successfully replaced voting disk group with +MGMT.
CRS-4256: Updating the profile
CRS-4266: Voting file(s) successfully replaced
## STATE    File Universal Id                File Name Disk group
— —–    —————–                ——— ———
1. ONLINE   5bcae5728f0a4f5fbfc6a734e7388e72 (o/192.168.10.20/DATA_CD_DISK01_stocell1) [MGMT]
Located 1 voting disk(s).
CRS-2672: Attempting to start ‘ora.asm’ on ‘exadb1’
CRS-2676: Start of ‘ora.asm’ on ‘exadb1’ succeeded
CRS-2672: Attempting to start ‘ora.MGMT.dg’ on ‘exadb1’
CRS-2676: Start of ‘ora.MGMT.dg’ on ‘exadb1’ succeeded
Configure Oracle Grid Infrastructure for a Cluster … succeeded

EXADB2 – node 2 RAC

[root@exadb2 ~]# /exa/app/oraInventory/orainstRoot.sh
Changing permissions of /exa/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /exa/app/oraInventory to oinstall.
The execution of the script is complete.

[root@exadb2 ~]# /exa/app/11.2.0/grid/root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
ORACLE_OWNER= grid
ORACLE_HOME= /exa/app/11.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin …
Copying oraenv to /usr/local/bin …
Copying coraenv to /usr/local/bin …

Creating /etc/oratab file…
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /exa/app/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
User ignored Prerequisites during installation
Installing Trace File Analyzer
OLR initialization – successful
Adding Clusterware entries to inittab
CRS-4402: The CSS daemon was started in exclusive mode but found an active CSS daemon on node exadb1, number 1, and is terminating
An active cluster was found during exclusive startup, restarting to join the cluster
Configure Oracle Grid Infrastructure for a Cluster … succeeded

Configure grid user’s environment. Insert into .bashrc of grid on exadb1 and exadb2

EXADB1 – node 2 RAC

#Grid & ASM settings
export ORACLE_BASE=/exa/app/grid
export ORACLE_HOME=/exa/app/11.2.0/grid/
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=+ASM1

EXADB2 – node 2 RAC

#Grid & ASM settings
export ORACLE_BASE=/exa/app/grid
export ORACLE_HOME=/exa/app/11.2.0/grid/
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=+ASM2

Next some configuration completion and check …

Prepare diskgroup for sample database using asmca

Finally database software installation. To be able using shared folder with oracle user, on both nodes I need

[root@exadb1 app]# usermod -g oinstall -G dba,oper,asmadmin,vboxsf oracle

[root@exadb2 app]# usermod -g oinstall -G dba,oper,asmadmin,vboxsf oracle

On exadb1 ad root do

xhost +
su – oracle
cd /media/sf_OF/database
./runInstaller

In the next choice I did the wrong, because I specified MGMT diskgroup in place of DATA diskgroup. After sw install I’ll must use dbca to configure the sample database.
So, the right choice here is DATA

OK. DNS fault. No problem I have /etc/hosts well configured

EXADB1 – node 1 RAC

[root@exadb1 app]# /exa/app/oracle/product/11.2.0/dbhome_1/root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /exa/app/oracle/product/11.2.0/dbhome_1

EXADB2 – node 2 RAC

[root@exadb2 app]# /exa/app/oracle/product/11.2.0/dbhome_1/root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /exa/app/oracle/product/11.2.0/dbhome_1

With environment variables set export ORACLE_BASE=/exa/app/oracle

export ORACLE_HOME=/exa/app/oracle/product/11.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin

dbca

Another error due my mistake on first database creation wizard. No problem. I’ll install EM later.

Database completed !!!

I have my Exadata Simulator with 2-way RAC and 1 storage cell.

I add usual settings to .bashrc of oracle user

EXADB1 – node 1 RAC

#Oracle settings
export ORACLE_BASE=/exa/app/oracle
export ORACLE_HOME=/exa/app/oracle/product/11.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=exadb1

EXADB2 – node 2 RAC

#Oracle settings
export ORACLE_BASE=/exa/app/oracle
export ORACLE_HOME=/exa/app/oracle/product/11.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=exadb2

[grid@exadb2 ~]$ crsctl status resource | egrep “NAME|STATE”
NAME=ora.DATA.dg
STATE=ONLINE on exadb1, ONLINE on exadb2
NAME=ora.LISTENER.lsnr
STATE=ONLINE on exadb1, ONLINE on exadb2
NAME=ora.LISTENER_SCAN1.lsnr
STATE=ONLINE on exadb2
NAME=ora.MGMT.dg
STATE=ONLINE on exadb1, ONLINE on exadb2
NAME=ora.asm
STATE=ONLINE on exadb1, ONLINE on exadb2
NAME=ora.cvu
STATE=ONLINE on exadb2
NAME=ora.exadb.db
STATE=ONLINE on exadb1, ONLINE on exadb2
NAME=ora.exadb1.vip
STATE=ONLINE on exadb1
NAME=ora.exadb2.vip
STATE=ONLINE on exadb2
NAME=ora.gsd
STATE=OFFLINE, OFFLINE
NAME=ora.net1.network
STATE=ONLINE on exadb1, ONLINE on exadb2
NAME=ora.oc4j
STATE=ONLINE on exadb2
NAME=ora.ons
STATE=ONLINE on exadb1, ONLINE on exadb2
NAME=ora.registry.acfs
STATE=ONLINE on exadb1, ONLINE on exadb2
NAME=ora.scan1.vip
STATE=ONLINE on exadb2

[grid@exadb2 ~]$ ocrcheck
Status of Oracle Cluster Registry is as follows :
Version                  :          3
Total space (kbytes)     :     262120
Used space (kbytes)      :       3084
Available space (kbytes) :     259036
ID                       : 1397037088
Device/File Name         :      +MGMT
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check bypassed due to non-privileged user

[grid@exadb2 ~]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 25 07:36:38 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> SELECT NAME, ALLOCATION_UNIT_SIZE AU, STATE, TYPE, TOTAL_MB FROM V$ASM_DISKGROUP;

NAME                                   AU STATE       TYPE     TOTAL_MB
—————————— ———- ———– —— ———-
DATA                              4194304 MOUNTED     EXTERN       4480
MGMT                              4194304 MOUNTED     EXTERN        896

SQL> SELECT LIBRARY, TOTAL_MB as MB, NAME, FAILGROUP, LABEL FROM V$ASM_DISK ORDER BY FAILGROUP, NAME;

LIBRARY     MB NAME                      FAILGROUP                 LABEL
——– —– ————————- ————————- ————————-
CELL       448 DATA_CD_DISK01_STOCELL1   DATA_CD_DISK01_STOCELL1   DATA_CD_DISK01_STOCELL1
CELL       448 DATA_CD_DISK02_STOCELL1   DATA_CD_DISK02_STOCELL1   DATA_CD_DISK02_STOCELL1
CELL       448 DATA_CD_DISK03_STOCELL1   DATA_CD_DISK03_STOCELL1   DATA_CD_DISK03_STOCELL1
CELL       448 DATA_CD_DISK04_STOCELL1   DATA_CD_DISK04_STOCELL1   DATA_CD_DISK04_STOCELL1
CELL       448 DATA_CD_DISK05_STOCELL1   DATA_CD_DISK05_STOCELL1   DATA_CD_DISK05_STOCELL1
CELL       448 DATA_CD_DISK06_STOCELL1   DATA_CD_DISK06_STOCELL1   DATA_CD_DISK06_STOCELL1
CELL       448 DATA_CD_DISK07_STOCELL1   DATA_CD_DISK07_STOCELL1   DATA_CD_DISK07_STOCELL1
CELL       448 DATA_CD_DISK08_STOCELL1   DATA_CD_DISK08_STOCELL1   DATA_CD_DISK08_STOCELL1
CELL       448 DATA_CD_DISK09_STOCELL1   DATA_CD_DISK09_STOCELL1   DATA_CD_DISK09_STOCELL1
CELL       448 DATA_CD_DISK10_STOCELL1   DATA_CD_DISK10_STOCELL1   DATA_CD_DISK10_STOCELL1
CELL       448 DATA_CD_DISK11_STOCELL1   DATA_CD_DISK11_STOCELL1   DATA_CD_DISK11_STOCELL1
CELL       448 DATA_CD_DISK12_STOCELL1   DATA_CD_DISK12_STOCELL1   DATA_CD_DISK12_STOCELL1

[oracle@exadb2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 25 07:45:15 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> SELECT NAME, DATABASE_ROLE, PLATFORM_NAME FROM V$DATABASE;

NAME DATABASE_ROLE PLATFORM_NAME
——— —————- ——————–
EXADB PRIMARY Linux x86 64-bit

SQL> SELECT INSTANCE_NAME, HOST_NAME, VERSION, DATABASE_STATUS, INSTANCE_ROLE FROM V$INSTANCE;

INSTANCE_NAME HOST_NAME VERSION DATABASE_STATUS INSTANCE_ROLE
—————- ———- ———— —————– ——————
exadb2 exadb2 11.2.0.4.0 ACTIVE PRIMARY_INSTANCE

On the other node

SQL> SELECT INSTANCE_NAME, HOST_NAME, VERSION, DATABASE_STATUS, INSTANCE_ROLE FROM V$INSTANCE;

INSTANCE_NAME HOST_NAME VERSION DATABASE_STATUS INSTANCE_ROLE
—————- ———- ———— —————– ——————
exadb1 exadb1 11.2.0.4.0 ACTIVE PRIMARY_INSTANCE

/*+ esp */

3 VMs for my laptop is too much

Posted on 21/01/2014 by dbaesp

OK.

I built the simulator with 2 storage cells and a 1-node RAC db, but I have no more RAM and CPU to have fun playing with that. So my target environment for OCE Prep should be 1 storage cell node and 1 db node.

Before that I’ll try to build a 2-node RAC over a single storage cell, than I’ll play with second node down.

I’ll post on that as soon as I can!

/*+ esp */

Dba Esp

dba topics by esp

Oracle 12c In Memory Option – Overview

Storage server 12.1.1

Let me add this …

Install OGG on DBFS

Exadata Simulators

Move from unsupported version

DBFS on Exadata (but also not !)

Oracle rdbms 12c – first look (for me)

Exadata Simulator, 2way RAC + 1 storage cell

3 VMs for my laptop is too much