首页 > 代码库 > Linux下搭建oracle客户端环境远程连接数据库

Linux下搭建oracle客户端环境远程连接数据库

一、root用户下操作:(#root用户下,$oracle用户下)

1、从oracle官网下载两个rpm包放置任意路径下,使用rpm安装:

rpm -ivh oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm

rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64.rpm

默认安装在/usr/lib/oracle/这个路径下。

(注:下载前需要注册账号,免费的,可以放心注册)

 

配置环境变量:(我的配置文件)

#vim ~/.bash_profile

 

PATH=$PATH:$HOME/bin

export ORACLE_HOME=/usr/lib/oracle/11.2/client64/

export TNS_ADMIN=$ORACLE_HOME/network/admin

export ORACLE_BASE=/usr/lib/oracle/11.2/

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

export PATH

 

退出并保存

# source ~/.bash_profile (使其生效)

 

创建tnsnames.ora配置文件

#cd  /usr/lib/oracle/11.2/client64/ #cd $ORACLE_HOME

#mkdir -p /network/admin(创建这两个目录)

#vim /usr/lib/oracle/11.2/client64/network/admin/tnsnames.ora

 

1 JK1 =

2   (DESCRIPTION =

3     (ADDRESS_LIST =

4        (ADDRESS = (PROTOCOL = TCP)(HOST = 173.16.21.132)(PORT = 1521))

5      )

6        (CONNECT_DATA =http://www.mamicode.com/

7          (SERVER = DEDICATED)

8          (SERVICE_NAME = jk)

9        )

0      )

 

1:自己定义的连接描述符名。

4:HOST要远程连接的数据库的IPPORT端口默认1521

8:数据库的实例名。

(注:查看实例名# ps -ef |grep pmon

oracle    3894     1  0 08:30 ?        00:00:00 ora_pmon_jk

root      5154  4546  0 09:56 pts/1    00:00:00 grep pmon

jk就是实例名)

 

2、连接数据库:

# cd /usr/lib/oracle/11.2/client64/bin/

 

报错(1)

# ./sqlplus system/oracle@jk1 (system:用户名 oracle:密码 jk1:自己定义的连接描述符名)

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 27 09:56:54 2016

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

ERROR:

ORA-21561: OID generation failed

 

发现报错:ERROR:ORA-21561: OID generation failed

解决方法:

# hostname (查看机器名与127.0.0.1后的localhost不一致)

Cenos

# cat /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

# hostname  localhost (临时修改)

# hostname

localhost

# vim /etc/sysconfig/network (永久修改,重启生效)

1 NETWORKING=yes

2 HOSTNAME=localhost (修改后)

 

报错(2)

再次执行:

#./sqlplus system/oracle@jk1

 

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 27 10:08:11 2016

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

ERROR:

ORA-12541: TNS:no listener

 

发现报错:ERROR:ORA-12541: TNS:no listener

解决方法:检查数据库服务端是否起监听? 数据库是否起来?

 

$ lsnrctl start (开起监听)

$ sqlplus / as sysdba (进入数据库管理用户)

SQL*Plus: Release 10.2.0.4.0 - Production on чǚ?t 12? 27 10:17:40 2016

 

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

Connected to an idle instance.

 

SQL> startup (启动数据库)

ORACLE instance started.

 

Total System Global Area  226492416 bytes

Fixed Size     2082912 bytes

Variable Size   184551328 bytes

Database Buffers    33554432 bytes

Redo Buffers     6303744 bytes

Database mounted.

Database opened.

SQL> (数据库起来了!!)

 

报错(3)

再次执行:

# ./sqlplus system/oracle@jk1

 

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 27 10:15:06 2016

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

ERROR:

ORA-12543: TNS:destination host unreachable

发现报错:ERROR:ORA-12543: TNS:destination host unreachable

解决方法:应该是服务端防火墙开着,关闭防火墙。

# service iptables stop (即时生效,重启复原)

清除防火墙规则:                                         [  确定  ]

chains 设置为 ACCEPT 策略:filter                             [  确定  ]

正在卸载 Iiptables 模块:                      [  确定  ]

 

成功(4)

再次执行:

# ./sqlplus system/oracle@jk1

 

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 27 10:18:26 2016

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL>  

连接成功!!

 

二、创建oralce用户和oinstall组,方便管理数据库:

(1)

创建组:

 

#groupadd oinstall

#groupadd dba

 

创建一个用户主目录/home/oracle

 

#mkdir -p /home/oracle

 

创建用户oracle 到主组oinstall,副组dba,主目录/home/oracle:

 

#useradd -g oinstall -G dba -d /home/oracle/  oracle

 

 

查看用户oracle的添加情况:

 

# id oracle

uid=501(oracle) gid=503(oinstall) =503(oinstall),504(dba)

 

/usr/lib/oracle/oracle目录拷贝至/home/oracle

 

#cp -r /usr/lib/oracle/  /home/oracle

 

/home的所有者改为oracle

 

#chown -R oracle:oinstall /home

#ll /home/ (查看)

oracle oinstall 4096 1226 19:14 oracle

 

报错(2)

#su - oracle (进入oracle用户)

-bash-3.2$

而并不是

[oracle@Cenos ~]$

 

解决方法: oracle用户下缺少.bash*等配置文件,将root用户下都拷贝过来

#ll -a ~ (查看root用户下.bash配置文件有哪些)

-rw-------.  1 root root    14941 1227 10:24 .bash_history

-rw-r--r--.  1 root root       18 5月  20 2009 .bash_logout

-rw-r--r--.  1 root root      368 1226 18:16 .bash_profile

-rw-r--r--.  1 root root      176 9月  23 2004 .bashrc

将这些都复制到/home/oracle/下并chown更改其用户分组为oracle:oinstall

 

再次:

# su - oracle

[oracle@Cenos ~]$ pwd (正常了)

/home/oracle/

 

报错(3)以下都是在oracle用户下执行

开始连接数据库:

$ sqlplus system/oracle@jk1

-bash: sqlplus: command not found

 

发现问题:sqlplus命令没有找到,应该是没有配置好环境变量

解决方法:

$ vim ~/.bash_profile

(添加这个)

export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

退出并保存

$ source ~/.bash_profile (使其生效)

 

成功(4)

再次执行:

$ sqlplus system/oracle@jk1

 

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 27 11:05:09 2016

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL>

 

连接成功!!

 

最近搭建oracle客户端环境就整理下遇到的报错和解决方法,大神路过如果发现不对之处希望指出!!

Linux下搭建oracle客户端环境远程连接数据库