Base-Oracle-2

TIPTOP数据库管理（Oracle）（二）讲师：梁保明广州鼎捷软件有限公司TIPTOP事业部课程大纲网络原理客户端配置lsnrctl工具介绍sqlplus工具介绍网络原理怎样配置一个数据库连接怎样诊断数据库中出现的问题Oracle数据库有哪些环境变量课程简介Oracle网络原理监听器服务器客户端Dedicated服务器进程123①客户端请求连接②监听器响应请求并创建一个服务器进程③客户端通过服务器进程对实例进行操作网络配置文件listener.ora监听器客户端服务器tnsnames.orasqlnet.ora网络配置文件服务配置文件监听配置文件上述文件位于$ORACLE_HOME/network/admin/目录下配置监听器全部使用默认设置即可监听配置文件（listener.ora）LISTENER=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=topprod)(PORT=1521)))(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))))SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=PLSExtProc)(ORACLE_HOME=D:\oracle\ora92)(PROGRAM=extproc)))LISTENER=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=topprod)(PORT=1521)))(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))))SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=PLSExtProc)(ORACLE_HOME=D:\oracle\ora92)(PROGRAM=extproc)))配置命名方法命名方法配置文件（sqlnet.ora）SQLNET.AUTHENTICATION_SERVICES=(NTS)NAMES.DIRECTORY_PATH=(TNSNAMES,ONAMES,HOSTNAME)SQLNET.AUTHENTICATION_SERVICES=(NTS)NAMES.DIRECTORY_PATH=(TNSNAMES,ONAMES,HOSTNAME)配置Net服务这里填入要连接的ORACLE_SID这里填入服务器IP要进行测试出现这种错误，检查服务器端的监听器是否打开出现这种错误，检查前面输入的ORACLE_SID是否正确出现这种错误，检查system用户的密码这里输入net服务名Net服务配置文件（tnsnames.ora）TOPDB=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.180.129)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=topdb)))TOPDB=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.180.129)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=topdb)))可以直接编辑该文件来配置网络服务（蓝色部分为需设定项目）Net服务测试工具tnspingtnsping[服务名]C:\DocumentsandSettings\LifengtnspingtopdbTNSPingUtilityfor32-bitWindows:Version9.2.0.1.0-Productionon10-2月-200821:53:51Copyright(c)1997OracleCorporation.Allrightsreserved.已使用的参数文件:D:\oracle\ora92\network\admin\sqlnet.ora已使用TNSNAMES适配器来解析别名Attemptingtocontact(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.180.129)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=topdb)))OK（0毫秒）C:\DocumentsandSettings\LifengC:\DocumentsandSettings\LifengtnspingtopdbTNSPingUtilityfor32-bitWindows:Version9.2.0.1.0-Productionon10-2月-200821:53:51Copyright(c)1997OracleCorporation.Allrightsreserved.已使用的参数文件:D:\oracle\ora92\network\admin\sqlnet.ora已使用TNSNAMES适配器来解析别名Attemptingtocontact(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.180.129)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=topdb)))OK（0毫秒）C:\DocumentsandSettings\Lifeng监听器管理工具lsnrctl[start/stop/status/reload/help][lsnr_name]lsnrctlLSNRCTL[command]LSNRCTLquitlsnrctlLSNRCTL[command]LSNRCTLhelp命令行方式提示符方式startstopstatusservicesversionreloadsave_configtracespawnchange_passwordquitexitset*show*startstopstatusservicesversionreloadsave_configtracespawnchange_passwordquitexitset*show*Lsnrctl的另一个作用：当管理员需要对oracle进行一些操作时，为了防止TIPTOP用户登录和使用，可以使用lsnrctlstop停掉监听器，这样可以在不关闭服务器的情况下避免客户端登入监听器管理工具LSNRCTLset[option]LSNRCTLsetLSNRCTLshow[option]LSNRCTLshowLSNRCTLset[option]LSNRCTLsetLSNRCTLshow[option]LSNRCTLshowpasswordrawmodeDisplaymodetrc_filetrc_directorytrc_levellog_filelog_directorylog_statuscurrent_listenerstartup_waittimesave_config_on_stoppasswordrawmodeDisplaymodetrc_filetrc_directorytrc_levellog_filelog_directorylog_statuscurrent_listenerstartup_waittimesave_config_on_stop设置/显示选项跟踪监听器异常维护listener.ora，增加trace设定LISTENER=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=topprod)(PORT=1521)))(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))))SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=PLSExtProc)(ORACLE_HOME=D:\oracle\ora92)(PROGRAM=extproc)))TRACE_LEVEL_LISTENER=16TRACE_DIRECTORY_LISTENER=/u1/outTRACE_FILE_LISTENER=listenerLISTENER=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=topprod)(PORT=1521)))(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))))SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=PLSExtProc)(ORACLE_HOME=D:\oracle\ora92)(PROGRAM=extproc)))TRACE_LEVEL_LISTENER=16TRACE_DIRECTORY_LISTENER=/u1/outTRACE_FILE_LISTENER=listener设定跟踪级别设定trace文件位置设定trace文件名/u2/oracle/10g/network/adminlsnrctlstartLSNRCTLforLinux:Version10.2.0.1.0-Productionon18-FEB-200817:55:25Copyright(c)1991,2005,Oracle.Allrightsreserved.Starting/u2/oracle/10g/bin/tnslsnr:pleasewait...TNSLSNRforLinux:Version10.2.0.1.0-ProductionSystemparameterfileis/u2/oracle/10g/network/admin/listener.oraLogmessageswrittento/u2/oracle/10g/network/log/listener.logErrorlisteningon:(ADDRESS=(PROTOCOL=ipc)(PARTIAL=yes)(QUEUESIZE=1))Nolongerlisteningon:(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=heliserver3)(PORT=1521)))TNS-12546:TNS:permissiondeniedTNS-12560:TNS:protocoladaptererrorTNS-00516:PermissiondeniedLinuxError:30:Read-onlyfilesystemListenerfailedtostart.Seetheerrormessage(s)above.../u2/oracle/10g/network/adminlsnrctlstartLSNRCTLforLinux:Version10.2.0.1.0-Productionon18-FEB-200817:55:25Copyright(c)1991,2005,Oracle.Allrightsreserved.Starting/u2/oracle/10g/bin/tnslsnr:pleasewait...TNSLSNRforLinux:Version10.2.0.1.0-ProductionSystemparameterfileis/u2/oracle/10g/network/admin/listener.oraLogmessageswrittento/u2/oracle/10g/network/