Docker: oracle_xe_10g 数据库 运行sqlplus 时Message file sp1<lang>.msb not found 错误解决办法_20160513_七侠镇莫尛貝

使用了https://hub.docker.com/r/dkfi/docker-oracle-xe-10g/ 提供的镜像搭建oracle_xe_10g容器。

使用一个以前写的脚本 recreate_orcl_tb_and_user.sh 来自动重建表空间和用户：

#!/bin/sh

ORACLE_BASE=/usr/lib/oracle/xe/app/oracle
ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
ORACLE_SID=XE
export ORACLE_BASE ORACLE_HOME ORACLE_SID

oraclehome=$ORACLE_HOME
echo $oraclehome

su - oracle -c "/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/sqlplus /nolog" <<EOF
connect / as sysdba
drop user $2 cascade;
drop tablespace $1 including contents and datafiles;

CREATE  TABLESPACE "$1" DATAFILE '/usr/lib/oracle/xe/oradata/XE/$1' SIZE 1024 M AUTOEXTEND ON NEXT 100 M ;
create user $2 identified by "<span style="color:#FF0000;">userpassword123</span>" default tablespace $1 temporary tablespace TEMP profile DEFAULT;
grant connect to $2;
grant resource to $2;
grant create any table to $2;
grant create any trigger to $2;
grant create any type to $2;
grant create any view to $2;
grant unlimited tablespace to $2;
exit
EOF
 
crdrresult=$?
echo ""
if [ "$crdrresult" != "0" ];then
        echo "create tablespace/user $1/$2 failure!!!"
else
        echo "create tablespace/user $1/$2 OK!!!"
fi

用法：root下运行recreate_orcl_tb_and_user.sh 表空间名 用户名

提示报错：

/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
Error 6 initializing SQL*Plus
Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

create tablespace/user UTS/UTS failure!!!

找到一个资料：http://biancheng.dnbcw.info/sql/365879.html

登录docker，切换到oracle用户，发现oracle用户没有自己的.bash_profile，

root@oracle_xe_10g:~# su - oracle
oracle@oracle_xe_10g:~$ cd
oracle@oracle_xe_10g:~$ pwd
/usr/lib/oracle/xe
oracle@oracle_xe_10g:~$ ls -la
total 32
drwxr-xr-x 7 oracle dba  4096 May 13 14:18 .
drwxr-xr-x 5 root   root 4096 May 13 13:04 ..
-rw------- 1 oracle dba  1051 May 13 14:17 .bash_history
-rw------- 1 oracle dba  5436 May 13 14:13 .viminfo
drwxr-xr-x 5 oracle dba  4096 May 13 13:04 app
drwxr-xr-x 4 oracle dba  4096 May 13 13:04 oradata
oracle@oracle_xe_10g:~$ vi .bash_profile

那就手动新建一个：

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

ORACLE_BASE=/usr/lib/oracle/xe/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/server
ORACLE_SID=XE
export ORACLE_BASE ORACLE_HOME ORACLE_SID

PATH=$ORACLE_HOME/bin:$PATH
export PATH

试一试：root下运行 recreate_orcl_tb_and_user.sh MYDB MYDB

表空间和用户MYDB创建成功，密码：userpassword123

问题原因：这个容器版的oracle_xe_10g不同于通过常规方法安装的oracle，oracle用户缺少.bash_profile文件，脚本运行运行时找不到相关系统变量，补上即可。

=====================================================================

续：

这个容器版Oralce_xe_10g默认的字符集是WE8MSWIN1252，不支持中文。

参考：http://blog.mcshell.org/2013/06/09/oracle-chs-character-al32utf-8.html

运行

alter database character set internal_use  AL32UTF-8  ;

时报错： ORA-12712: new character set must be a superset of old character set

参考：http://blog.csdn.net/nsj820/article/details/6571105

改为运行：alter database character set internal_use utf8;

OK!