[memo] docker : wnameless/oracle-xe-11g , create database tsdb steps

…

docker run \
  --name ora11xe -d \
  -p 49160:22 \
  -p 49161:1521 \
  -v //mnt/data:/data \
  -v /etc/localtime:/etc/localtime:ro \
  -e ORACLE_ALLOW_REMOTE=true \
  wnameless/oracle-xe-11g

Installation(with Ubuntu 16.04)

docker pull wnameless/oracle-xe-11g

docker run –name ora11xe -d -p 49160:22 -p 49161:1521 -e ORACLE_ALLOW_REMOTE=true wnameless/oracle-xe-11g

Run this, if you want the database to be connected remotely:

docker run -d -p 49160:22 -p 49161:1521 -e ORACLE_ALLOW_REMOTE=true wnameless/oracle-xe-11g

Connect database with following setting:

hostname: localhost port: 49161 sid: xe username: system password: oracle

Password for SYS & SYSTEM

oracle

Login by SSH

ssh root@localhost -p 49160 password: admin

Support custom DB Initialization

# Dockerfile FROM wnameless/oracle-xe-11g ADD init.sql /docker-entrypoint-initdb.d/

用ssh進 container , 建立 tsdb

ssh root@localhost -p 49160 password: admin

 

create user tsdb profile default identified by password default tablespace users temporary tablespace temp account unlock;

 

grant dba to tsdb;

 

 

sqlplus / as sysdba

 

 

Enter user-name: system

Enter password: oracle

 

 

SQL> create user tsdb profile default

identified by password

default tablespace users

temporary tablespace temp

account unlock;

User created.

SQL> SQL> grant dba to tsdb;

 
 
 
Screenshot 2017 03 24 22 28 43
 
 
 
 
 
— Create table
create table TBL_ITEM_KW_DAY
(
  XDATE DATE not null,
  XHOUR NUMBER(2) default 0,
  TAG1 VARCHAR2(50),
  TAG2 VARCHAR2(100),
  VAL NUMBER(10) default 0 not null
)
tablespace USERS;
 
— Add comments to the table
comment on table TBL_ITEM_KW_DAY is ‘搜尋後點商品統計(日), tag1:g_no , tag2:關鍵字 , val:count’;
 
— Create/Recreate indexes
create index TBL_ITEM_KW_DAY_XDATE on TBL_ITEM_KW_DAY (XDATE) tablespace USERSINDEX;
 
 
 
 
 
 
select count(1) from tbl_ts_ip_day_2017
— 83011233
 
 
select count(distinct tag1) from tbl_ts_ip_day_2017
— 24315442
— 479秒
 
 
 
 
以下是 8G RAM server 比較快
 
 
 
 
 
 
create user tsdb profile default
identified by password
default tablespace users
temporary tablespace temp
account unlock;

grant dba to tsdb;


root@43b4858cfdeb:~# exp userid=xxxx/yyyy@172.25.0.227/tsdb  tables=TBL_TS_IP_DAY_2017  file=/tmp/TBL_TS_IP_DAY_2017_$(date +%Y%m%d%s).dmp log=/tmp/log_database_$(date +%Y%m%d%s).log ROWS=Y




time imp tsdb/password file=/tmp/TBL_TS_IP_DAY_2017_201703231490242301.dmp  FULL=Y ignore=y feedback=1000000