oracle 安装和配置

oracle配置相关

标签（空格分隔）： 知识库 系统集成 Oracle

---

fedora23安装oracle数据库

安装过程

• 安装依赖

sudo yum install binutils -y
#sudo yum install compat-libstdc++ -y
#sudo yum install libelf -y
#sudo yum install libelf-devel -y
#sudo yum install libelf-devel-static -y
sudo yum install gcc -y
sudo yum install gcc-c++ -y
sudo yum install glibc -y
sudo yum install glibc-common -y
sudo yum install glibc-devel -y
sudo yum install glibc-headers -y
sudo yum install kernel-headers -y
sudo yum install ksh -y
sudo yum install libaio -y
sudo yum install libaio-devel -y
sudo yum install libgcc -y
sudo yum install libgomp -y
sudo yum install libstdc++ -y
sudo yum install libstdc++-devel -y
sudo yum install make -y
sudo yum install numactl-devel -y
sudo yum install sysstat -y
sudo yum install compat-libstdc++ -y
sudo yum install elfutils-libelf-devel -y
sudo yum install elfutils-libelf-devel-static -y
sudo yum install libaio-devel -y
sudo yum install unixODBC.x86_64 -y 
sudo yum install glibc.i686 -y
sudo yum install libXext.i686 -y
sudo yum install compat-libstdc++-33.x86_64 -y
sudo yum install unixODBC.i686 -y

• 添加oracle用户并配置环境变量和系统参数

:<<BLOCK
BLOCK
groupadd oinstall
useradd -s /bin/bash -m -g oinstall oracle 
passwd oracle 
#there should 8 len password ,so we used :innpaydb
xhost local:oracle
echo "export ORACLE_SID=orcl" >> /etc/profile
echo "export ORACLE_BASE=/home/oracle/app">>/etc/profile
echo "export ORACLE_HOME=\$ORACLE_BASE/product/11.2.0/dbhome_1">>/etc/profile
echo "export PATH=\$PATH:\$ORACLE_HOME/bin">>/etc/profile
echo "export ORACLE_UNQNAME=\$ORACLE_SID">>/etc/profile
echo "export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/usr/lib:/usr/local/lib">>/etc/profile
su - oracle -c "source /etc/profile"
echo "fs.file-max= 6815744" >> /etc/sysctl.conf
echo "fs.aio-max-nr=1048576" >> /etc/sysctl.conf
echo "net.ipv4.ip_local_port_range=9000 65500" >> /etc/sysctl.conf
echo "net.core.rmem_default=262144" >> /etc/sysctl.conf
echo "net.core.rmem_max=4194304" >> /etc/sysctl.conf
echo "net.core.wmem_default=262144" >> /etc/sysctl.conf
echo "net.core.wmen_max=1048576" >> /etc/sysctl.conf
echo "kernel.sem=250 32000 100 128" >> /etc/sysctl.conf
echo "kernel.shmall=2097152" >> /etc/sysctl.conf
echo "kernel.shmmax=536870912" >>  /etc/sysctl.conf
sysctl -p
echo "oracle soft nproc 2047" >> /etc/security/limits.conf
echo "oracle hard nproc 16384" >> /etc/security/limits.conf
echo "oracle soft nofile 1024" >> /etc/security/limits.conf
echo "oracle hard nofile 65536" >> /etc/security/limits.conf

echo "session required pam_limits.so" >> "/etc/pam.d/login"

• 若出现安装时中文乱码，可直接用 export 设置临时环境变量的语言环境为英文。

export LANG=en_US
export LC_ALL=en_US

• 设置下字符集

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter system enable restricted session;
SQL> alter system set job_queue_processes=0;
SQL> alter database open;
SQL> alter database character set internal_use ZHS16GBK;
SQL> shutdown immediate;
SQL> startup

• 查看监听状态

lsnrctl status/start/stop/restart

• 调用sqlplus出错

proc: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory
1.export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/local/lib
2.如果还不行的话
    chmod -R ugoa+rwxX oracle

oracle 卸载

1. 使用SQL*PLUS停止数据库
   [oracle@OracleTest oracle]$ sqlplus /nolog
   SQL> connect / as sysdba
   SQL> shutdown [immediate]
   SQL> exit

2. 停止Listener
   [oracle@OracleTest oracle]$ lsnrctl stop

3. 停止HTTP服务
   [root@OracleTest /root]# service httpd stop

