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 -ysudo yum install gcc -ysudo yum install gcc-c++ -ysudo yum install glibc -ysudo yum install glibc-common -ysudo yum install glibc-devel -ysudo yum install glibc-headers -ysudo yum install kernel-headers -ysudo yum install ksh -ysudo yum install libaio -ysudo yum install libaio-devel -ysudo yum install libgcc -ysudo yum install libgomp -ysudo yum install libstdc++ -ysudo yum install libstdc++-devel -ysudo yum install make -ysudo yum install numactl-devel -ysudo yum install sysstat -ysudo yum install compat-libstdc++ -ysudo yum install elfutils-libelf-devel -ysudo yum install elfutils-libelf-devel-static -ysudo yum install libaio-devel -ysudo yum install unixODBC.x86_64 -y sudo yum install glibc.i686 -ysudo yum install libXext.i686 -ysudo yum install compat-libstdc++-33.x86_64 -ysudo yum install unixODBC.i686 -y

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

:<<BLOCKBLOCKgroupadd oinstalluseradd -s /bin/bash -m -g oinstall oracle passwd oracle #there should 8 len password ,so we used :innpaydbxhost local:oracleecho "export ORACLE_SID=orcl" >> /etc/profileecho "export ORACLE_BASE=/home/oracle/app">>/etc/profileecho "export ORACLE_HOME=\$ORACLE_BASE/product/11.2.0/dbhome_1">>/etc/profileecho "export PATH=\$PATH:\$ORACLE_HOME/bin">>/etc/profileecho "export ORACLE_UNQNAME=\$ORACLE_SID">>/etc/profileecho "export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/usr/lib:/usr/local/lib">>/etc/profilesu - oracle -c "source /etc/profile"echo "fs.file-max= 6815744" >> /etc/sysctl.confecho "fs.aio-max-nr=1048576" >> /etc/sysctl.confecho "net.ipv4.ip_local_port_range=9000 65500" >> /etc/sysctl.confecho "net.core.rmem_default=262144" >> /etc/sysctl.confecho "net.core.rmem_max=4194304" >> /etc/sysctl.confecho "net.core.wmem_default=262144" >> /etc/sysctl.confecho "net.core.wmen_max=1048576" >> /etc/sysctl.confecho "kernel.sem=250 32000 100 128" >> /etc/sysctl.confecho "kernel.shmall=2097152" >> /etc/sysctl.confecho "kernel.shmmax=536870912" >>  /etc/sysctl.confsysctl -pecho "oracle soft nproc 2047" >> /etc/security/limits.confecho "oracle hard nproc 16384" >> /etc/security/limits.confecho "oracle soft nofile 1024" >> /etc/security/limits.confecho "oracle hard nofile 65536" >> /etc/security/limits.confecho "session required pam_limits.so" >> "/etc/pam.d/login"

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

export LANG=en_USexport 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 directory1.export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/local/lib2.如果还不行的话    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 tmpstempfile '/home/oracle/ora_dbf/tmps.dbf'size 50mautoextend onnext 50m maxsize 20480mextent management local;

3. 创建数据表空间

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

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

create user innpay identified by innpaydefault tablespace TBS_INNPAYtemporary 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-servicessudo systemctl enable iptables && sudo systemctl enable ip6tablessudo 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;