Oracle 12c Flex Cluster Installation in VirtualBox

Installing a flex cluster requires additional configuration, because GNS (Grid Naming Service) is mandatory for this new type of Oracle cluster that was introduced in Oracle Grid Infrastructure 12c. For this specific setup I have used VirtualBox with 5 virtual machines: 4-node cluster and a DHCP/DNS server VM. I don’t want to go into the details of the regular parts of the installation, this post is mainly about the GNS configuration (DHCP and DNS) and the new parts in the installation.

Read more of this post

Oracle Database 12c New Features – Data Redaction

Introduction

Starting with Oracle Database version 12.1.0.1 (and 11.2.0.4!) the Advanced Security option contains a new feature: Data Redaction. Data Redaction provides in-built support for hiding sensitive data on-the-fly in query results on certain conditions.

Read more of this post

Installing Oracle Linux 6 in VirtualBox

Oracle Linux ISOs can be downloaded at: http://edelivery.oracle.com/linux

Virtual Machine Setup

Create a new Virtual Machine:

Read more of this post

Enterprise Manager Cloud Control 12.1.0.4 upgrade and downgrade

This post is about upgrading Enterprise Manager Cloud Control from version 12.1.0.3 to 12.1.0.4 with a fallback plan. As of now, there is no supported method for downgrading Enterprise Manager Cloud Control, so actually this is carried out by making the required backups, and reverting to the older state from them.

Preparations

One of the requirements for upgrading is to copy the emkey to the repository database:

$ export OMS_HOME=/oracle/base/product/mw12103/oms
$ $OMS_HOME/bin/emctl config emkey -copy_to_repos_from_file -repos_host emdb.sequoia.vbox -repos_port 1521 -repos_sid emrep -repos_user sysman -emkey_file $OMS_HOME/sysman/config/emkey.ora

Next export the OMS configuration, which should be part of the regular backups also. The file created by this operation can be used to recreate the OMS from a software-only installation in case the binaries are lost.

$ $OMS_HOME/bin/emctl exportconfig oms

Read more of this post

Adding host and database target in Cloud Control

On the top right, select Setup, Add Target, Add Targets Manually:

Adding target manually

Adding target manually

Select Add Host Targets:

Add Host Targets

Add Host Targets

Click Add, in the new empty line, type the hostname, and select the platform, then click Next:

Add, type hostname and select platform

Add, type hostname and select platform

Specify:

  • Installation Base Directory
  • Instance Directory
Specify installation parameters

Specify installation parameters

Create new Named Credential:

Create new Named Credential

Create new Named Credential

In order for the above to work, on the host emdb.sequoia.vbox, I have changed the following in /etc/sudoers:

  • disable requiretty:
Defaults    !requiretty
  • enable sudo without password for oracle:
oracle          ALL=(ALL)       NOPASSWD: ALL

Summary before deploying the agent:

Summary before deploying the agent

Summary before deploying the agent

Summary after deploying the agent:

Summary after deploying the agent

Summary after deploying the agent

Next step is to discover the targets on the newly added host. On the top right, select Setup, Add Target, Configure Auto Discovery:

Configure Auto Discovery

Configure Auto Discovery

Select All Discovery Modules:

Select All Discovery Modules

Select All Discovery Modules

Select the newly added host, then Configure Discovery Modules:

Select the newly added host, then Configure Discovery Modules

Select the newly added host, then Configure Discovery Modules

Enable the required plugins:

Enable the required plugins

Enable the required plugins

Run Discovery for the newly added host:

Run Discovery for the newly added host

Run Discovery for the newly added host

View the discovered targets (click on the number):

View the discovered targets

View the discovered targets

Select the database instance, then promote the target:

Select the database instance, then promote the target

Select the database instance, then promote the target

Select the database, provide the password for DBSNMP, and select the related listener:

Select the database, provide the password for DBSNMP, and select the related listener

Select the database, provide the password for DBSNMP, and select the related listener

Review before promoting the targets:

Review before promoting the targets

Review before promoting the targets

Targets promoted successfully:

Targets promoted successfully

Targets promoted successfully

INDEX SKIP SCAN not considered

This post was inspired by the below question:

http://dba.stackexchange.com/questions/64880/why-does-the-cbo-not-chose-a-lower-cost-skip-scan-without-stats

Short version: INDEX SKIP SCAN is not considered by the optimizer, when optimizer statistics are missing.

Let’s go with the example from the above post, the database version is 12.1.0.1.

drop table foo purge;

create table foo( bar integer, baz integer, qux char(99), constraint pk_foo primary key (bar, baz) );

insert into foo(bar,baz) 
with g as (select dummy from dual connect by level <= 1000)
select mod(rownum,1000), rownum from g, g;

commit;

Do not collect statistics at this point, as it is a key point for the further. The query we want to run (explain) is:

select * from foo where baz=1

Explaining the query:

