Exadata Exafusion!

We are looking into using the Exafusion feature on an Exadata X5 full rack and will start to test in non-production first.

This is well documented in MOS Oracle support note: EXAFUSION - FAQ (Doc ID 2037900.1)

Exafusion is Direct-to-Wire protocol allows database processes to read and send Oracle Real Applications Cluster (Oracle RAC) messages directly over the Infiniband network bypassing the overhead of entering the OS kernel, and running the normal networking software stack. This improves the response time and scalability of the Oracle RAC environment on Oracle Exadata Database Machine.  Data is transferred directly from user space to the Infiniband network, leading to reduced CPU utilization and better scale-out performance. Exafusion is especially useful for OLTP applications because per message overhead is particularly apparent in small OLTP messages.

Exafusion helps small messages by bypassing OS network layer overhead and is ideal for this purpose.


Requirements to use Exafusion -

Oracle Exadata Storage Server Software release 12.1.2.1.1 or later
Oracle Database software Release 12.1.0.2.0 BP6 or later
Mellanox ConnectX-2 and ConnectX-3 Host Channel Adapters (HCAs) are required.
Oracle Enterprise Kernel 2 Quarterly Update 5 (UEK2QU5) kernels (2.6.39-400.2nn) or later are required.

Enabling Exafusion -

Exafusion is disabled by default.
To enable Exafusion, set the EXAFUSION_ENABLED initialization parameter to 1.
To disable Exafusion, set the EXAFUSION_ENABLED initialization parameter to 0.
This parameter cannot be set dynamically. It must be set before instance startup.
All of the instances in an Oracle RAC cluster must enable this parameter, or all of the instances in an Oracle RAC cluster must disable the parameter.

12c Dataguard broker setup error ORA-16698

The Oracle Data Guard broker is a great management framework that automates and centralizes the creation, maintenance, and monitoring of Oracle Data Guard configurations.

Recently for a Primary Oracle RAC database I was in the process of setting up a RAC Dataguard broker in Oracle 12c - 12.1.0.2 on the latest Exadata X5-2 and encountered an error ORA-16698. The setup steps and error details and solution/workaround I used is below.

On the Primary RAC database ensure the broker file is on ASM and turn on the broker.

-- Primary

alter system set dg_broker_config_file1 = '+DATA/DROID/DATAFILE/dg1_DROID.dat' scope=both sid='*';
alter system set dg_broker_config_file2 = '+DATA/DROID/DATAFILE/dg2_DROID.dat' scope=both sid='*';

alter system set dg_broker_start=true scope=both sid='*';

On the Standby RAC database ensure the broker file is on ASM as well and turn on the broker.

-- Standby

alter system set dg_broker_config_file1 = '+DATA/DROIDDG/DATAFILE/dg1_DROIDDG.dat' scope=both sid='*';
alter system set dg_broker_config_file2 = '+DATA/DROIDDG/DATAFILE/dg2_DROIDDG.dat' scope=both sid='*';

alter system set dg_broker_start=true scope=both sid='*';

 

On the primary I invoked the dataguard broker and encountered the error when setting up the configuration for the standby database.

-- Back to Primary


DGMGRL> connect sys
Password:
Connected as SYSDG.
DGMGRL>  CREATE CONFIGURATION 'DROIDDR' AS PRIMARY DATABASE IS 'DROID' CONNECT IDENTIFIER IS DROID;
Configuration "DROIDDR" created with primary database "DROID"
DGMGRL> ADD DATABASE 'DROIDDG' AS CONNECT IDENTIFIER IS DROIDDG;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Failed.

Save the log archive destination settings from both the Primary and Standby databases then remove the configuration.

 
-- PRIMARY log_archive_dest_2 setting
log_archive_dest_2='SERVICE=DROIDDG ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DROIDDG'

-- STANDBY log_archive_dest_2 setting
log_archive_dest_2='SERVICE=DROID ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DROID'

-- Remove the Dataguard configuration

DGMGRL> remove configuration;
Removed configuration


 Set the log_archive_dest_2 settings from both the Primary and Standby databases to be nothing.

alter system set log_archive_dest_2='' scope=both sid='*';
  

Disable then Enable the broker parameter on both the Primary and Standby databases.

-- Primary
alter system set dg_broker_start=false scope=both sid='*';
alter system set dg_broker_start=true scope=both sid='*';

-- Standby
alter system set dg_broker_start=false scope=both sid='*';
alter system set dg_broker_start=true scope=both sid='*';


