Oracle Database In-Memory can be installed and configured on all Exalytics Machines running Oracle Exalytics Release 2.0 or later, and used for all analytics use cases. Oracle Database In-Memory is easily deployed under any existing application that is compatible with Oracle Database 12c. No application changes are required.
Note:
Oracle does not offer out of the box High Availability/Disaster Recovery/Backup, or other features, related to the management and availability of Oracle Database In-Memory on an Exalytics Machine. For information on these and other features, see the Oracle Database 12c Release 1 (12.1) documentation at the following location:
This section consists of the following topics:
Parent topic: Installing Software on an Exalytics Machine
The following Oracle Database In-Memory configurations are certified to run on Oracle Exalytics:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64 bit Production with In-Memory Option.
Database type of DATA_WAREHOUSE.
Data warehouse applications. Online Transaction Processing (OLTP) applications are not supported.
Storage Option of File System. Oracle Automatic Storage Management (ASM) is not supported.
Single Instance of Oracle Database with In-Memory Option. Oracle Real Application Cluster (RAC) and Oracle Grid Infrastructure are not supported.
The following use cases are supported on Oracle Database In-Memory on Oracle Exalytics:
You can use the following aggregates to accelerate your queries:
OBIEE Generated or Managed Aggregates
You can use Summary Advisor to analyze the Business Intelligence workload and generate recommendations.
You can use Aggregate Persistence Wizard to define aggregates on a logical model in the RPD file.
You can execute the aggregate script (recommended by Summary Advisor or manually defined aggregates) using Oracle Database In-Memory on Oracle Exalytics as target.
Database Aggregates
You can populate your aggregates by defining and coding your aggregates using any Extract, Transform, Load (ETL) or Extract, Load, Transform (ELT) tools certified on Oracle Database In-Memory. To do so, wire the aggregate tables manually in the RPD file to leverage BI Server aggregate navigation.
Parent topic: Supported Oracle Database In-Memory Use Cases
You can choose the entire Data warehouse reporting tables or a subset of the reporting tables in Oracle Database In-Memory on Exalytics. To do so, build (code) the data using any Extract, Transform, Load (ETL) or Extract, Load, Transform (ELT) tools certified on Oracle Database In-Memory.
Parent topic: Supported Oracle Database In-Memory Use Cases
You can use Logical Standby or use GoldenGate to replicate data from your Online Transaction Processing (OLTP) database and use the database for Operational reporting.
Parent topic: Supported Oracle Database In-Memory Use Cases
You can create Enterprise Performance Management (EPM) repository and create Metadata Services (MDS) and Business Intelligence Platform (BIPLATFORM) database schemas using the Repository Creation Utility (RCU).
Parent topic: Supported Oracle Database In-Memory Use Cases
Oracle recommends a maximum size of 830 GB for an Oracle Database installed on an X2-4 Exalytics Machine with 1 TB of RAM, and a maximum size of 1 TB for an Oracle Database installed on an X3-4, X4-4, X5-4, or X6-4 Exalytics Machine with 2 TB of RAM.
Note:
The following procedure assumes installing an Oracle Database In-Memory of 830 GB on an X2-4 Exalytics Machine with 1 TB of RAM.
This section consists of the following topics:
Before you install Oracle Database In-Memory for Exalytics, verify that the kernel parameters shown in Table 6-1 are set to values greater than or equal to the minimum values shown.
Note:
If the current value of any parameter is higher than the value shown in Table 6-1, then do not change the value of that parameter.
Table 6-1 Minimum kernel Parameter Values
kernel parameters | Minimum Values | File |
---|---|---|
semms1 |
250 |
/proc/sys/kernel/sem |
semmns |
32000 |
/proc/sys/kernel/sem |
semopm |
100 |
/proc/sys/kernel/sem |
semmni |
128 |
/proc/sys/kernel/sem |
shmall |
2097152 |
/proc/sys/kernel/shmall |
shmmax |
Minimum: 536870912 Maximum: A value that is 1byte less that the physical memory. Recommended: More than half the physical memory. See My Oracle Support Note 567506.1 for additional information about configuring shmmax. |
/proc/sys/kernel/shmmax |
shmmni |
4096 |
/proc/sys/kernel/shmmni |
file-max |
6815744 |
/proc/sys/fs/file-max |
ip_local_port range |
Minimum: 9000 Maximum: 65500 |
/proc/sys/net/ipv4/ip_local_ port_range |
rmem_default |
262144 |
/proc/sys/net/core/rmem |
rmem_max |
4194304 |
/proc/sys/net/core/rmem_max |
wmem_default |
262144 |
/proc/sys/net/core/wmem_ default |
wmem_max |
1048576 |
/proc/sys/net/core/wmem_max |
aio-max-nr |
1048576 |
/proc/sys/fs/aio-max-nr |
If the kernel parameters are less than the minimum values shown in Table 6-1, you must update the kernel parameters.
To update kernel parameters:
Edit the /etc/sysctl.conf file to update the kernel parameters.
After updating the kernel parameters, enter the following command to apply the updated kernel values:
# /sbin/sysctl -p
Review the output from this command to verify that the updated kernel values are correct.
You can install Oracle Database In-Memory manually or use the Exalytics Domain Management Utility.
This section consists of the following topics:
You can also install Oracle Database In-Memory using the Exalytics Domain Management Utility.
Prerequisites for Installing Oracle Database In-Memory Using the Exalytics Domain Management Utility
Before you use the Exalytics Domain Management Utility to install Oracle Database In-Memory, you must:
Familiarize yourself with the Exalytics Domain Management Utility. See Installing the Software by Using the Exalytics Domain Management Utility.
Ensure that you have upgraded to Oracle Exalytics Release 2.
Create a password on the operating system. See Creating a Password on the Operating System.
Create an input properties file. See Creating an Input Properties File.
Verify user permissions to the /u01 Directory. See Verifying User Permissions to the /u01 Directory.
Create an Installers directory structure such as the following for staging the Oracle Database In-Memory files:
/home/oracle/Installers/oracledb
To install Oracle Database In-Memory using the Exalytics Domain Management Utility:
Parent topic: Installing Oracle Database In-Memory
To manually install Oracle Database In-Memory:
Navigate to https://edelivery.oracle.com.
In the Select a Product Pack list, select Oracle Database.
In the Platform list, select Linux x86-64.
Click Go.
Select Oracle Database 12c Release 1 (12.1.0.2.0) Media Pack for Linux x86-64.
Download Oracle Database 12c Release 1 Client (12.1.0.2.0) for Microsoft Windows x64 (64-bit).
Note:
Download and install the Oracle Database 12c Release 1 Client (12.1.0.2.0) for Microsoft Windows x64 (64-bit) on a Windows machine and not on the Exalytics Machine. You use the Oracle Database 12c Client for Windows to install SQL Plus, create database schemas, and so on, on a Windows machine.
Download Oracle Database 12c Release 1 (12.1.0.2.0) (Part 1 of 2) and Oracle Database 12c Release 1 (12.1.0.2.0) (Part 2 of 2).
The zip files contains the following disks:
linuxamd64_12102_database_1of 2.zip
linuxamd64_12102_database_2of 2.zip
Stage the Oracle database installer disks in /home/oracle/EXALYTICS_INSTALLERS/12cdb/.
Navigate to /home/oracle/EXALYTICS_INSTALLERS/12cdb/database/.
Enter the following command to start Oracle Universal Installer:
$ ./runInstaller
On the Configure Security Updates screen, perform the following tasks:
In the Email field, enter your e-mail address, preferably your My Oracle Support e-mail address and user name.
Optional: Select I wish to receive security updates via My Oracle Support to receive security updates. If you choose to receive security updates via My Oracle Support, enter your My Oracle Support password in the My Oracle Support Password field.
Click Next.
On the Select Installation Option screen, select Create and configure a database, then click Next.
On the System Class screen, select Server Class, then click Next.
On the Grid Installation Options screen, select Single instance database installation, then click Next.
On the Select Install Type screen, select Advanced Installation, then click Next.
On the Select Product Languages screen, select a language, then click Next.
On the Select Database Edition screen, select Enterprise Edition (6.4 GB), then click Next.
On the Specify Installation Location screen, perform the following tasks:
In the Oracle base field, enter the location of where you want to install the Oracle database.
In the Software location field, enter the location of where you want to install the Oracle database software files.
Note:
Oracle recommends you install the database and database software files in the /u01 directory.
Click Next.
On the Create Inventory screen, perform the following tasks:
In the Inventory Directory field, specify the full path of the Oracle Inventory directory.
In the orainventory Group name list, select oinstall.
Click Next.
On the Select Configuration Type screen, select Data Warehousing, then click Next.
On the Specify Database Identifiers screen, perform the following actions:
In the Global database name field, enter a unique global database name.
Oracle system identifier (SID) field, enter a unique database identifier.
Deselect Create as Container database.
Click Next.
On the Specify Configuration Options screen, perform the following tasks:
Select the Memory tab, and allocate memory to the database.
Note:
The memory allocated to the database cannot exceed the total RAM memory of the Exalytics Machine.
For an X2-4 machine, total RAM memory is 1 TB.
For an X3-4, X4-4, X5-4, or X6-4 machine, total RAM memory is 2 TB.
Select the Character sets tab, and then select Use Unicode (AL32UTF8).
Select the Sample schemas tab, and then select Create database with sample schemas.
Click Next.
Optional: On the Specify Management Options screen, specify details of the Oracle Enterprise Manager 12c Cloud Control configuration to manage your database, then click Next.
On the Specify Recovery Options screen, perform the following tasks:
Select Enable Recovery.
Select File System, and in the Recovery area location field, enter the location from where you can, if needed, recover the database.
Click Next.
On the Specify Schema Passwords screen, specify passwords for the schemas, and then click Next.
Note:
You can choose to use different passwords for each account or use a single password for all the accounts.
On the Privileged Operating System groups screen, select oinstall to grant system privileges to the "oinstall" user group for each Operating System group, and then select Next.
On the Perform Prerequisite Checks screen, confirm that all the prerequisite checks are successful, then click Next.
On the Summary screen, review the information, then click Install.
The Install Product screen displays the progress of the database installation.
On the Install Product screen, when prompted, perform the following tasks:
Execute the configuration scripts as a "root" user by following the directions on the Execute Configuration scripts screen.
Open the Database Configuration Assistant screen and verify the database information. When the installation process is complete, make a note of the EM Database Express URL.
Note:
If required, on the Database Configuration Assistant screen, click Password Management to unlock any accounts.
Click OK.
On the Finish screen, confirm that the installation of Oracle Database was successful, then click Close.
For more information, see Oracle Database Quick Installation Guide 12c Release 1 (12.1) for Linux x86-64 at the following location:
Postinstallation Tasks
Perform the following postinstallation tasks:
Setting Environment Variables
You set the ORACLE_HOME and ORACLE_SID environment variables.
To set ORACLE_HOME and ORACLE_SID environment variables:
Log on as a root user.
Enter the following commands to set ORACLE_HOME and ORACLE_SID environment variables:
$ export ORACLE_HOME=/u01/
<location of ORACLE_HOME>
$ export ORACLE_SID=
<name of the unique system identifier>
Configuring Oracle Database In-Memory
This section consists of the following topics:
Setting the INMEMORY_SIZE Parameter
Confirm the INMEMORY_SIZE parameter is set to zero.
To check INMEMORY_SIZE parameter:
Navigate to Oracle _HOME/ bin folder, by entering the following command:
$ cd $ORACLE_HOME/bin
Connect to Oracle Database SQLPlus client as SYSDBA, by entering the following command:
$ ./sqlplus sys/
<password>
AS SYSDBA
The following output is displayed:
SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 1 17:56:50 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL>
Check the INMEMORY_SIZE parameter, by entering the following command:
SQL> show parameter INMEMORY_SIZE
The following parameter is displayed:
NAME TYPE VALUE inmemory_size big integer 0
Setting the IN_MEMORY_CLAUSE_DEFAULT Parameter
You set the IN_MEMORY_CLAUSE_DEFAULT parameter so that any tables created subsequently are automatically retained in memory.
To set the INMEMORY_CLAUSE_DEFAULT parameter:
Set the IN_MEMORY_CLAUSE_DEFAULT parameter, by entering the following command:
SQL> ALTER SYSTEM SET INMEMORY_CLAUSE_DEFAULT = 'INMEMORY MEMCOMPRESS FOR QUERY LOW PRIORITY HIGH' scope = BOTH
The following output is displayed:
System altered. SQL>
To enable the INMEMORY_CLAUSE_DEFAULT parameter, you must shut down and restart the database:
To shutdown the database, enter the following command:
SQL> shutdown abort
The following output is displayed:
ORACLE instance shut down.
To restart the database, enter the following command:
SQL> startup
The following output is displayed:
Total System Global Area 6.5445E+11 bytes Fixed Size 7697568 bytes Variable Size 2.1475E+10 bytes Database Buffers 1.1704E+11 bytes Redo Buffers 529170432 bytes In-Memory Area 5.1540E+11 bytes Database mounted. Database opened. SQL>
Verify that the INMEMORY_CLAUSE_DEFAULT parameter is set, by entering the following command:
SQL> show parameter INMEMORY_CLAUSE_DEFAULT;
The following output is displayed:
NAME TYPE VALUE inmemory_clause_default string INMEMORY MEMCOMPRESS FOR QUERY LOW PRIORITY HIGH SQL>
Calculating and Setting the INMEMORY_SIZE Parameter
Note that since the INMEMORY_SIZE parameter is set to zero, Database In-Memory is not enabled. To enable In-Memory option, you must set the INMEMORY_SIZE parameter.
You set up INMEMORY_SIZE parameter based on the size of the Oracle Database you created (830 GB). Oracle recommends that INMEMORY_SIZE parameter not be greater than 60% of the Oracle Database size.
Note:
When setting your INMEMORY_SIZE parameter, ensure that the SGA_TARGET parameter is 110% of the INMEMORY_SIZE parameter and the PGA_AGGREGATE parameter is 50% of the INMEMORY_SIZE parameter.
For example, if you set the INMEMORY_SIZE parameter to 400 GB, ensure that the SGA_TARGET parameter is 440 GB and the PGA_AGGREGATE parameter is 200 GB.
Before setting the INMEMORY_SIZE parameter, check the SGA_TARGET and PGA_AGGREGATE_TARGET size parameters, by entering the following commands:
SQL> show parameter SGA_TARGET;
SQL> show parameter PGA_AGGREGATE_TARGET;
Note:
Note the value of the SGA_TARGET parameter. The INMEMORY_SIZE parameter cannot be greater than this value.
To set INMEMORY_SIZE parameter:
To set the INMEMORY_SIZE parameter to 480 GB (60% of 830 GB), enter the following command:
SQL> ALTER SYSTEM SET INMEMORY_SIZE = 480G SCOPE = SPFILE;
The following output is displayed:
System altered. SQL>
To enable the INMEMORY_SIZE parameter, you must stop and restart the Oracle database.
To stop the database, enter the following command:
SQL> shutdown abort;
The following output is displayed:
ORACLE instance shut down.
To restart the database, enter the following command:
SQL> startup
The following output is displayed:
ORACLE instance started. Total System Global Area 6.5445E+11 bytes Fixed Size 7697568 bytes Variable Size 2.1475E+10 bytes Database Buffers 1.1704E+11 bytes Redo Buffers 529170432 bytes In-Memory Area 5.1540E+11 bytes Database mounted. Database opened. SQL>
To check the INMEMORY_SIZE setting, enter the following command:
SQL> show parameter INMEMORY_SIZE
The following output is displayed:
NAME TYPE VALUE inmemory_size big integer 480G SQL>
To check the In-Memory Area size, enter the following command:
SQL> select name , value from v$sga;
The following output is displayed:
NAME VALUE Fixed Size 7697568 Variable Size 2.1475E+10 Database Buffers 1.1704E+11 Redo Buffers 529170432 In-Memory Area 5.1540E+11 SQL>
Parent topic: Installing Oracle Database In-Memory