Oracle Database
To use an Oracle database, follow these procedures.
- Collecting Oracle Database Information
- Configuring the Oracle Server
- Installing the Oracle JDBC Connector
- Creating Databases for the Cloudera Manager Server, Activity Monitor, Reports Manager, Hive Metastore Server, Sentry Server, Cloudera Navigator Audit Server, and Cloudera Navigator Metadata Server
- Configuring the Hue Server to Store Data in Oracle (Parcel Installation)
- Configuring the Hue Server to Store Data in Oracle (Package Installation)
- Configuring Oracle for Oozie
Collecting Oracle Database Information
To configure Cloudera Manager to work with an Oracle database, get the following information from your Oracle DBA:
- Hostname - The DNS name or the IP address of the host where the Oracle database is installed.
- SID - The name of the schema that will store Cloudera Manager information.
- Username - A username for each schema that is storing information. You could have four unique usernames for the four schema.
- Password - A password corresponding to each username.
Configuring the Oracle Server
Adjusting Oracle Settings to Accommodate Larger Clusters
- Enable direct and asynchronous I/O by setting the FILESYSTEMIO_OPTIONS parameter to SETALL.
- Increase the RAM available to Oracle by changing the MEMORY_TARGET parameter. The amount of memory to assign depends on the size of the Hadoop cluster.
- Create more redo log groups and spread the redo log members across separate disks or logical unit numbers.
- Increase the size of redo log members to be at least 1 GB.
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.
Installing the Oracle JDBC Connector
You must install the JDBC connector on the Cloudera Manager Server host and on hosts to which you assign the Activity Monitor, Reports Manager, Hive Metastore Server, Sentry Server, Cloudera Navigator Audit Server, and Cloudera Navigator Metadata Server server roles.
Cloudera recommends that you assign all roles that require a database on the same host and install the connector on that host. Locating all such roles on the same host is recommended but not required. If you install a role, such as Activity Monitor, on one host and other roles on a separate host, you would install the JDBC connector on each host running roles that access the database.
- Download and install the ojdbc6.jar file, which contains the JDBC driver. Download the version that is designed for:
- Java 6
- The Oracle database version used in your environment. For example, for an environment using Oracle 11g R2, download the JAR file from http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html.
Note: Only the ojdbc6.jar file is supported for both Oracle 11g R2 and Oracle 12c; the ojdbc7.jar file is not supported. - Copy the appropriate JDBC JAR file to /usr/share/java/oracle-connector-java.jar for use with the Cloudera Manager databases (for example, for the
Activity Monitor, and so on), and for use with Hive.
$ mkdir /usr/share/java (if necessary) $ cp /tmp/ojdbc6.jar /usr/share/java/oracle-connector-java.jar
Creating Databases for the Cloudera Manager Server, Activity Monitor, Reports Manager, Hive Metastore Server, Sentry Server, Cloudera Navigator Audit Server, and Cloudera Navigator Metadata Server
- 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.
- Log into the Oracle client:
sqlplus system@localhost Enter password: ******
- Create a schema and user for the Cloudera Manager Server:
SQL> create user username identified by password; SQL> grant CREATE SESSION to username; SQL> grant CREATE ANY TABLE to username; SQL> grant CREATE ANY SEQUENCE to username; SQL> grant CREATE ANY INDEX to username; SQL> grant ALTER ANY TABLE to username; SQL> grant ALTER ANY INDEX to username;
where username and password are the credentials you specified in Preparing a Cloudera Manager Server External Database. - 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
- Create schema and users for Activity Monitor, Reports Manager, Hive Metastore 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 - For each user in the table in the preceding step, create a user and add privileges for the each user:
SQL> create user username identified by password; SQL> grant CREATE SESSION to username; SQL> grant CREATE ANY TABLE to username; SQL> grant CREATE ANY SEQUENCE to username; SQL> grant CREATE ANY INDEX to username; SQL> grant ALTER ANY TABLE to username; SQL> grant ALTER ANY INDEX to username;
- 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. - 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.
Configuring the Hue Server to Store Data in Oracle (Parcel Installation)
Use the following instructions to configure the Hue Server with an Oracle database if you are working on a parcel-based deployment. If you are using packages, see Configuring the Hue Server to Store Data in Oracle (Package Installation).
- Install the required packages.
RHEL
$ sudo yum install gcc python-devel python-pip python-setuptools libaio
SLES
$ sudo zypper install gcc python-devel python-pip python-setuptools libaio
Ubuntu or Debian
$ sudo apt-get install gcc python-devel python-pip python-setuptools libaio1
- Add http://tiny.cloudera.com/hue-oracle-client-db to the Cloudera Manager remote parcel repository URL list and download, distribute, and activate the parcel.
- For CDH versions lower than 5.3, install the Python Oracle library:
Note: HUE_HOME is a reference to the location of your Hue installation. For package installs, this is usually /usr/lib/hue; for parcel installs, this is usually, /opt/cloudera/parcels/<parcel version>/lib/hue/.
$ HUE_HOME/build/env/bin/pip install cx_Oracle
- For CDH versions lower than 5.3, upgrade django south:
$ HUE_HOME/build/env/bin/pip install south --upgrade
- In the Cloudera Manager Admin Console, go to the Hue service status page.
- Select . Confirm you want to stop the service by clicking Stop.
- Select . Confirm you want to dump the database by clicking Dump Database.
- Click the Configuration tab.
- Select .
- Select .
- Set the Hue Service Advanced Configuration Snippet (Safety Valve) for hue_safety_valve.ini property.
Note: If you set Hue Database Hostname, Hue Database Port, Hue Database Username, and Hue Database Password at the service-level, under , you can omit those properties from the server-lever configuration above and avoid storing the Hue password as plain text. In either case, set engine and name in the server-level safety-valve.Add the following options (and modify accordingly for your setup):
[desktop] [[database]] host=localhost port=1521 engine=oracle user=hue password=secretpassword name=<SID of the Oracle database, for example, 'XE'>
For CDH 5.1 and higher you can use an Oracle service name. To use the Oracle service name instead of the SID, use the following configuration instead:port=0 engine=oracle user=hue password=secretpassword name=oracle.example.com:1521/orcl.example.com
The directive port=0 allows Hue to use a service name. The name string is the connect string, including hostname, port, and service name.
To add support for a multithreaded environment, set the threaded option to true under the [desktop]>[[database]] section.
options={"threaded":true}
- Grant required permissions to the hue user in Oracle:
GRANT CREATE <sequence> TO <user>; GRANT CREATE <session> TO <user>; GRANT CREATE <table> TO <user>; GRANT CREATE <view> TO <user>; GRANT CREATE <procedure> TO <user>; GRANT CREATE <trigger> TO <user>; GRANT EXECUTE ON sys.dbms_crypto TO <user>; GRANT EXECUTE ON SYS.DBMS_LOB TO <user>;
- Go to the Hue Server instance in Cloudera Manager and select .
- Ensure you are connected to Oracle as the hue user, then run the following command to delete all data from Oracle tables:
> set pagesize 100; > SELECT 'DELETE FROM ' || table_name || ';' FROM user_tables;
- Run the statements generated in the preceding step.
- Commit your changes.
commit;
- Load the data that you dumped. Go to the Hue Server instance and select . This step is not necessary if you have a fresh Hue install with no data or if you don’t want to save the Hue data.
- Start the Hue service.
Configuring the Hue Server to Store Data in Oracle (Package Installation)
If you have a parcel-based environment, see Configuring the Hue Server to Store Data in Oracle (Parcel Installation).
- Download the Oracle libraries at Instant Client for Linux x86-64 Version 11.1.0.7.0, Basic and SDK (with headers) zip files to the same directory.
- Unzip the Oracle client zip files.
- Set environment variables to reference the libraries.
$ export ORACLE_HOME=oracle_download_directory $ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME
- Create a symbolic link for the shared object:
$ cd $ORACLE_HOME $ ln -sf libclntsh.so.11.1 libclntsh.so
- Install the required packages.
RHEL
$ sudo yum install gcc python-devel python-pip python-setuptools libaio
SLES
$ sudo zypper install gcc python-devel python-pip python-setuptools libaio
Ubuntu or Debian
$ sudo apt-get install gcc python-devel python-pip python-setuptools libaio1
- For CDH versions lower than 5.3, install the Python Oracle library:
Note: HUE_HOME is a reference to the location of your Hue installation. For package installs, this is usually /usr/lib/hue; for parcel installs, this is usually, /opt/cloudera/parcels/<parcel version>/lib/hue/.
$ HUE_HOME/build/env/bin/pip install cx_Oracle
- For CDH versions lower than 5.3, upgrade django south:
$ HUE_HOME/build/env/bin/pip install south --upgrade
- In the Cloudera Manager Admin Console, go to the Hue service status page.
- Select . Confirm you want to stop the service by clicking Stop.
- Select . Confirm you want to dump the database by clicking Dump Database.
- Click the Configuration tab.
- Select .
- Select .
- Set the Hue Service Environment Advanced Configuration Snippet (Safety Valve) property to
ORACLE_HOME=oracle_download_directory LD_LIBRARY_PATH=$LD_LIBRARY_PATH:oracle_download_directory
- Set the Hue Service Advanced Configuration Snippet (Safety Valve) for hue_safety_valve.ini property.
Note: If you set Hue Database Hostname, Hue Database Port, Hue Database Username, and Hue Database Password at the service-level, under , you can omit those properties from the server-lever configuration above and avoid storing the Hue password as plain text. In either case, set engine and name in the server-level safety-valve.Add the following options (and modify accordingly for your setup):
[desktop] [[database]] host=localhost port=1521 engine=oracle user=hue password=secretpassword name=<SID of the Oracle database, for example, 'XE'>
For CDH 5.1 and higher you can use an Oracle service name. To use the Oracle service name instead of the SID, use the following configuration instead:port=0 engine=oracle user=hue password=secretpassword name=oracle.example.com:1521/orcl.example.com
The directive port=0 allows Hue to use a service name. The name string is the connect string, including hostname, port, and service name.
To add support for a multithreaded environment, set the threaded option to true under the [desktop]>[[database]] section.
options={"threaded":true}
- Grant required permissions to the hue user in Oracle:
GRANT CREATE <sequence> TO <user>; GRANT CREATE <session> TO <user>; GRANT CREATE <table> TO <user>; GRANT CREATE <view> TO <user>; GRANT CREATE <procedure> TO <user>; GRANT CREATE <trigger> TO <user>; GRANT EXECUTE ON sys.dbms_crypto TO <user>; GRANT EXECUTE ON SYS.DBMS_LOB TO <user>;
- Go to the Hue Server instance in Cloudera Manager and select .
- Ensure you are connected to Oracle as the hue user, then run the following command to delete all data from Oracle tables:
> set pagesize 100; > SELECT 'DELETE FROM ' || table_name || ';' FROM user_tables;
- Run the statements generated in the preceding step.
- Commit your changes.
commit;
- Load the data that you dumped. Go to the Hue Server instance and select . This step is not necessary if you have a fresh Hue install with no data or if you don’t want to save the Hue data.
- Start the Hue service.
Configuring Oracle for Oozie
Install and Start Oracle 11g
Create the Oozie Oracle User and Grant Privileges
The following example uses the Oracle sqlplus command-line tool, and shows the privileges Cloudera recommends.
$ sqlplus system@localhost Enter password: ****** SQL> create user oozie identified by oozie default tablespace users temporary tablespace temp; User created. SQL> grant alter any index to oozie; grant alter any table to oozie; grant alter database link to oozie; grant create any index to oozie; grant create any sequence to oozie; grant create database link to oozie; grant create session to oozie; grant create table to oozie; grant drop any sequence to oozie; grant select any dictionary to oozie; grant drop any table to oozie; grant create procedure to oozie; grant create trigger to oozie; SQL> exit $
Do not make the following grant:
grant select any table;
Add the Oracle JDBC Driver JAR to Oozie
Copy or symbolically link the Oracle JDBC driver JAR into the /var/lib/oozie/ directory.
<< MySQL Database | ©2016 Cloudera, Inc. All rights reserved | Configuring an External Database for Oozie >> |
Terms and Conditions Privacy Policy |