explain plan for select * from foo where baz=1;
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1245013993

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    22 |  2794 |   610   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| FOO  |    22 |  2794 |   610   (1)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("BAZ"=1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

The optimizer decided to perform a FTS on foo. Let’s try again with the INDEX_SS hint:

explain plan for select /*+ INDEX_SS(FOO PK_FOO) */ * from foo where baz=1;
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 790150314

----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |    22 |  2794 |   486   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| FOO    |    22 |  2794 |   486   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN                   | PK_FOO |  4067 |       |   421   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - access("BAZ"=1)
       filter("BAZ"=1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

With the INDEX_SS hint, we force an INDEX SKIP SCAN, and the cost is even lower than before. Then why wasn’t the INDEX SKIP SCAN chosen originally? It’s time to trace the optimizer.

alter system flush shared_pool;
alter session set events '10053 trace name context forever, level 1';
explain plan for select * from foo where baz=1;
alter session set events '10053 trace name context forever, off';

Here are the relevant parts from the trace:

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: FOO  Alias: FOO  (NOT ANALYZED)
  #Rows: 164014  #Blks:  2008  AvgRowLen:  100.00  ChainCnt:  0.00
Index Stats::
  Index: PK_FOO  Col#: 1 2    (NOT ANALYZED)
    LVLS: 1  #LB: 25  #DK: 100  LB/K: 1.00  DB/K: 1.00  CLUF: 800.00

Values like “#Rows: 164014″, “#DK: 100″ (Distinct Keys) or “CLUF: 800.00″ (Clustering Factor) are definitely inaccurate, even without checking them. We can also see “(NOT ANALYZED)”, since we didn’t collect statistics on the table and the index.

Access path analysis for FOO
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for FOO[FOO]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE

*** 2014-06-11 21:11:51.049
** Performing dynamic sampling initial checks. **
  Column (#2): BAZ(NUMBER)  NO STATISTICS (using defaults)
    AvgLen: 13 NDV: 5125 Nulls: 0 Density: 0.000195
** Dynamic sampling initial checks returning TRUE (level = 2).
** Dynamic sampling updated index stats.: PK_FOO, blocks=4150
** Dynamic sampling updated table stats.: blocks=2008

*** 2014-06-11 21:11:51.049
** Generated dynamic sampling query:
    query text :
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("FOO") FULL("FOO") NO_PARALLEL_INDEX("FOO") */ 1 AS C1, CASE WHEN "FOO"."BAZ"=1 THEN 1 ELSE 0 END AS C2 FROM "TEST"."FOO" SAMPLE BLOCK (3.137450 , 1) SEED (1) "FOO") SAMPLESUB

Here we can see dynamic sampling kicking in because of the missing statistics.

*** 2014-06-11 21:11:51.053
** Executed dynamic sampling query:
    level : 2
    sample pct. : 3.137450
    actual sample size : 31898
    filtered sample card. : 0
    orig. card. : 164014
    block cnt. table stat. : 2008
    block cnt. for sampling: 2008
    max. sample block cnt. : 64
    sample block cnt. : 63
    min. sel. est. : 0.01000000
** Using dynamic sampling card. : 1016685
** Dynamic sampling updated table card.
** Using single table dynamic sel. est. : 0.00002173
  Table: FOO  Alias: FOO
    Card: Original: 1016685.460317  Rounded: 22  Computed: 22.09  Non Adjusted: 22.09
  Access Path: TableScan
    Cost:  610.10  Resp: 610.10  Degree: 0
      Cost_io: 604.00  Cost_cpu: 237971185
      Resp_io: 604.00  Resp_cpu: 237971185
kkofmx: index filter:"FOO"."BAZ"=1

Above is the result of dynamic sampling, and the cost for FTS. And below is the interesting part. Though INDEX_SKIP_SCAN is mentioned, it is not even considered. The optimizer examined the cost of INDEX FULL SCAN, found it higher than TABLE ACCESS FULL, so it chose the later as the best access path.

SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SKIP_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
  Access Path: index (FullScan)
    Index: PK_FOO
    resc_io: 4216.00  resc_cpu: 30525891
    ix_sel: 1.000000  ix_sel_with_filters: 0.004000
 ***** Logdef predicate Adjustment ******
 Final IO cst 0.00 , CPU cst 50.00
 ***** End Logdef Adjustment ******
    Cost: 4218.09  Resp: 4218.09  Degree: 1
  Best:: AccessPath: TableScan
         Cost: 610.10  Degree: 1  Resp: 610.10  Card: 22.09  Bytes: 0

Let’s check the hidden parameters related to INDEX SKIP SCAN:

select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b 
where a.indx = b.indx
and a.ksppinm like '\_%skip_scan%' escape '\'
order by name;

NAME                           VALUE DESCRIPTION
------------------------------ ----- ----------------------------------------------------------------------
_optimizer_skip_scan_enabled   TRUE  enable/disable index skip scan
_optimizer_skip_scan_guess     FALSE consider index skip scan for predicates with guessed selectivity

According to “_optimizer_skip_scan_enabled”, the INDEX SKIP SCAN is enabled. The second parameter however, disables INDEX SKIP SCAN when the selectivity for the predicate is guessed, which is the default behaviour. Let’s test it.

alter session set "_optimizer_skip_scan_guess" = true;

explain plan for select * from foo where baz=1;
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 790150314

----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |    22 |  2794 |   486   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| FOO    |    22 |  2794 |   486   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN                   | PK_FOO |  4067 |       |   421   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - access("BAZ"=1)
       filter("BAZ"=1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

As expected, the optimizer chose INDEX SKIP SCAN this time, even without statistics (indicated by dynamic sampling).

 

Installing Cloud Control 12c on Oracle Linux 6

This post covers installing Cloud Control 12c (actually 12.1.0.3) on a fresh (with newest updates) Oracle Linux 6.5 system. The repository database (11.2.0.4) resides on another host:

emrep =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = emdb.sequoia.vbox)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = emrep)
    )
  )

