1、监听日志清理
1)、进入$ORACLE_HOME/network/log,查看日志大小: du -a 2)、关闭监听记录日志信息:lsnrctl set log_status off 3)、备份监听日志:mv listener.log listener_`date +%Y%m%d`.log.bak 4)、重新设置,让监听记录日志:lsnrctl set log_status on
2、设置监听密码
禁用本地验证(10g以上):添加LOCAL_OS_AUTHENTICATION_LISTENER= OFF 设置密码: LSNRCTL> show current_listener LSNRCTL> set current_listener LISTENER LSNRCTL> change_password LSNRCTL> set password LSNRCTL> save_config LSNRCTL> save_status
3、静态监听配置
vi $ORACLE_HOME/network/admin/listener.ora ##单机监听在Oracle用户下 ##rac监听在grid用户下 LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Yong)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /opt/oracle/orabase/product/db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = emrep) (ORACLE_HOME = /opt/oracle/orabase/product/db_1) (SID_NAME = emrep) ) )
4、监听日志解析
####过滤监听日志中的ip地址 cat listener_zjhz-bjiagw-mdsp-rac01.log.2013bak|fgrep "establish"|awk -F* '{print $3}'|awk -F= '{ print $4}'|sed -e 's/......$//g'|sort |uniq -c|sort ####采集监听连接信息 fgrep "(CONNECT_DATA=(SERVER=" listener_zjhz-bjiagw-mdsp-rac03.log|fgrep "establish"|awk -F* '{print $3}'|awk -F= '{ print $4}'|sed -e 's/......$//g'|sort |uniq -c|sort 1.根据监听日志生成insert数据 grep 'establish' listener_zjhz-bjiagw-mdsp-rac03.log | sed 's/\*.*SERVICE_NAME=/ /g;s/).*tcp)(HOST=/ /g;s/).*$//g' \
| awk '{if(NF==4){print "insert into t_tab values('\''"$1"'\'','\''"$2"'\'','\''"$3"'\'','\''"$4"'\'');"}}' > /home/oracle/listener_m3_insert.sql 2.建表并入库数据 --a1 日期 --a2 时间 --a3 服务名 --a4 客户端ip Create table t_tab( a1 varchar2(50),a2 varchar2(50),a3 varchar2(50),a4 varchar2(50)); @/arch01/insert.26.sql Commit
5、连接串的配置
emrep= (DESCRIPTION_LIST = (LOAD_BALANCE = off) (FAILOVER = on) (DESCRIPTION = (ADDRESS_LIST = (LOAD_BALANCE=OFF) (FAILOVER=ON) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.212.200.76)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = emrep) (INSTANCE_NAME = emrep1) (FAILOVER_MODE=(TYPE=session)(METHOD=basic)(RETRIES=4)(DELAY=1)) ) ) (DESCRIPTION = (ADDRESS_LIST = (LOAD_BALANCE=OFF) (FAILOVER=ON) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.212.200.78)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = emrep) (INSTANCE_NAME = emrep2) (FAILOVER_MODE=(TYPE=session)(METHOD=basic)(RETRIES=4)(DELAY=1)) ) ) ) emrep= (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (LOAD_BALANCE=OFF) (FAILOVER=ON) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.212.200.76)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.212.200.77)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = emrep) (FAILOVER_MODE=(TYPE=session)(METHOD=basic)(RETRIES=4)(DELAY=1)) ) ) )
6、遇到的问题
出现两个相同的监听进程,其中一个是另一个的子进程 据ORACLE解释,在任何操作系统版本都有此问题。 现象:监听器启动后,隔一段时间(长短不定),就会出现无法连接: 若是用10201版本的SQLPLUS,则会出现 NO LISTENER。 9207 版本的SQLPLUS,则会出现:没反应,HANG住。 原因:10201 版本上的一个BUG:4518443。其会自动创建一个子监听器,当出现此情况时,监听器将会挂起。 /opt/oracle/product/10g/network/log/listener.log有如下语句: WARNING: Subscription for node down event still pending 检查是否真因为此BUG造成此现象: $ ps -ef | grep tnslsnr ora10g 8909 1 0 Sep 15 ? 902:44 /u05/10GHOME/DBHOME/bin/tnslsnr sales -inherit ora10g 22685 8909 0 14:19:23 ? 0:00 /u05/10GHOME/DBHOME/bin/tnslsnr sales –inherit 正常情况只有一个监听器,而此BUG则会出现两个监听器。 解决方法:打补丁4518443 或者在listener.ora 文件里加入: SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name>=OFF 其中,<listener_name> 是数据库的监听器的名称。如:默认情况下,监听器名为:LISTENER 。则语句就是: SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF 重启监听程序: lsnrctl stop lncrctl start