Oracle Database

Adjusting Oracle Settings to Accommodate Larger Clusters

Reserving Ports for HiveServer 2

HiveServer2 uses port 10000 by default, but Oracle database changes the local port range. This can cause HiveServer2 to fail to start.

Manually reserve the default port for HiveServer2. For example, the following command reserves port 10000 and inserts a comment indicating the reason:

echo << EOF > /etc/sysctl.cnf
# HS2 uses port 10000
net.ipv4.ip_local_reserved_ports = 10000
EOF

sysctl -q -w net.ipv4.ip_local_reserved_ports=10000

Modifying the Maximum Number of Oracle Connections

Work with your Oracle database administrator to ensure appropriate values are applied for your Oracle database settings. You must determine the number of connections, transactions, and sessions to be allowed.

Allow 100 maximum connections for each service that requires a database and then add 50 extra connections. For example, for two services, set the maximum connections to 250. If you have five services that require a database on one host (the databases for Cloudera Manager Server, Activity Monitor, Reports Manager, Cloudera Navigator, and Hive metastore), set the maximum connections to 550.

From the maximum number of connections, you can determine the number of anticipated sessions using the following formula:

sessions = (1.1 * maximum_connections) + 5

For example, if a host has a database for two services, anticipate 250 maximum connections. If you anticipate a maximum of 250 connections, plan for 280 sessions.

Once you know the number of sessions, you can determine the number of anticipated transactions using the following formula:

transactions = 1.1 * sessions

Continuing with the previous example, if you anticipate 280 sessions, you can plan for 308 transactions.

Work with your Oracle database administrator to apply these derived values to your system.

Using the sample values above, Oracle attributes would be set as follows:

alter system set processes=250;
alter system set transactions=308;
alter system set sessions=280;

Ensuring Your Oracle Database Supports UTF8

The database you use must support UTF8 character set encoding. You can implement UTF8 character set encoding in Oracle databases by using the dbca utility. In this case, you can use the characterSet AL32UTF8 option to specify proper encoding. Consult your DBA to ensure UTF8 encoding is properly configured.

• Cloudera Manager Server (not required if you are using the Cloudera Manager installer)
• Cloudera Management Service roles:
  • Activity Monitor (if using the MapReduce service)
  • Reports Manager
  • Cloudera Navigator Audit Server
  • Cloudera Navigator Metadata Server
• Hive Metastore
• Sentry Server

You can create the Oracle database, schema and users on the host where the Cloudera Manager Server will run, or on any other hosts in the cluster. For performance reasons, you should install each database on the host on which the service runs, as determined by the roles you assign during installation or upgrade. In larger deployments or in cases where database administrators are managing the databases the services use, you can separate databases from services, but use caution.

The database must be configured to support UTF-8 character set encoding.

Record the values you enter for database names, usernames, and passwords. The Cloudera Manager installation wizard requires this information to correctly connect to these databases.

1. Log into the Oracle client:

   sqlplus system@localhost
   Enter password: ******

2. Create a schema and user for the Cloudera Manager Server. The minimum permissions required are:

   create user username identified by password default tablespace tablespace;
   grant CREATE SESSION to username;
   grant CREATE TABLE to username;
   grant CREATE SEQUENCE to username;
   grant EXECUTE on sys.dbms_lob to username;

   where username and password are the credentials you specified in Preparing a Cloudera Manager Server External Database.
3. Grant a quota on the tablespace (the default tablespace is SYSTEM) where tables will be created:

   SQL> ALTER USER username quota 100m on tablespace 

   or for unlimited space:

   SQL> ALTER USER username quota unlimited on tablespace

4. Create schema and users for Activity Monitor, Reports Manager, Hive Metastore Server, Hue Server, Sentry Server, Cloudera Navigator Audit Server, and Cloudera Navigator Metadata Server:schema, user, and password can be any value. The examples match the default names provided in the Cloudera Manager configuration settings:

   Role	Schema	User	Password
   Activity Monitor	amon	amon	amon_password
   Reports Manager	rman	rman	rman_password
   Hive Metastore Server	metastore	hive	hive_password
   Sentry Server	sentry	sentry	sentry_password
   Cloudera Navigator Audit Server	nav	nav	nav_password
   Cloudera Navigator Metadata Server	navms	navms	navms_password

5. For each user in the table in the preceding step, create a user and add privileges for the each user:

   create user username identified by password default tablespace tablespace;
   grant CREATE SESSION to username;
   grant CREATE TABLE to username;
   grant CREATE SEQUENCE to username;
   grant EXECUTE on sys.dbms_lob to username;

6. Grant a quota on the tablespace (the default tablespace is SYSTEM) where tables will be created:

   SQL> ALTER USER username quota 100m on tablespace 

   or for unlimited space:

   SQL> ALTER USER username quota unlimited on tablespace

   For further information about Oracle privileges, see Authorization: Privileges, Roles, Profiles, and Resource Limitations.
7. After creating the Cloudera Navigator Audit Server database, set the following additional privileges:

   GRANT EXECUTE ON sys.dbms_crypto TO nav;
   GRANT CREATE VIEW TO nav;

   where nav is the Navigator Audit Server user you specified above when you created the database.

Return to Establish Your Cloudera Manager Repository Strategy.

Install and Start Oracle 11g

Use Oracle's instructions.

Create the Oozie Oracle User and Grant Privileges

The following example uses the Oracle sqlplus command-line tool, and shows the privileges Cloudera recommends. Oozie needs CREATE SESSION to start and manage workflows. The additional roles are needed for creating and upgrading the Oozie database.

$ sqlplus system@localhost

Enter password: ******

SQL> create user oozie identified by oozie default tablespace users temporary tablespace temp;

User created.

SQL> grant alter index to oozie;
grant alter table to oozie;
grant create index to oozie;
grant create sequence to oozie;
grant create session to oozie;
grant create table to oozie;
grant drop sequence to oozie;
grant select dictionary to oozie;
grant drop table to oozie;
alter user oozie quota unlimited on users; 
alter user oozie quota unlimited on system;

SQL> exit

$

Add the Oracle JDBC Driver JAR to Oozie

Copy or symbolically link the Oracle JDBC driver JAR into the /var/lib/oozie/ directory.