tns配置能连接mysql吗_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