HDP Sandbox (2.6.1)のAmbariでOracle XEを使う

https://community.hortonworks.com/content/supportkb/49135/how-to-install-oracle-express-xe-on-sandbox.html
https://docs.hortonworks.com/HDPDocuments/Ambari-2.2.2.0/bk_ambari_reference_guide/content/_using_ambari_with_oracle.html
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r31/datapump_OBE/datapump.html

1. Oracle XEのインストール

Dockerホスト(Ubuntu）でSwapが2GB以上あるか確認して、内容であれば追加する
dd if=/dev/zero of=/var/swap.file count=2560 bs=1M
chmod go= /var/swap.file
mkswap /var/swap.file
grep -qw swap /etc/fstab || echo "/var/swap.file swap swap defaults 0 0" >> /etc/fstab
swapon /var/swap.file

適当にあるCentOS系イメージからコンテナーを作成する（Sandboxのもので可）
docker run --name oracle --hostname "node100.localdomain" --network=hdp --ip=172.17.130.100  --privileged -d hdp/base:6.8  /usr/sbin/sshd -D

Container上でOracleのウエブサイトからOracle XE Linux 64bit rpm zipファイルをダウンロードし、適当なところに解凍する
/dev/shmが2GB以上あることを確認する。
ない場合は
mount -t tmpfs shmfs -o size=2g /dev/shm

Oracle rpmをインストール

cd ./Disk1
rpm -ivh oracle-xe-11.2.0-*.0.x86_64.rpm
/etc/init.d/oracle-xe configure

確認

su - oracle
. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
sqlplus / as sysdba

2. AmbariようにOracleを設定する

CREATE USER ambari IDENTIFIED BY bigdata default tablespace USERS temporary tablespace TEMP;
GRANT unlimited tablespace to ambari;
GRANT create session to ambari;
GRANT create TABLE to ambari;
GRANT create SEQUENCE to ambari;

新規にDBを作成する場合は

sqlplus ambari/bigdata < /var/lib/ambari-server/resources/Ambari-DDL-Oracle-CREATE.sql

既存のDBをImportするには

SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='DATA_PUMP_DIR';
/u01/app/oracle/admin/XE/dpdump/

mv ambari.dmp /u01/app/oracle/admin/XE/dpdump/

もし、1521ポートがマッピングされていない場合は
. ./start_hdp.sh
f_port_forward 1521 sandbox.hortonworks.com 1521

Oracle SQL DeveloperをPC/Macから開始
New Connectionを作成（SYSTEMユーザで）
View => DBAからもコネクション作成ボタンを押して上記で作ったコネクションを使用
Data Pumpを右クリックしてData Pump Import Wizardを選択
Step 1でFile Namesに.dmpファイル名をタイプ（Type of ImportはTablesかSchema)
Step 2で全テーブルを選択
Step 3でRe-Map SchemasでSchemaをAMBARI、Re-Map TablespacesでDestinationをUSERS
Step 4でAction On Table if Table ExistsをReplace

tail -f /u01/app/oracle/admin/XE/dpdump/IMPORT.LOG

うまくいった場合はSQL DeveloperでOther Users => AMBARI => Tables (Filtered)にテーブルがたくさんあるはずです。

新規作成かインポート後ambari-server setup

その前に、
ln -s /u01/app/oracle/product/11.2.0/xe/jdbc/lib/ojdbc6.jar /usr/share/java/ojdbc6.jar
echo 'server.jdbc.driver.path=/usr/share/java/ojdbc6.jar' >> /etc/ambari-server/conf/ambari.properties

もしインポートした場合はadminユーザのパスワードを変更する必要があります。
[root@sandbox ~]# su - oracle
-bash-4.1$ . /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
-bash-4.1$ sqlplus ambari/bigdata

SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 27 06:23:08 2017

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


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> UPDATE users SET user_password='538916f8943ec225d97a9a86a2c6ec0818c1cd400e09e03b660fdaaec4af29ddbb6f2b1033b81b00' WHERE user_name='admin' and user_type='LOCAL';

1 row updated.

