This assumes that you’re in an environment with TDE already enabled (such as in Oracle Cloud at Customer).
When you clone a database you have to enter the encryption password – you may want to avoid doing this if you are executing this from scripts as hardcoded values will make them less general purpose.
SQL> create pluggable database tryout from mpdb; create pluggable database tryout from mpdb * ERROR at line 1: ORA-46697: Keystore password required.
Create a directory on all nodes of the cluster to hold your keystore as oracle – This directory should be available to all nodes in the cluster, and should be under WALLET_ROOT
Locate WALLET_ROOT on your system
SQL> show parameter wallet_root
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
wallet_root string /var/opt/oracle/dbaas_acfs/MEL
ECB/wallet_root
Create a directory called ‘tde_seps’ under WALLET_ROOT. This is a shared filesystem on EXACC so no additional work required to make it accessible to all instances.
Once the DB has restarted create the external wallet and give the password for your DB
SQL> Administer key management add secret 'ATPwelcome--1234' for client 'TDE_WALLET' to auto_login keystore '/var/opt/oracle/dbaas_acfs/MELECB/wallet_root/tde_seps' ;
The DBMS_CLOUD package was developed to allow the Autonomous Database access to Object Storage for data loading, external tables and migration using data pump. The functionality has been extended to other types of Oracle Databases in the cloud from release 19.9, and it is hoped, will actually become built in.
Create a schema owning the DBMS_CLOUD package and install the DBMS_CLOUD code in CDB and all PDBs
requires CDB architecture
must be owned by the C##Cloud$SERVICE for future compatibility.
Create a script to run the install setup
@$ORACLE_HOME/rdbms/admin/sqlsessstart.sql set verify off -- you must not change the owner of the functionality to avoid future issues define username='C##CLOUD$SERVICE' create user &username no authentication account lock; REM Grant Common User Privileges grant INHERIT PRIVILEGES on user &username to sys; grant INHERIT PRIVILEGES on user sys to &username; grant RESOURCE, UNLIMITED TABLESPACE, SELECT_CATALOG_ROLE to &username; grant CREATE ANY TABLE, DROP ANY TABLE, INSERT ANY TABLE, SELECT ANY TABLE, CREATE ANY CREDENTIAL, CREATE PUBLIC SYNONYM, CREATE PROCEDURE, ALTER SESSION, CREATE JOB to &username; grant CREATE SESSION, SET CONTAINER to &username; grant SELECT on SYS.V_$MYSTAT to &username; grant SELECT on SYS.SERVICE$ to &username; grant SELECT on SYS.V_$ENCRYPTION_WALLET to &username; grant read, write on directory DATA_PUMP_DIR to &username; grant EXECUTE on SYS.DBMS_PRIV_CAPTURE to &username; grant EXECUTE on SYS.DBMS_PDB_LIB to &username; grant EXECUTE on SYS.DBMS_CRYPTO to &username; grant EXECUTE on SYS.DBMS_SYS_ERROR to &username; grant EXECUTE ON SYS.DBMS_ISCHED to &username; grant EXECUTE ON SYS.DBMS_PDB_LIB to &username; grant EXECUTE on SYS.DBMS_PDB to &username; grant EXECUTE on SYS.DBMS_SERVICE to &username; grant EXECUTE on SYS.DBMS_PDB to &username; grant EXECUTE on SYS.CONFIGURE_DV to &username; grant EXECUTE on SYS.DBMS_SYS_ERROR to &username; grant EXECUTE on SYS.DBMS_CREDENTIAL to &username; grant EXECUTE on SYS.DBMS_RANDOM to &username; grant EXECUTE on SYS.DBMS_SYS_SQL to &username; grant EXECUTE on SYS.DBMS_LOCK to &username; grant EXECUTE on SYS.DBMS_AQADM to &username; grant EXECUTE on SYS.DBMS_AQ to &username; grant EXECUTE on SYS.DBMS_SYSTEM to &username; grant EXECUTE on SYS.SCHED$_LOG_ON_ERRORS_CLASS to &username; grant SELECT on SYS.DBA_DATA_FILES to &username; grant SELECT on SYS.DBA_EXTENTS to &username; grant SELECT on SYS.DBA_CREDENTIALS to &username; grant SELECT on SYS.AUDIT_UNIFIED_ENABLED_POLICIES to &username; grant SELECT on SYS.DBA_ROLES to &username; grant SELECT on SYS.V_$ENCRYPTION_KEYS to &username; grant SELECT on SYS.DBA_DIRECTORIES to &username; grant SELECT on SYS.DBA_USERS to &username; grant SELECT on SYS.DBA_OBJECTS to &username; grant SELECT on SYS.V_$PDBS to &username; grant SELECT on SYS.V_$SESSION to &username; grant SELECT on SYS.GV_$SESSION to &username; grant SELECT on SYS.DBA_REGISTRY to &username; grant SELECT on SYS.DBA_DV_STATUS to &username; alter session set current_schema=&username; REM Create the Catalog objects @$ORACLE_HOME/rdbms/admin/dbms_cloud_task_catalog.sql @$ORACLE_HOME/rdbms/admin/dbms_cloud_task_views.sql @$ORACLE_HOME/rdbms/admin/dbms_cloud_catalog.sql @$ORACLE_HOME/rdbms/admin/dbms_cloud_types.sql REM Create the Package Spec @$ORACLE_HOME/rdbms/admin/prvt_cloud_core.plb @$ORACLE_HOME/rdbms/admin/prvt_cloud_task.plb @$ORACLE_HOME/rdbms/admin/dbms_cloud_capability.sql @$ORACLE_HOME/rdbms/admin/prvt_cloud_request.plb @$ORACLE_HOME/rdbms/admin/prvt_cloud_internal.plb @$ORACLE_HOME/rdbms/admin/dbms_cloud.sql @$ORACLE_HOME/rdbms/admin/prvt_cloud_admin_int.plb REM Create the Package Body @$ORACLE_HOME/rdbms/admin/prvt_cloud_core_body.plb @$ORACLE_HOME/rdbms/admin/prvt_cloud_task_body.plb @$ORACLE_HOME/rdbms/admin/prvt_cloud_capability_body.plb @$ORACLE_HOME/rdbms/admin/prvt_cloud_request_body.plb @$ORACLE_HOME/rdbms/admin/prvt_cloud_internal_body.plb @$ORACLE_HOME/rdbms/admin/prvt_cloud_body.plb @$ORACLE_HOME/rdbms/admin/prvt_cloud_admin_int_body.plb -- Create the metadata @$ORACLE_HOME/rdbms/admin/dbms_cloud_metadata.sql alter session set current_schema=sys; @$ORACLE_HOME/rdbms/admin/sqlsessend.sql
The documents say that the wallet needs to be added to the grid home sqlnet.ora in RAC, database home in non-RAC. (need to check if this holds true to single instance but GI installs)
Current wallet location in the GRID HOME sqlnet.ora
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u02/app/oracle/admin/grid/grid_wallet))) [grid@ecs1-dnvwf1 grid_wallet]$ orapki wallet display -wallet . Oracle PKI Tool Release 19.0.0.0.0 - Production Version 19.4.0.0.0 Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved. Requested Certificates: User Certificates: Oracle Secret Store entries: exacs_patch_key Trusted Certificates:
Can I add my certificates to this?
Nope! It fails constantly – almost certainly because I don’t know the password for this wallet or it’s a different format.
Need to create a new wallet and the keys.
Suggested location /opt/oracle/dcs/commonstore/wallets/ssl is owned by opc…
So, going to go alongside the existing wallet – this is outside the GRID HOME so in theory safe from patching?
Now you need to create Access Control Entries to allow communication with object store through https.
Your Exadata must be able to access the service network containing Object Storage. (via service gateway, internet gateway etc.)
As oracle, create a dbms_cloud_aces.sql
set the sslwalletdir before running
Note – this script will still prompt for proxy information as the parser spots the ‘&’ in the commented out area and ask for the value. If you don’t have a proxy, either just hit return when prompted , or delete the proxy lines before running.
@$ORACLE_HOME/rdbms/admin/sqlsessstart.sql -- you must not change the owner of the functionality to avoid future issues define clouduser=C##CLOUD$SERVICE -- CUSTOMER SPECIFIC SETUP, NEEDS TO BE PROVIDED BY THE CUSTOMER -- - SSL Wallet directory define sslwalletdir=<Set SSL Wallet Directory> -- -- UNCOMMENT AND SET THE PROXY SETTINGS VARIABLES IF YOUR ENVIRONMENT NEEDS PROXYS -- -- define proxy_uri=<your proxy URI address> -- define proxy_host=<your proxy DNS name> -- define proxy_low_port=<your_proxy_low_port> -- define proxy_high_port=<your_proxy_high_port> -- Create New ACL / ACE s begin -- Allow all hosts for HTTP/HTTP_PROXY dbms_network_acl_admin.append_host_ace( host =>'*', lower_port => 443, upper_port => 443, ace => xs$ace_type( privilege_list => xs$name_list('http', 'http_proxy'), principal_name => upper('&clouduser'), principal_type => xs_acl.ptype_db)); -- -- UNCOMMENT THE PROXY SETTINGS SECTION IF YOUR ENVIRONMENT NEEDS PROXYS -- -- Allow Proxy for HTTP/HTTP_PROXY -- dbms_network_acl_admin.append_host_ace( -- host =>'&proxy_host', -- lower_port => &proxy_low_port, -- upper_port => &proxy_high_port, -- ace => xs$ace_type( -- privilege_list => xs$name_list('http', 'http_proxy'), -- principal_name => upper('&clouduser'), -- principal_type => xs_acl.ptype_db)); -- -- END PROXY SECTION -- -- Allow wallet access dbms_network_acl_admin.append_wallet_ace( wallet_path => 'file:&sslwalletdir', ace => xs$ace_type(privilege_list => xs$name_list('use_client_certificates', 'use_passwords'), principal_name => upper('&clouduser'), principal_type => xs_acl.ptype_db)); end; / -- Setting SSL_WALLET database property begin -- comment out the IF block when installed in non-CDB environments if sys_context('userenv', 'con_name') = 'CDB$ROOT' then execute immediate 'alter database property set ssl_wallet=''&sslwalletdir'''; -- -- UNCOMMENT THE FOLLOWING COMMAND IF YOU ARE USING A PROXY -- -- execute immediate 'alter database property -- set http_proxy=''&proxy_uri'''; end if; end; / @$ORACLE_HOME/rdbms/admin/sqlsessend.sql
Execute this as sys connected to cdb$root
Verify the configuration of DBMS_CLOUD
Use the following script to check your database can access the oracle cloud object storage home page
Need to set the variables sslwalletdir, sslwalletpwd for your environment
-- you must not change the owner of the functionality to avoid future issues define clouduser=C##CLOUD$SERVICE
-- CUSTOMER SPECIFIC SETUP, NEEDS TO BE PROVIDED BY THE CUSTOMER -- - SSL Wallet directory and password define sslwalletdir=<Set SSL Wallet Directory> define sslwalletpwd=<Set SSL Wallet password>
-- create and run this procedure as owner of the ACLs, which is the future owner -- of DBMS_CLOUD CREATE OR REPLACE PROCEDURE &clouduser..GET_PAGE(url IN VARCHAR2) AS request_context UTL_HTTP.REQUEST_CONTEXT_KEY; req UTL_HTTP.REQ; resp UTL_HTTP.RESP; data VARCHAR2(32767) default null; err_num NUMBER default 0; err_msg VARCHAR2(4000) default null;
BEGIN
-- Create a request context with its wallet and cookie table request_context := UTL_HTTP.CREATE_REQUEST_CONTEXT( wallet_path => 'file:&sslwalletdir', wallet_password => '&sslwalletpwd');
-- Make a HTTP request using the private wallet and cookie -- table in the request context req := UTL_HTTP.BEGIN_REQUEST( url => url, request_context => request_context);
resp := UTL_HTTP.GET_RESPONSE(req);
DBMS_OUTPUT.PUT_LINE('valid response');
EXCEPTION WHEN OTHERS THEN err_num := SQLCODE; err_msg := SUBSTR(SQLERRM, 1, 3800); DBMS_OUTPUT.PUT_LINE('possibly raised PLSQL/SQL error: ' ||err_num||' - '||err_msg);
UTL_HTTP.END_RESPONSE(resp); data := UTL_HTTP.GET_DETAILED_SQLERRM ; IF data IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('possibly raised HTML error: ' ||data); END IF; END; / set serveroutput on BEGIN &clouduser..GET_PAGE('https://objectstorage.eu-frankfurt-1.oraclecloud.com'); END; /
set serveroutput off drop procedure &clouduser..GET_PAGE;
Right – that failed
possibly raised PLSQL/SQL error: -29273 - ORA-29273: HTTP request failedpossibly raised HTML error: ORA-12535: TNS:operation timed out
Due to IAM policies I cannot see all the network definitions, but we do have services network to LHR defined. Change the script to access this object storage. https://objectstorage.uk-london-1.oraclecloud.com
New error
possibly raised PLSQL/SQL error: -29273 - ORA-29273: HTTP request failedpossibly raised HTML error: ORA-28759: failure to open file
Can the oracle software owner read the wallet? – nope! add g+r to the files in the wallet directory (all nodes!)
[grid@ecs1-dnvwf2 ssl_wallet]$ ls -latr total 16 drwxr-xr-x 4 grid oinstall 4096 Jun 18 13:39 .. -rw-r----- 1 grid oinstall 0 Jun 18 13:40 ewallet.p12.lck -rw-r----- 1 grid oinstall 0 Jun 18 13:40 cwallet.sso.lck drwxr-xr-x 2 grid oinstall 4096 Jun 18 13:40 . -rw-r----- 1 grid oinstall 3512 Jun 18 13:40 ewallet.p12 -rw-r----- 1 grid oinstall 3557 Jun 18 13:40 cwallet.sso
Now when executed the script returns the text valid response
SQL> @dbms_cloud_check old 1: CREATE OR REPLACE PROCEDURE &clouduser..GET_PAGE(url IN VARCHAR2) AS new 1: CREATE OR REPLACE PROCEDURE C##CLOUD$SERVICE.GET_PAGE(url IN VARCHAR2) AS old 13: wallet_path => 'file:&sslwalletdir', new 13: wallet_path => 'file:/u02/app/oracle/admin/grid/ssl_wallet', old 14: wallet_password => '&sslwalletpwd'); new 14: wallet_password => 'We1come$');
Procedure created.
old 2: &clouduser..GET_PAGE('https://objectstorage.uk-london-1.oraclecloud.com'); new 2: C##CLOUD$SERVICE.GET_PAGE('https://objectstorage.uk-london-1.oraclecloud.com'); valid response
PL/SQL procedure successfully completed.
old 1: drop procedure &clouduser..GET_PAGE new 1: drop procedure C##CLOUD$SERVICE.GET_PAGE
Procedure dropped.
Configuring users or roles to use DBMS_CLOUD
Grant the minimal privileges to a user or role for using DBMS_CLOUD
You can grant the required privileges to individual users (e.g. execute on dbms_cloud) but it is simpler to manage if you create a role and then grant this to users as required.
In your pdb
define userrole='OBJECT_STORAGE_USER'
-- target sample user define username='MEL'
create role &userrole; grant &userrole to &username;
REM the following are minimal privileges to use DBMS_CLOUD REM - this script assumes core privileges REM - CREATE SESSION REM - Tablespace quote on the default tablespace for a user
REM for creation of external tables, e.g. DBMS_CLOUD.CREATE_EXTERNAL_TABLE() grant CREATE TABLE to &userrole;
REM for using COPY_DATA() REM - Any log and bad file information is written into this directory grant read, write on directory DATA_PUMP_DIR to &userrole;
REM grant EXECUTE on dbms_cloud to &userrole;
Configure ACEs for a user or role to use DBMS_CLOUD
ok – now you have a role, you need to connect to the pdb and set it so your role can use the ACE/ACL to see the wallet
Make sure to set cloudrole and sslwalletdir
Note – this script will still prompt for proxy information as the parser spots the ‘&’ in the commented out area and ask for the value. If you don’t have a proxy, either just hit return when prompted , or delete the proxy lines before running.
@$ORACLE_HOME/rdbms/admin/sqlsessstart.sql
-- target sample role define cloudrole=OBJECT_STORAGE_USER
-- CUSTOMER SPECIFIC SETUP, NEEDS TO BE PROVIDED BY THE CUSTOMER -- - SSL Wallet directory define sslwalletdir=<Set SSL Wallet Directory>
-- -- UNCOMMENT AND SET THE PROXY SETTINGS VARIABLES IF YOUR ENVIRONMENT NEEDS PROXYS -- -- define proxy_uri=<your proxy URI address> -- define proxy_host=<your proxy DNS name> -- define proxy_low_port=<your_proxy_low_port> -- define proxy_high_port=<your_proxy_high_port>
-- Create New ACL / ACE s begin -- Allow all hosts for HTTP/HTTP_PROXY dbms_network_acl_admin.append_host_ace( host =>'*', lower_port => 443, upper_port => 443, ace => xs$ace_type( privilege_list => xs$name_list('http', 'http_proxy'), principal_name => upper('&cloudrole'), principal_type => xs_acl.ptype_db));
Next you need to generate credentials that allow your user to access the bucket
In the Cloud console go to your user settings page and generate an AUTH TOKEN and note it.
Connect as the database user who will be accessing object storage and execute the create credential process. Each user has their own credential namespace.
BEGIN DBMS_CLOUD.CREATE_CREDENTIAL( credential_name => 'your credential name', username => 'OCI within your tenancy', password => 'auth token generated for OCI user' ); END; /
Start with the smaller/simpler files – The user ratings – this has the columns ID, Name,Rating
Create the destination table
CREATE TABLE RATINGS (rating_id varchar(255), name VARCHAR2(255), rating VARCHAR2(255) );
Try loading data using the first file
BEGIN DBMS_CLOUD.COPY_DATA( table_name =>'RATINGS', credential_name =>'CLOUD_OSS_CRED', file_uri_list =>'https://objectstorage.uk-london-1.oraclecloud.com/n/demotenancy/b/mel_test/o/user_rating_0_to_1000.csv', format => json_object('delimiter' value ',','ignoremissingcolumns' value 'true', 'rejectlimit' value '100','skipheaders' value '1') ); END; /
Filesize
Run 1
Run2
2.55mb
2.52
1.37
Try with multiple files in file_uri_list
BEGIN DBMS_CLOUD.COPY_DATA( table_name =>'RATINGS', credential_name =>'CLOUD_OSS_CRED', file_uri_list =>'https://objectstorage.uk-london-1.oraclecloud.com/n/demotenancy/b/mel_test/o/user_rating_0_to_1000.csv,https://objectstorage.uk-london-1.oraclecloud.com/n/demotenancy/b/mel_test/o/user_rating_1000_to_2000.csv', format => json_object('delimiter' value ',','ignoremissingcolumns' value 'true', 'rejectlimit' value '100','skipheaders' value '1') ); END; /
Filesize
Run 1
Run2
2.55mb + 2.14mb
1.78
1.64
Right – now to go a bit bigger because it’s starting to feel like the time taken is all setup time. The book files supplied in the zip annoying have different formats, so determine which have the same columns (books 1800 to 5000)
Multiple individual files – note the data is fugly, and there are a lot of rejects due to multi-line HTML descriptions so have increased the rejectlimit.
BEGIN DBMS_CLOUD.COPY_DATA( table_name =>'BOOKS', credential_name =>'CLOUD_OSS_CRED', file_uri_list =>'https://objectstorage.uk-london-1.oraclecloud.com/n/demotenancy/b/mel_test/o/book1800k-1900k.csv,https://objectstorage.uk-london-1.oraclecloud.com/n/demotenancy/b/mel_test/o/book1900k-2000k.csv,https://objectstorage.uk-london-1.oraclecloud.com/n/demotenancy/b/mel_test/o/book2000k-3000k.csv,https://objectstorage.uk-london-1.oraclecloud.com/n/demotenancy/b/mel_test/o/book3000k-4000k.csv,https://objectstorage.uk-london-1.oraclecloud.com/n/demotenancy/b/mel_test/o/book4000k-5000k.csv', format => json_object('delimiter' value ',','ignoremissingcolumns' value 'true', 'rejectlimit' value '100000','skipheaders' value '1') ); END; / select count(*) from books;
Filesize
Run 1
Run 2
29mb + 32mb +276m + 166mb +172mb
8.45
6.54
Larger file made by concatenating those 5 the user files together
BEGIN DBMS_CLOUD.COPY_DATA( table_name =>'BOOKS', credential_name =>'CLOUD_OSS_CRED', file_uri_list =>'https://objectstorage.uk-london-1.oraclecloud.com/n/demotenancy/b/mel_test/o/combined_book_1800-500.csv', format => json_object('delimiter' value ',','ignoremissingcolumns' value 'true', 'rejectlimit' value '100000','skipheaders' value '1') ); END; / select count(*) from books;
Filesize
Run 1
Run 2
627mb
4.51
4.31
Counter-intuitively it seems that a single large file performs better than multiple individual files. However, this needs further investigation.
Validation Script
This script from the MOS note can be used to verify the connectivity and get more detailed error messages.
-- user to trouble shoot define clouduser=<username>
-- CUSTOMER SPECIFIC SETUP, NEEDS TO BE PROVIDED BY THE CUSTOMER -- - SSL Wallet directory and password define sslwalletdir=<Set SSL Wallet Directory> define sslwalletpwd=<Set SSL Wallet password>
-- create and run this procedure as owner of the ACLs, which is the future owner -- of DBMS_CLOUD CREATE OR REPLACE PROCEDURE &clouduser..GET_PAGE(url IN VARCHAR2) AS request_context UTL_HTTP.REQUEST_CONTEXT_KEY; req UTL_HTTP.REQ; resp UTL_HTTP.RESP; data VARCHAR2(32767) default null; err_num NUMBER default 0; err_msg VARCHAR2(4000) default null;
BEGIN
-- Create a request context with its wallet and cookie table request_context := UTL_HTTP.CREATE_REQUEST_CONTEXT( wallet_path => 'file:&sslwalletdir', wallet_password => '&sslwalletpwd');
-- Make a HTTP request using the private wallet and cookie -- table in the request context req := UTL_HTTP.BEGIN_REQUEST( url => url, request_context => request_context);
resp := UTL_HTTP.GET_RESPONSE(req);
DBMS_OUTPUT.PUT_LINE('valid response');
EXCEPTION WHEN OTHERS THEN err_num := SQLCODE; err_msg := SUBSTR(SQLERRM, 1, 3800); DBMS_OUTPUT.PUT_LINE('possibly raised PLSQL/SQL error: ' ||err_num||' - '||err_msg);
UTL_HTTP.END_RESPONSE(resp); data := UTL_HTTP.GET_DETAILED_SQLERRM ; IF data IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('possibly raised HTML error: ' ||data); END IF; END; / set serveroutput on BEGIN &clouduser..GET_PAGE('https://objectstorage.eu-frankfurt-1.oraclecloud.com'); END; /
set serveroutput off drop procedure &clouduser..GET_PAGE;
One challenge is with Exadata cells in a lab environment is that they are secure! This means that it has long lock out times in the event of an incorrect login and tough lock settings. You can manually change these.. but every time you update your cell there is a chance they will be reset.
For example, if you want to drop the lock time in the event of a failed login from 10 minutes to a more manageable 60 seconds you would issue the command
You can combine multiple pam-auth commands on the same line.. e.g. if I also want to say that the cell only remembers one previous password I could say
There are a lot of options for this tool – you can set the system back to secure defaults, or make it even more secure, such as locking an account after a single failed login!
In Solaris 11.2 (and probably other releases – I haven’t checked) if you try to view the ldom configuration as a non-privileged user you will get the following message
emagent @sscadb01:~$ ldm ls
Authorization failed
You can grant the user the ability to view the LDOM config using the built in ‘LDoms Review’ profile
emagent@sscadb01:~$ ldm ls
NAME STATE FLAGS CONS VCPU MEMORY UTIL NORM UPTIME
primary active -n-cv- UART 128 523776M 3.7% 3.7% 41d 6h 9m
ssccn1-app1 active -n---- 5001 128 512G 0.4% 0.2% 41d 6h 32m
Enterprise Manager 13 is LDOMs aware, and you will need to add this privilege to the agent software owner if you want the virtualization to be shown in this tool.
There is a common IB network between all nodes in a SuperCluster. This is primarily used for access to the ZFS storage appliance, but it can also be used to provide IB access to the database.
I have a 2 node RAC cluster, and both nodes have a network adapter similar to this
# ifconfig stor_ipmp0
stor_ipmp0: flags=108001000843 mtu 65520 index 2
inet 192.168.28.2 netmask fffffc00 broadcast 192.168.31.255
groupname stor_ipmp0
First create entries for the IB VIP in /etc/hosts on all nodes in the cluster and on the hosts who want to access the IB Listener (e.g. Exalogic, application nodes in the SuperCluster)
Create the required oracle network resources on one node of the cluster. If you’re uncertain about the subnet to specify you can use one of the online calculators such as http://www.subnet-calculator.com/
Define the network interface as a global public cluster interface as root
# /u01/app/11.2.0.3/grid/bin/oifcfg setif -global stor_ipmp0/192.168.28.0:public
# /u01/app/11.2.0.3/grid/bin/oifcfg getif
bondeth0 138.3.16.0 global public
bondib0 192.168.0.0 global cluster_interconnect
bondib1 192.168.0.0 global cluster_interconnect
bondib2 192.168.0.0 global cluster_interconnect
bondib3 192.168.0.0 global cluster_interconnect
stor_ipmp0 192.168.28.0 global public
Create the new network resource in the grid infrastructure as root
Add entries to the grid home tnsnames.ora. There are slightly different configuartions per node, as we will explicitly name the ‘local’ and ‘remote’ nodes. If we had more than 2 nodes, then our entries for LISTENER_IBREMOTE and LISTENER_IPREMOTE would list all of the non-local nodes.
Copy your new tnsnames.ora entries to your $ORACLE_HOME/network/admin/tnsnames.ora
Login as to your database as sysdba and set the listener_networks parameter
SQL> alter system set listener_networks='((NAME=network2)(LOCAL_LISTENER=LISTENER_IBLOCAL)(REMOTE_LISTENER=LISTENER_IBREMOTE))','((NAME=network1)(LOCAL_LISTENER=LISTENER_IPLOCAL)(REMOTE_LISTENER=LISTENER_IPREMOTE))' scope=both sid='*';
This will cause the database to register with the new infiniband listener.
Oracle, Unix and the world at large 