4. 用su或者重新登录到root(如想重新安装可以保留oracle用户，省得输入环境变量了)

5. 将安装目录删除
   [root@OracleTest /root]# rm -rf /u01/app/oracle/

6. 将/usr/bin下的文件删除
   [root@OracleTest /root]# rm /usr/local/bin/dbhome
   [root@OracleTest /root]# rm /usr/local/bin/oraenv
   [root@OracleTest /root]# rm /usr/local/bin/coraenv

7. 将/etc/oratab删除
   [root@OracleTest /root]# rm /etc/oratab

8. 将/etc/oraInst.loc删除
   [root@OracleTest /root]# rm /etc/oraInst.loc

9. 将oracle用户删除(若要重新安装,可以不删除)
   [root@OracleTest /root]# userdel –r oracle

10. 将用户组删除(若要重新安装,可以不删除)
    [root@OracleTest /root]# groupdel oinstall
    [root@OracleTest /root]# groupdel dba

11. 将启动服务删除
    [root@OracleTest /root]# chkconfig –del dbora
    到此为止重启后，你的Linux系统下的Oracle数据库已完全删除了！！！

如果要再次安装， 最好先做一些备份工作。
包括用户的登录脚本，数据库自动启动关闭的脚本，和Listener自动启动的脚本。
要是有可能连创建数据库的脚本也保存下来。

异常情况

新建用户及授权

1. 删除user

drop user ×× cascade

2. 创建临时表空间

create temporary tablespace tmps
tempfile '/home/oracle/ora_dbf/tmps.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

3. 创建数据表空间

--先在老数据库查看当前用户建立了什么表空间
select distinct t.tablespace_name from user_all_tables t;
--建立表空间
create tablespace TBS_INNPAY
logging
datafile '/home/oracle/ora_dbf/TBS_INNPAY.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

4. 创建用户并指定表空间

create user innpay identified by innpay
default tablespace TBS_INNPAY
temporary tablespace tmps;

5. 给用户授予权限

grant connect,resource,dba,IMP_FULL_DATABASE,EXP_FULL_DATABASE,
UNLIMITED TABLESPACE
 to innpay;

监听配置

1. 配置本地监听

进到$ORACLE_HOME/network/admin下
从其子目录sample 中复制出listern.ora 和tnsname.ora复制到本层
在本层listern.ora 中加入以下数据

listern.ora 添加
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (SID_NAME = orcl)
      (ORACLE_HOME = /home/orcale/app/product/11.2.0/dbhome_1)
      (PRESPAWN_MAX = 20)
      (PRESPAWN_LIST =
        (PRESPAWN_DESC =
          (PROTOCOL = tcp)
          (POOL_SIZE = 2)
          (TIMEOUT = 1)
        )
      )
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

2. 在tnsnames.ora中加入本地监听连接

orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

3. 异常情况
① 先ping 看是否能ping通该地址
② 查看该端口是否启用
~:netstat -tln
③ 在客户机使用tnsping [地址] 看看能否连接到该端口
错误分支：

1. 先关掉防火墙试试，再由window stnsping
   ~:service iptables stop
   若能tnsping通则需关注两个点：
2. /etc/sysconfig/iptables中是否开放了1521端口
   -A INPUT -p tcp -m state –state NEW -m tcp –dport 1521 -j ACCEPT
3. /etc/sysconfig/iptables中是否有全面拦截的规则
   -A INPUT -j REJECT –reject-with icmp-host-prohibited
   有的话请在前边加上#将其注释，意思大家可以上网查，具体风险还不太楚
   ~:service iptables restart
   ~:iptables -L -n 查看该端口是否放开
   ~:service iptables save
4. lsnrctl restart 重启监听

iptables错误
sudo yum install iptables-services
sudo systemctl enable iptables && sudo systemctl enable ip6tables
sudo systemctl start iptables 

密码管理

• 当用户被锁定时

alter user innpay account unlock identified by InnPAY20161031;
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited/120/;
select count(*) from 
(select rownum,sessionid,userid,userhost,comment$text,spare1,to_char(ntimestamp#+1/3,'yyyy-mm-dd hh24:mi:ss') from aud$ where returncode=1017 order by ntimestamp# desc);

select * from 
(select rownum,userid,userhost,to_char(ntimestamp#+1/3,'yyyy-mm-dd hh24:mi:ss') from aud$ where returncode=1017 order by ntimestamp# desc)
where rownum <= 50;