Ambariセットアップの開始
ambari-server stop
ambari-server setup
Using python  /usr/bin/python
Setup ambari-server
Checking SELinux...
SELinux status is 'disabled'
Customize user account for ambari-server daemon [y/n] (n)?
Adjusting ambari-server permissions and ownership...
Checking firewall status...
WARNING: iptables is running. Confirm the necessary Ambari ports are accessible. Refer to the Ambari documentation for more details on ports.
OK to continue [y/n] (y)?
Checking JDK...
Do you want to change Oracle JDK [y/n] (n)?
Completing setup...
Configuring database...
Enter advanced database configuration [y/n] (n)? y
Configuring database...
==============================================================================
Choose one of the following options:
[1] - PostgreSQL (Embedded)
[2] - Oracle
[3] - MySQL / MariaDB
[4] - PostgreSQL
[5] - Microsoft SQL Server (Tech Preview)
[6] - SQL Anywhere
[7] - BDB
==============================================================================
Enter choice (2): 2
Hostname (localhost):
Port (1521):
Select Oracle identifier type:
1 - Service Name
2 - SID
(1): 2
SID (XE):
Username (ambari):
Enter Database Password (bigdata):
Configuring ambari database...
Configuring remote database connection properties...
WARNING: Before starting Ambari Server, you must run the following DDL against the database to create the schema: /var/lib/ambari-server/resources/Ambari-DDL-Oracle-CREATE.sql'
Proceed with configuring remote database connection properties [y/n] (y)?
Extracting system views...
............
Adjusting ambari-server permissions and ownership...
Ambari Server 'setup' completed successfully.

ambari-server start

補足：コンテナを止めてしまって、再度Oracleをスタートするには
[root@sandbox ~]# rm -rf /var/tmp/.oracle
[root@sandbox ~]# mount -t tmpfs shmfs -o size=2g /dev/shm
[root@sandbox ~]# su - oracle
-bash-4.1$ . /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
-bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 3 12:30:23 2017

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2233344 bytes
Variable Size             729811968 bytes
Database Buffers          331350016 bytes
Redo Buffers                5541888 bytes
Database mounted.
Database opened.
SQL> Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-bash-4.1$ lsnrctl start


補足2：HDFだと変なエラーが出る
30 Jan 2018 01:23:53,156  WARN [Stack Version Loading Thread] RepoVdfCallable:142 - Could not load version definition for HDP-2.6 identified by http://public-repo-1.hortonworks.com/HDP/ubuntu12/2.x/updates/2.6.4.0/HDP-2.6.4.0-91.xml. null
javax.xml.bind.UnmarshalException
 - with linked exception:
[org.xml.sax.SAXParseException; lineNumber: 54; columnNumber: 15; cvc-complex-type.2.4.d: Invalid content was found starting with element 'tags'. No child element is expected at this point.]
        at com.sun.xml.bind.v2.runtime.unmarshaller.UnmarshallerImpl.handleStreamException(UnmarshallerImpl.java:431)
        at com.sun.xml.bind.v2.runtime.unmarshaller.UnmarshallerImpl.unmarshal0(UnmarshallerImpl.java:368)
        at com.sun.xml.bind.v2.runtime.unmarshaller.UnmarshallerImpl.unmarshal(UnmarshallerImpl.java:338)
        at org.apache.ambari.server.state.repository.VersionDefinitionXml.load(VersionDefinitionXml.java:442)
...
30 Jan 2018 01:23:53,238 ERROR [main] AmbariServer:1073 - Failed to run the Ambari Server
org.apache.ambari.server.AmbariException: An error occured during updating current repository versions with stack repositories.
        at org.apache.ambari.server.stack.UpdateActiveRepoVersionOnStartup.process(UpdateActiveRepoVersionOnStartup.java:99)
        at org.apache.ambari.server.orm.AmbariJpaLocalTxnInterceptor.invoke(AmbariJpaLocalTxnInterceptor.java:128)
        at org.apache.ambari.server.controller.AmbariServer.main(AmbariServer.java:1061)
Caused by: java.lang.NullPointerException
        at org.apache.ambari.server.stack.UpdateActiveRepoVersionOnStartup.updateRepoVersion(UpdateActiveRepoVersionOnStartup.java:106)
        at org.apache.ambari.server.stack.UpdateActiveRepoVersionOnStartup.process(UpdateActiveRepoVersionOnStartup.java:92)
        ... 2 more

回避策
ambari-server install-mpack --mpack=http://public-repo-1.hortonworks.com/HDF/centos6/3.x/updates/3.0.2.0/tars/hdf_ambari_mp/hdf-ambari-mpack-3.0.2.0-76.tar.gz --verbose