On the Primary database create the broker configuration for the Primary and Standby database and this time it should work fine with no issues since the log archive destination 2 setting is not set, this is the workaround/solution.


[oracle@okx1pdbadm06 ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected as SYSDG.
DGMGRL>
DGMGRL>  CREATE CONFIGURATION 'DROIDDR' AS PRIMARY DATABASE IS 'DROID' CONNECT IDENTIFIER IS DROID;
Configuration "DROIDDR" created with primary database "DROID"
DGMGRL> ADD DATABASE 'DROIDDG' AS CONNECT IDENTIFIER IS DROIDDG;

 Revert the original settings back for the log_archive_dest_2 settings from both Primary and Standby databases.


-- PRIMARY log_archive_dest_2
alter system set log_archive_dest_2='SERVICE=DROIDDG ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DROIDDG' sid='*' scope=both;

-- STANDBY log_archive_dest_2
alter system set log_archive_dest_2='SERVICE=DROID ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DROID' sid='*' scope=both;

  

Enable the broker configuration and we now have a successful 12c RAC dataguard broker configuration enabled on Exadata!


[oracle@okx1pdbadm06 ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected as SYSDG.
DGMGRL>

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - DROIDDR

  Protection Mode: MaxPerformance
  Members:
  DROID   - Primary database
    DROIDDG - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 315 seconds ago)




    

ORA-00304: requested INSTANCE_NUMBER is busy

I was working on verifying that all 12c Dataguard instances were running on an Exadata X5 full rack system and I came across one instance that was not running. I noticed this when I saw the /etc/oratab entry and did not see the corresponding instance running on the database compute server.

I manually tried to bring up the instance from node 3 and encountered the error below.

node3-DR> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 16 18:30:15 2015

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

Connected to an idle instance.

SQL> startup nomount;

ORA-00304: requested INSTANCE_NUMBER is busy

SQL> exit

Disconnected

I then looked at the instance alert log and saw the following:

...

Wed Dec 16 18:30:22 2015

ASMB started with pid=39, OS id=59731

Starting background process MMON

Starting background process MMNL

Wed Dec 16 18:30:22 2015

MMON started with pid=40, OS id=59733

Wed Dec 16 18:30:22 2015

MMNL started with pid=41, OS id=59735

Wed Dec 16 18:30:22 2015

NOTE: ASMB registering with ASM instance as Standard client 0xffffffffffffffff (reg:1438891928) (new connection)

NOTE: ASMB connected to ASM instance +ASM3 osid: 59737 (Flex mode; client id 0xffffffffffffffff)

NOTE: initiating MARK startup

Starting background process MARK

Wed Dec 16 18:30:22 2015

MARK started with pid=42, OS id=59741

Wed Dec 16 18:30:22 2015

NOTE: MARK has subscribed

High Throughput Write functionality enabled

Wed Dec 16 18:30:34 2015

USER (ospid: 59191): terminating the instance due to error 304

Wed Dec 16 18:30:35 2015

Instance terminated by USER, pid = 59191

 The error number was 304 that was in the output from sqlplus and from the alert log as well, then the next thing I did was naturally check google and oracle support to see if I could find something that matched my scenario and could not find something exact. 

Then the next thing that came to mind was to check gv$instance to see where the Primary RAC and Standby RAC standby database were running from.

# Primary
  INST_ID INSTANCE_NUMBER INSTANCE_NAME     HOST_NAME      STATUS
---------- --------------- ---------------- ------------ ------------
         1               1     PROD1            node01        OPEN
         3               3     PROD3            node08        OPEN
         2               2     PROD2            node07        OPEN

# Standby
   INST_ID INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME      STATUS
---------- --------------- ---------------- ------------ ------------
         1               1 DG1               node01        MOUNTED
         3               3 DG3               node08        MOUNTED
         2               2 DG2               node07        MOUNTED

I then realized from node 3 that the oratab entry for instance 3 was not correct it was already running from node node08!

Furthermore I also verified the RAC configuration via srvctl.

node3-DR>srvctl config database -d DG3

Database unique name: DG

Database name: PROD

Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_1

Oracle user: oracle

Spfile: +RECOC1/DG/PARAMETERFILE/spfile.4297.894386377

Password file:

Domain:

Start options: mount

Stop options: immediate

Database role: PHYSICAL_STANDBY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATAC1,RECOC1

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: dba

OSOPER group: dba

Database instances: DG1,DG2,DG3

Configured nodes: node01,node07,node08

Database is administrator managed

The configured nodes for the Dataguard Standby database is supposed to be running from node01, node07 and node08 it is not configured to run on node03.

Once I confirmed this I simply removed the oratab entry to prevent any future further confusion. Moral of the story please only leave oratab entries intact for real instances that need to run from the node. ☺

How to Rename a 12c Pluggable Database

The following steps is an example for renaming a Pluggable database (PDB) TESTPDB to NEWPDB and its associated service.

SQL> alter pluggable database TESTPDB close instances=all;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE TESTPDB OPEN RESTRICTED FORCE;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO NEWPDB;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE NEWPDB close instances=all;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE NEWPDB open instances=all;

Pluggable database altered.

SQL>


SQL> connect / as sysdba
Connected.

SQL> select name from v$pdbs;

NAME
------------------------------
PDB$SEED
NEWPDB


Alert log.

alter pluggable database TESTPDB close instances=all
Tue Sep 15 13:41:05 2015
ALTER SYSTEM: Flushing buffer cache inst=1 container=3 local
Tue Sep 15 13:41:16 2015
Pluggable database TESTPDB closed
Completed: alter pluggable database TESTPDB close instances=all
ALTER PLUGGABLE DATABASE OPEN RESTRICTED FORCE
Tue Sep 15 13:41:23 2015
This instance was first to open pluggable database TESTPDB (container=3)
Database Characterset for TESTPDB is WE8ISO8859P1
Opening pdb TESTPDB (3) with no Resource Manager plan active
Pluggable database TESTPDB opened read write
Completed: ALTER PLUGGABLE DATABASE OPEN RESTRICTED FORCE
ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO NEWPDB
Tue Sep 15 13:41:38 2015
ALTER SYSTEM: Flushing buffer cache inst=1 container=3 local
Tue Sep 15 13:41:50 2015
Pluggable database TESTPDB closed
This instance was first to open pluggable database TESTPDB (container=3)
Database Characterset for TESTPDB is WE8ISO8859P1
Tue Sep 15 13:41:51 2015
Successfully created internal service NEWPDB at open
Tue Sep 15 13:41:51 2015
ALTER SYSTEM: Flushing buffer cache inst=1 container=3 local
This instance was first to open pluggable database NEWPDB (container=3)
Database Characterset for NEWPDB is WE8ISO8859P1
Opening pdb NEWPDB (3) with no Resource Manager plan active
Pluggable database NEWPDB opened read write
Completed: ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO NEWPDB
Tue Sep 15 14:25:33 2015
ALTER PLUGGABLE DATABASE NEWPDB close instances=all
Tue Sep 15 14:25:34 2015
ALTER SYSTEM: Flushing buffer cache inst=1 container=3 local
Tue Sep 15 14:25:47 2015
Pluggable database NEWPDB closed
Completed: ALTER PLUGGABLE DATABASE NEWPDB close instances=all
ALTER PLUGGABLE DATABASE NEWPDB open instances=all
Tue Sep 15 14:25:58 2015
This instance was first to open pluggable database NEWPDB (container=3)
Database Characterset for NEWPDB is WE8ISO8859P1
Opening pdb NEWPDB (3) with no Resource Manager plan active
Pluggable database NEWPDB opened read write
Completed: ALTER PLUGGABLE DATABASE NEWPDB open instances=all

How to Add or Configure Memory on Exadata

On the Exadata X2 the following are the four valid memory configurations. Please note on each database compute node motherboard has 18 memory sockets or slots for the Memory DIMMS.

1.    (12 x 8GB DIMMS, as shipped from Oracle) - 96GB

2.    (12 x 16GB DIMMS leaving the 6 plastic inserts in which is simply replacing the previous 12x 8GB DIMMS to 16GB DIMMS) - 192GB

3.    (12 x 8 GB and 6 x 16GB) - 192GB

4.    (18 x 16GB DIMMS) - 288GB, The Maximum for Exadata X2 compute node.

I came across a client that did not have a supported memory configuration in their Exadata X2-2.

They had (6 x 8GB DIMMS, 12 x 16GB DIMMS) => 240GB, which is not supported by Oracle on the Exadata X2-2 hardware as a valid memory combination.

That configuration may lead to system performance and may not achieve your full Exadata operational ability, and furthermore there is a potential, which may lead to a system kernel bug.

The steps provided here were used to put the correct support memory configuration of 192GB.

These changes can be staged prior just before adding the memory to the each of the database compute nodes. Once the parameters are staged then the memory can be added by Oracle field support and then the nodes can be rebooted for the changes to take effect.

As per note Oracle Support note:

How to Increase Database Allocated Memory on Exadata Database Server (Doc ID 1346747.1)

The Best Practice for Exadata is to use hugepages.  The formula used to set the huge pages is usually 50-75% of the total physical memory on the server. You can allocate additional memory to the SGA buffer cache.  Do not exceed 50% of RAM if you expect a large connection load, or large PGA usage.  Perhaps up to 75% of RAM if you monitor PGA usage and connection load very closely. The memory allocated to huge pages is always pinned and is never paged or swapped out.

In our setup we will set the hugepages to be 60% of the total physical memory.

Please note an OS page size on Linux is 2M or 2048KB.

·      60% of 192GB is 115.2GB

·      Convert 115.2GB to KB => 115.2 * 1024 to get # in MB (117964.8) then again multiply by 1024 to get value in KB 120795955.2

·      Divide 120795955.2 KB by the Linux pagesize which is 2048KB, the value will be 58982 pages but we round down to the nearest whole number to set the number of hugepages.

1.    Save backup copy of the /etc/sysctl.conf file then update the kernel file /etc/sysctl.conf , set  vm.nr_hugepages = 58982

2.    Set memlock kernel parameter in KB this value should be less then the total physical memory on the server. This value represents the maximum locked in memory space on the OS. In our example we set memlock to about 191GB. Save a backup copy of the /etc/security/limits.conf then update the file.

/etc/security/limits.conf

oracle     soft    memlock      200278016

oracle     hard    memlock     200278016

3.    Set shmall memory pages to be less than the total physical memory and larger than the sum of all of SGAs. On the server in our setup the sum of all SGAs will be 115.2GB or less so we will use 115.2GB as our number to calculate shmall.

Convert 115.2GB to Bytes (123695058124.8)

Divide by the 4KB OS page size not to be confused with the hugepage size.

123695058124.8/4096 => 30198988.8

4. Save backup copy of the /etc/sysctl.conf then update the file

/etc/sysctl.conf set - kernel.shmall = 30198988

Increase the SGA and PGA parameters across the database(s) if needed for performance. Please note the total sum of the sga_target initialization parameter value across all instances on the node should be less than or equal to the value of the huge pages of 115.2GB that we are using in this example.

PGA is not part of the hugepages and can be accordingly however I do recommend leaving at least 5GB free for the OS.

Please note memory_target should not be set which is part of AMM (Automatic Memory Management) as it is not compatible with huge pages. ASMM (Automatic Shared Memory Management) should be used instead. The sga_target parameter is compatible with ASMM.

The Oracle Field Engineer will make changes to the Database compute nodes and add or adjust the memory configuration and then startup the compute nodes once complete.

Run the free –g command to confirm the memory is 192GB it may say 188GB due to motherboard settings.

 To verify hugepages has been set correctly on the OS please run the following command on each node. The values below are just an example and do not reflect the settings we have.

$ cat /proc/meminfo|grep -i HugePages_Total

HugePages_Total:   58982

For each database please verify in the database alert log hugepages is being used. The values below is an example from the alert log file for the instance.

Starting ORACLE instance (normal)

************************ Large Pages Information *******************

Per process system memlock (soft) limit = UNLIMITED

Total Shared Global Region in Large Pages = 40 GB (100%)

...

 

New Exadata book released!

I am excited about the new book "Oracle Exadata Expert's Handbook" that has been finally released!

Amazon Exadata book link

I helped author one chapter in the book and also assisted on one as well.

I am very thankful to be part of a strong team of authors Tariq Farooq, Charles Kim, Nitin Vengurlekar, Sridhar Avantsa, Guy Harrison and Syed Jaffar Hussain. Thanks to Charles Kim for inviting me to help on this exciting project.

I hope everyone will benefit from the knowledge in the book, there is plenty of good content to read and learn from.

DBaaS: Deploying Databasess quickly from OEM @ Dallas Oracle Users Group

I will be speaking at the Dallas Oracle Users Group - DOUG on July 23, 2015 5pm-7:15pm. The topic will be "DBaaS: Deploying Databases quickly from OEM @ Dallas Oracle Users Group"

Please come and learn how to setup your Cloud infrastructure in Oracle Enterprise Manager 12c and deploy databases to it with a few clicks using Database as a Service (DBaaS)! More information at the below link.

http://viscosityna.com/event/dbaas-deploying-dbs-quickly-from-oem-dallas-oracle-users-group/