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 卸载
使用SQL*PLUS停止数据库
[oracle@OracleTest oracle]$ sqlplus /nolog
SQL> connect / as sysdba
SQL> shutdown [immediate]
SQL> exit停止Listener
[oracle@OracleTest oracle]$ lsnrctl stop停止HTTP服务
[root@OracleTest /root]# service httpd stop用su或者重新登录到root(如想重新安装可以保留oracle用户,省得输入环境变量了)
将安装目录删除
[root@OracleTest /root]# rm -rf /u01/app/oracle/将/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将/etc/oratab删除
[root@OracleTest /root]# rm /etc/oratab将/etc/oraInst.loc删除
[root@OracleTest /root]# rm /etc/oraInst.loc将oracle用户删除(若要重新安装,可以不删除)
[root@OracleTest /root]# userdel –r oracle将用户组删除(若要重新安装,可以不删除)
[root@OracleTest /root]# groupdel oinstall
[root@OracleTest /root]# groupdel dba将启动服务删除
[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 [地址] 看看能否连接到该端口
错误分支:
- 先关掉防火墙试试,再由window stnsping
~:service iptables stop
若能tnsping通则需关注两个点:- /etc/sysconfig/iptables中是否开放了1521端口
-A INPUT -p tcp -m state –state NEW -m tcp –dport 1521 -j ACCEPT- /etc/sysconfig/iptables中是否有全面拦截的规则
-A INPUT -j REJECT –reject-with icmp-host-prohibited
有的话请在前边加上#将其注释,意思大家可以上网查,具体风险还不太楚
~:service iptables restart
~:iptables -L -n 查看该端口是否放开
~:service iptables save- 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;