sqlplus 链接数据库

实验目的：在虚拟机中用sqlplus工具访问真实机的数据库；

实验环境：

真实机（windows系统，数据库服务名 orcl）：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

虚拟机（linux系统，数据库服务名 yjgocp）：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

sqlplus登录本地及远程数据库的方式有：

sqlplus username/password  

sqlplususername/password@net_service_name

sqlplus  username/password assysdba

sqlplususername/password@//host:port/sid 

在虚拟机上操作,直接链接真实机数据库

[oracle@localhost ~]$ sqlplusscott/tiger@orcl

SQL*Plus: Release 11.2.0.1.0 Production onSun Jul 28 13:40:38 2013

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

ERROR:

ORA-12154: TNS:could not resolve theconnect identifier specified

 说明链接信息配置有问题，查看本地环境变量及查找本地tnsname.ora 

[oracle@localhost ~]$ more .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

       . ~/.bashrc

fi

# User specific environment and startupprograms

 

PATH=$PATH:$HOME/bin

export ORACLE_BASE=/u01/app/oracle

exportORACLE_HOME=/u01/app/oracle/product/11g

export ORACLE_SID=yjgocp

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

export PATH=$ORACLE_HOME/bin:$PATH

[root@localhost script]#find / -name tnsname*

/u01/app/oracle/product/11g/inventory/Templates/hs/admin/tnsnames.ora.sample

/u01/app/oracle/product/11g/network/admin/samples/tnsnames.ora

/u01/app/oracle/product/11g/hs/admin/tnsnames.ora.sample

 

TNS_ADMIN

This variable  TNS_ADMIN specifies the user ID of the OracleNet Services configuration files, for example, LISTENER.ORA, TNSNAMES.ORA and SQLNET.ORA. If TNS_ADMIN is not defined, then the configuration files are searched underthe local user ID with the prefix NETWORK.ADMIN.

环境变量里面没有设置该值，说明oracle 会到默认的network/admin/下查找LISTENER.ORA, TNSNAMES.ORA and SQLNET.ORA文件，如果想链接远程数据库，那么需要在该位置创建一个TNSNAME.ORA文件，并把远程的链接信息配置到该文件中；

 

经过刚才查找得之/u01/app/oracle/product/11g/network/admin/samples/tnsnames.ora把该文件拷贝到/u01/app/oracle/product/11g/network/admin/目录下一份，

然后添加远程数据库的链接信息：

[oracle@localhost admin]$ more tnsnames.ora

# This file contains the syntax informationfor

# the entries to be put in any tnsnames.orafile

# The entries in this file are need based.

# There are no defaults for entries in thisfile

# that Sqlnet/Net3 use that need to beoverridden

#

# Typically you could have two tnsnames.orafiles

# in the system, one that is set for theentire system

# and is called the system tnsnames.orafile, and a

# second file that is used by each userlocally so that

# he can override the definitions dictatedby the system

# tnsnames.ora file.

 

# The entries in tnsnames.ora are analternative to using

# the names server with the onames adapter.

# They are a collection of aliases for theaddresses that

# the listener(s) is(are) listening for adatabase or

# several databases.

 

# The following is the general syntax forany entry in

# a tnsnames.ora file. There could beseveral such entries

# tailored to the user's needs.

#下面红色字体是添加的远程链接信息；其余代码是该文件原有的代码；

ORCL =

  (DESCRIPTION =

     (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.100)(PORT = 1521))

     (CONNECT_DATA =

       (SERVICE_NAME = orcl)

     )

   )

<alias>= [ (DESCRIPTION_LIST =  # Optional depending on whether u have

                                # one or moredescriptions

                                # If there isjust one description, unnecessary

]

         (DESCRIPTION=

           [ (SDU=2048) ]      # Optional,defaults to 2048

                                # Can take values between 512 and 32K

           [ (ADDRESS_LIST=    # Optionaldepending on whether u have

                                # one or moreaddresses

                                # If there isjust one address, unnecessary ]

             (ADDRESS=

                [(COMMUNITY=<community_name>) ]

                (PROTOCOL=tcp)

                (HOST=<hostname>)

                (PORT=<portnumber (1521 is astandard port used)>)

              )

              [ (ADDRESS=

                  (PROTOCOL=ipc)

                  (KEY=<ipckey (PNPKEY is astandard key used)>)

                )

              ]

              [ (ADDRESS=

                  [(COMMUNITY=<community_name>) ]

                  (PROTOCOL=decnet)

                  (NODE=<nodename>)

                  (OBJECT=<objectname>)

                )

              ]

              ... # More addresses

           [ ) ] # Optional depending on whether ADDRESS_LIST is used or not

           [ (CONNECT_DATA=

                (SID=<oracle_sid>)

                [(GLOBAL_NAME=<global_database_name>) ]

              )

           ]

           [ (SOURCE_ROUTE=yes) ] 

         )

         (DESCRIPTION=    

           [ (SDU=2048) ]      # Optional,defaults to 2048

                                # Can takevalues between 512 and 32K

           [ (ADDRESS_LIST= ]  # Optionaldepending on whether u have more

                                # than oneaddress or not

                                # If there is justone address, unnecessary

              (ADDRESS

                [(COMMUNITY=<community_name>) ]

                (PROTOCOL=tcp)

                (HOST=<hostname>)

                (PORT=<portnumber (1521 is astandard port used)>)

              )

             [ (ADDRESS=

                  (PROTOCOL=ipc)

                  (KEY=<ipckey (PNPKEY is astandard key used)>)

                 )

              ]

              ...               # More addresses

           [ ) ]               # Optionaldepending on whether ADDRESS_LIST 

                                # is being used

           [ (CONNECT_DATA=

                (SID=<oracle_sid>)

                [(GLOBAL_NAME=<global_database_name>) ]

             )

           ]

           [ (SOURCE_ROUTE=yes) ]

         )

         [ (CONNECT_DATA=

              (SID=<oracle_sid>)

              [(GLOBAL_NAME=<global_database_name>) ]

           )

         ]

         ...   # More descriptions

       [ ) ]   # Optional depending onwhether DESCRIPTION_LIST is used or not

 

然后尝试链接

[oracle@localhost admin]$ sqlplusscott/tiger@orcl

SQL*Plus: Release 11.2.0.1.0 Production onSun Jul 28 13:59:59 2013

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

 

ERROR:

ORA-12541: TNS:nolistener

 

提示没有监听，这个时候查看远程数据库，监听已经启动；于是就把链接信息里的HOST信息又远程主机IP改为了主机名（由HOST = 192.168.1.100改为

HOST = 4728tef987uid34）

[oracle@localhost admin]$ sqlplusscott/tiger@orcl

 

SQL*Plus: Release 11.2.0.1.0 Production onSun Jul 28 14:02:38 2013

 

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

 

Connected to:

Oracle Database 10g Enterprise EditionRelease 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Miningoptions

 

Ok 链接成功；

另外用下面的方法也可以在没有配置tnsname.ora的情况下访问；

[oracle@localhost ~]$ sqlplus scott/tiger@//192.168.1.100/orcl

SQL*Plus: Release 11.2.0.1.0 Production onSun Jul 28 13:45:43 2013

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

Connected to:

Oracle Database 10g Enterprise EditionRelease 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Miningoptions

[oracle@localhost ~]$ sqlplus scott/tiger@//192.168.1.100/orcl

SQL*Plus: Release 11.2.0.1.0 Production onSun Jul 28 13:45:43 2013

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

 

Connected to:

Oracle Database 10g Enterprise EditionRelease 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Miningoptions