The server used for Cloud Control:

  • Hostname: cc.sequoia.vbox
  • OS: Oracle Linux 6.5 64 bit, Minimal install

First start with a few configuration changes, that are not prerequisites, but I usually do them.

  • Disable SELINUX
[root@cc ~]# setenforce 0
[root@cc ~]# vi /etc/selinux/config

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of these two values:
# targeted - Targeted processes are protected,
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
  • Disable iptables
[root@cc ~]# service iptables stop
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
iptables: Flushing firewall rules:                         [  OK  ]
iptables: Unloading modules:                               [  OK  ]

[root@cc ~]# service ip6tables stop
ip6tables: Setting chains to policy ACCEPT: filter         [  OK  ]
ip6tables: Flushing firewall rules:                        [  OK  ]
ip6tables: Unloading modules:                              [  OK  ]

[root@cc ~]# chkconfig iptables off
[root@cc ~]# chkconfig ip6tables off
  • Add the hostname and IP to /etc/hosts
[root@cc ~]# vi /etc/hosts

127.0.0.1       localhost localhost.localdomain localhost4 localhost4.localdomain4
::1             localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.5     cc.sequoia.vbox cc

Now back to Cloud Control.

  • Install the required packages mentioned in the documentation. Some of them are already installed.
[root@cc ~]# yum install make binutils gcc libaio glibc-common libstdc++ libXtst sysstat glibc-devel.i686 glibc-devel.x86_64 glibc
  • For X11 forwarding, additional packages are required, that don’t come with a minimal Linux installation:
yum install xorg-x11-utils xauth
  • Create the group, user and directory used for installation
[root@cc ~]# groupadd -g 54321 oinstall
[root@cc ~]# useradd -g oinstall -u 54321 oracle
[root@cc ~]# passwd oracle

[root@cc ~]# mkdir /oracle/base -p
[root@cc ~]# chown -R oracle:oinstall /oracle
[root@cc ~]# chmod -R 775 /oracle

Time to start runInstaller.

Uncheck “I wish to receive security update …”.

Uncheck

Uncheck “I wish to receive security update …”

Choose Skip for searching for updates.

Choose Skip for searching for updates.

Choose Skip for searching for updates.

As the Oracle Base is /oracle/base, the inventory location is /oracle/oraInventory.

As the Oracle Base is /oracle/base, the inventory location is /oracle/oraInventory

As the Oracle Base is /oracle/base, the inventory location is /oracle/oraInventory

Prerequisite checks should all succeed at this point.

Prerequisite checks should all succeed at this point.

Prerequisite checks should all succeed at this point.

Creating a new Enterprise Manager System (Simple).

Creating a new Enterprise Manager System (Simple).

Creating a new Enterprise Manager System (Simple).

Specify Middleware Home, Agent Home, and OMS hostname.

Specify Middleware Home, Agent Home, and OMS hostname.

Specify Middleware Home, Agent Home, and OMS hostname.

A little explanation for this: I prefer to name Oracle homes in a way that just by looking at name I already know what is inside them (for example db11204ee instead of 11.2.0/dbhome_1). With Cloud Control 12c, upgrades are out-of-place, so there will be another Middleware Home used for the new release and it is “safe” to also specify the current version for the name of Middleware Home. However, the same Agent Base is used by the new releases also when upgrading, so it would not be fortunate to name the agent base as agent12103, since the newer agents will be installed also in this directory, thus agent12 is just enough.

Passwords, repository database and software library.

Passwords, repository database and software library.

Passwords, repository database and software library.

The Software Library works the same way as the Agent Base, same path used by newer releases when upgrading.

Repository database related errors…

Repository database related errors.

Repository database related errors…

… and warnings.

... and warnings.

… and warnings.

Some of them the installer fixes automatically, the others should be fixed manually before continuing.

A summary before the installation begins.

A summary before the installation begins.

A summary before the installation begins.

The installation process begins…

The installation process begins...

The installation process begins…

Run the root scripts when prompted.

Run the root scripts when prompted.

Run the root scripts when prompted.

Installation finished.

Installation finished.

Installation finished.

Log in to Cloud Control:

Cloud Control Login Page

Read and accept the license agreement:

Read and accept the license agreement.

Read and accept the license agreement.

Default home page:

Cloud Control Enterprise Manager Overview

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: