oracle数据库配置监听和TNS

1.配置监听的作用

可以在win的命令行窗口远程连接数据库

默认是有一个tns的，可以启动来远程连接数据库

[oracle@localhost ~]$ ps -ef | grep tns
oracle    4539  4506  0 21:18 pts/1    00:00:00 grep tns
[oracle@localhost ~]$ lsnrctl start    ##启动监听

查看监听的状态，显示如下则表示启动成功

[oracle@localhost ~]$ lsnrctl status
......
Service "OCPLHR1XDB" has 1 instance(s).
  Instance "OCPLHR1", status READY, has 1 handler(s) for this service...
The command completed successfully

如果监听的状态是下面这种情况（后两行），则执行下面的命令

[oracle@localhost ~]$ lsnrctl status
......
The listener supports no services
The command completed successfully
[oracle@localhost ~]$ sqlplus / as sysdba

执行下面这条命令，执行成功之后查看监听状态
SQL> alter system register;      
System altered.

如果执行这条命令时报下面的错误
SQL> alter system register;
alter system register
*
ERROR at line 1:
ORA-01034: ORAC
则先启动数据库，之后查看监听状态，如状态不正常再执行这条命令
SQL> startup;

2. 图形界面创建监听

配置监听之前的文件

[oracle@localhost network]$ cd $ORACLE_HOME/network/admin
[oracle@localhost admin]$ ll
drwxr-xr-x 2 oracle oinstall 4096 Jun 18 01:14 samples
-rw-r--r-- 1 oracle oinstall  205 May 11  2011 shrept.lst

使用这个命令来创建监听

[oracle@oracle ~]$ netmgr

选择第二个选项保存

配置完成之后的文件

[oracle@localhost network]$ cd $ORACLE_HOME/network/admin
[oracle@localhost admin]$ ll
total 12
-rw-r--r-- 1 oracle oinstall  293 Jun 18 22:09 listener.ora
drwxr-xr-x 2 oracle oinstall 4096 Jun 18 01:14 samples
-rw-r--r-- 1 oracle oinstall  205 May 11  2011 shrept.lst

3. 图形化创建TNS

[oracle@oracle ~]$ netmgr

配置完成之后，这个目录又多出来一个文件

[oracle@localhost network]$ cd $ORACLE_HOME/network/admin
[oracle@localhost admin]$ ll
total 16
-rw-r--r-- 1 oracle oinstall  293 Jun 18 22:09 listener.ora
drwxr-xr-x 2 oracle oinstall 4096 Jun 18 01:14 samples
-rw-r--r-- 1 oracle oinstall  205 May 11  2011 shrept.lst
-rw-r--r-- 1 oracle oinstall  367 Jun 18 22:19 tnsnames.ora

查看tnsnames.ora文件

[oracle@localhost admin]$ cat tnsnames.ora 
OCPLHR1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = OCPLHR1)
    )
  )

4. 手工配置TNS

添加OCPLHR2

[oracle@localhost admin]$ vim tnsnames.ora 
OCPLHR1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = OCPLHR1)
    )
  )
  
OCPLHR2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = OCPLHR2)
    )

再次打开图形界面，OCPLHR2已经出现

[oracle@localhost ~]$ netmgr