oracle 10g rac当监听程序监听对方vip时启动监听报错TNS-12545

rac中各节点监听程序本来只监听当前节点的vip 和public ip,不知为何客户的rac各个节点配置成监听自己节点和对方节点的vip
连接rac数据库报TNS-12545错误
登录数据库服务器执行crs_stat -t

[oracle@keqsi1 admin]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.keqjm.db   application    ONLINE    ONLINE    keqsi1
ora....m1.inst application    ONLINE    ONLINE    keqsi1
ora....m2.inst application    ONLINE    ONLINE    keqsi2
ora....orcl.cs application    ONLINE    ONLINE    keqsi2
ora....jm1.srv application    ONLINE    ONLINE    keqsi1
ora....jm2.srv application    ONLINE    ONLINE    keqsi2
ora....SM1.asm application    ONLINE    ONLINE    keqsi1
ora....I1.lsnr application    ONLINE    OFFLINE
ora.keqsi1.gsd application    ONLINE    ONLINE    keqsi1
ora.keqsi1.ons application    ONLINE    ONLINE    keqsi1
ora.keqsi1.vip application    ONLINE    ONLINE    keqsi1
ora....SM2.asm application    ONLINE    ONLINE    keqsi2
ora....I2.lsnr application    ONLINE    OFFLINE
ora.keqsi2.gsd application    ONLINE    ONLINE    keqsi2
ora.keqsi2.ons application    ONLINE    ONLINE    keqsi2
ora.keqsi2.vip application    ONLINE    ONLINE    keqsi2

检查listener.ora文件:10.53.1.237和10.53.1.238是两个节点
的虚拟IP地址

[oracle@keqsi2 admin]$ more listener.ora
# listener.ora.keqsi2 Network Configuration File: /u01/app/oracle/10gR2/db/network/admin/listener.ora.keqsi2
# Generated by Oracle configuration tools.

LISTENER_KEQSI2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.53.1.237)(PORT = 1521)(IP = FIRST))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.53.1.238)(PORT = 1521)(IP = FIRST))
    )
  )

SID_LIST_LISTENER_KEQSI2 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/10gR2/db)
      (PROGRAM = extproc)
    )
  )

检查网络是否能拼通

[oracle@keqsi2 admin]$ hostname
keqsi2
[oracle@keqsi2 admin]$ more /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
#127.0.0.1       keqsi2  localhost.localdomain   localhost
127.0.0.1       localhost.localdomain    localhost
172.18.20.1     keqsi1-priv
172.18.20.2     keqsi2-priv
10.53.1.230     keqsi1
10.53.1.237     keqsi1-vip
10.53.1.231     keqsi2
10.53.1.238     keqsi2-vip
[oracle@keqsi2 admin]$ ping keqsi1
PING keqsi1 (10.53.1.230) 56(84) bytes of data.
64 bytes from keqsi1 (10.53.1.230): icmp_seq=0 ttl=64 time=0.131 ms
64 bytes from keqsi1 (10.53.1.230): icmp_seq=1 ttl=64 time=0.125 ms
64 bytes from keqsi1 (10.53.1.230): icmp_seq=2 ttl=64 time=0.128 ms
64 bytes from keqsi1 (10.53.1.230): icmp_seq=3 ttl=64 time=0.125 ms
64 bytes from keqsi1 (10.53.1.230): icmp_seq=4 ttl=64 time=0.126 ms
64 bytes from keqsi1 (10.53.1.230): icmp_seq=5 ttl=64 time=0.125 ms
64 bytes from keqsi1 (10.53.1.230): icmp_seq=6 ttl=64 time=0.126 ms

--- keqsi1 ping statistics ---
7 packets transmitted, 7 received, 0% packet loss, time 5999ms
rtt min/avg/max/mdev = 0.125/0.126/0.131/0.012 ms, pipe 2
[oracle@keqsi2 admin]$ ping keqsi2
PING keqsi2 (10.53.1.231) 56(84) bytes of data.
64 bytes from keqsi2 (10.53.1.231): icmp_seq=0 ttl=64 time=0.024 ms
64 bytes from keqsi2 (10.53.1.231): icmp_seq=1 ttl=64 time=0.010 ms
64 bytes from keqsi2 (10.53.1.231): icmp_seq=2 ttl=64 time=0.015 ms

--- keqsi2 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 1999ms
rtt min/avg/max/mdev = 0.010/0.016/0.024/0.006 ms, pipe 2
[oracle@keqsi2 admin]$ ping keqsi2-vip
PING keqsi2-vip (10.53.1.238) 56(84) bytes of data.
64 bytes from keqsi2-vip (10.53.1.238): icmp_seq=0 ttl=64 time=0.018 ms
64 bytes from keqsi2-vip (10.53.1.238): icmp_seq=1 ttl=64 time=0.011 ms
64 bytes from keqsi2-vip (10.53.1.238): icmp_seq=2 ttl=64 time=0.010 ms
64 bytes from keqsi2-vip (10.53.1.238): icmp_seq=3 ttl=64 time=0.009 ms

--- keqsi2-vip ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 2999ms
rtt min/avg/max/mdev = 0.009/0.012/0.018/0.003 ms, pipe 2
[oracle@keqsi2 admin]$ ping keqsi1-vip
PING keqsi1-vip (10.53.1.237) 56(84) bytes of data.
64 bytes from keqsi1-vip (10.53.1.237): icmp_seq=0 ttl=64 time=0.135 ms
64 bytes from keqsi1-vip (10.53.1.237): icmp_seq=1 ttl=64 time=0.129 ms
64 bytes from keqsi1-vip (10.53.1.237): icmp_seq=2 ttl=64 time=0.121 ms

--- keqsi1-vip ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2000ms
rtt min/avg/max/mdev = 0.121/0.128/0.135/0.010 ms, pipe 2

经上面的命令检查网络没有问题

下面手功启动listener还是报TNS-12545

[oracle@keqsi2 admin]$ srvctl start listener -n keqsi2
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 28-AUG-2013 08:26:14
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Copyright (c) 1991, 2007, Oracle.  All rights reserved.
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Starting /u01/app/oracle/10gR2/db/bin/tnslsnr: please wait...
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:TNSLSNR for Linux: Version 10.2.0.4.0 - Production
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:System parameter file is /u01/app/oracle/10gR2/db/network/admin/listener.ora
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Log messages written to /u01/app/oracle/10gR2/db/network/log/listener_keqsi2.log
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.53.1.237)(PORT=1521)(IP=FIRST)))
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:TNS-12545: Connect failed because target host or object does not exist
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr: TNS-12560: TNS:protocol adapter error
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:  TNS-00515: Connect failed because target host or object does not exist
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:   Linux Error: 99: Cannot assign requested address
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Listener failed to start. See the error message(s) above...
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 28-AUG-2013 08:26:14
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Copyright (c) 1991, 2007, Oracle.  All rights reserved.
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.53.1.237)(PORT=1521)(IP=FIRST)))
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:TNS-12541: TNS:no listener
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr: TNS-12560: TNS:protocol adapter error
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:  TNS-00511: No listener
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:   Linux Error: 111: Connection refused
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.53.1.238)(PORT=1521)(IP=FIRST)))
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:TNS-12541: TNS:no listener
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr: TNS-12560: TNS:protocol adapter error
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:  TNS-00511: No listener
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:   Linux Error: 111: Connection refused
CRS-0215: Could not start resource 'ora.keqsi2.LISTENER_KEQSI2.lsnr'.

下面让各个节点的监听程序只监听自己节点的vip

[oracle@keqsi2 admin]$ vi listener.ora
   
# listener.ora.keqsi2 Network Configuration File: /u01/app/oracle/10gR2/db/network/admin/listener.ora.keqsi2
# Generated by Oracle configuration tools.

LISTENER_KEQSI2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.53.1.238)(PORT = 1521)(IP = FIRST))
    )
  )

SID_LIST_LISTENER_KEQSI2 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/10gR2/db)
      (PROGRAM = extproc)
    )
  )

~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
"listener.ora" 19L, 474C written

[oracle@keqsi1 admin]$ vi listener.ora

# listener.ora.keqsi1 Network Configuration File: /u01/app/oracle/10gR2/db/network/admin/listener.ora.keqsi1
# Generated by Oracle configuration tools.

LISTENER_KEQSI1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST =10.53.1.237)(PORT = 1521)(IP = FIRST))
    )
  )

SID_LIST_LISTENER_KEQSI1 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/10gR2/db)
      (PROGRAM = extproc)
    )
  )

~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
"listener.ora" 19L, 473C written

启动监听程序正常

[oracle@keqsi2 admin]$ lsnrctl start LISTENER_KEQSI2

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 28-AUG-2013 08:42:17

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Starting /u01/app/oracle/10gR2/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /u01/app/oracle/10gR2/db/network/admin/listener.ora
Log messages written to /u01/app/oracle/10gR2/db/network/log/listener_keqsi2.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.53.1.238)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.53.1.238)(PORT=1521)(IP=FIRST)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_KEQSI2
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                28-AUG-2013 08:42:17
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/10gR2/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/10gR2/db/network/log/listener_keqsi2.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.53.1.238)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


[oracle@keqsi1 admin]$ lsnrctl start LISTENER_KEQSI1

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 28-AUG-2013 08:37:08

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Starting /u01/app/oracle/10gR2/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /u01/app/oracle/10gR2/db/network/admin/listener.ora
Log messages written to /u01/app/oracle/10gR2/db/network/log/listener_keqsi1.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.53.1.237)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.53.1.237)(PORT=1521)(IP=FIRST)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_KEQSI1
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                28-AUG-2013 08:37:08
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/10gR2/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/10gR2/db/network/log/listener_keqsi1.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.53.1.237)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


[oracle@keqsi1 admin]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.keqjm.db   application    ONLINE    ONLINE    keqsi1
ora....m1.inst application    ONLINE    ONLINE    keqsi1
ora....m2.inst application    ONLINE    ONLINE    keqsi2
ora....orcl.cs application    ONLINE    ONLINE    keqsi2
ora....jm1.srv application    ONLINE    ONLINE    keqsi1
ora....jm2.srv application    ONLINE    ONLINE    keqsi2
ora....SM1.asm application    ONLINE    ONLINE    keqsi1
ora....I1.lsnr application    ONLINE    ONLINE    keqsi1
ora.keqsi1.gsd application    ONLINE    ONLINE    keqsi1
ora.keqsi1.ons application    ONLINE    ONLINE    keqsi1
ora.keqsi1.vip application    ONLINE    ONLINE    keqsi1
ora....SM2.asm application    ONLINE    ONLINE    keqsi2
ora....I2.lsnr application    ONLINE    ONLINE    keqsi2
ora.keqsi2.gsd application    ONLINE    ONLINE    keqsi2
ora.keqsi2.ons application    ONLINE    ONLINE    keqsi2
ora.keqsi2.vip application    ONLINE    ONLINE    keqsi2

然后再重新启动rac数据库能正常启动

[oracle@keqsi1 admin]$ crs_stop -all
Attempting to stop `ora.keqjm.orcl.keqjm1.srv` on member `keqsi1`
Attempting to stop `ora.keqsi1.gsd` on member `keqsi1`
Stop of `ora.keqjm.orcl.keqjm1.srv` on member `keqsi1` succeeded.
Attempting to stop `ora.keqjm.orcl.keqjm2.srv` on member `keqsi2`
Attempting to stop `ora.keqjm.db` on member `keqsi1`
Attempting to stop `ora.keqsi1.ons` on member `keqsi1`
Attempting to stop `ora.keqjm.orcl.cs` on member `keqsi2`
Attempting to stop `ora.keqsi2.gsd` on member `keqsi2`
Attempting to stop `ora.keqsi2.ons` on member `keqsi2`
Stop of `ora.keqjm.orcl.keqjm2.srv` on member `keqsi2` succeeded.
Stop of `ora.keqsi1.gsd` on member `keqsi1` succeeded.
Stop of `ora.keqsi1.ons` on member `keqsi1` succeeded.
Stop of `ora.keqsi2.gsd` on member `keqsi2` succeeded.
Stop of `ora.keqsi2.ons` on member `keqsi2` succeeded.
Stop of `ora.keqjm.orcl.cs` on member `keqsi2` succeeded.
Stop of `ora.keqjm.db` on member `keqsi1` succeeded.
`ora.keqjm.keqjm1.inst` is already OFFLINE.
`ora.keqjm.keqjm2.inst` is already OFFLINE.
Attempting to stop `ora.keqsi1.ASM1.asm` on member `keqsi1`
Attempting to stop `ora.keqsi2.ASM2.asm` on member `keqsi2`
Attempting to stop `ora.keqsi1.LISTENER_KEQSI1.lsnr` on member `keqsi1`
Attempting to stop `ora.keqsi2.LISTENER_KEQSI2.lsnr` on member `keqsi2`
Stop of `ora.keqsi2.LISTENER_KEQSI2.lsnr` on member `keqsi2` succeeded.
Attempting to stop `ora.keqsi2.vip` on member `keqsi2`
Stop of `ora.keqsi1.LISTENER_KEQSI1.lsnr` on member `keqsi1` succeeded.
Attempting to stop `ora.keqsi1.vip` on member `keqsi1`
Stop of `ora.keqsi2.vip` on member `keqsi2` succeeded.
Stop of `ora.keqsi1.ASM1.asm` on member `keqsi1` succeeded.
Stop of `ora.keqsi1.vip` on member `keqsi1` succeeded.
Stop of `ora.keqsi2.ASM2.asm` on member `keqsi2` succeeded.
CRS-0216: Could not stop resource 'ora.keqjm.keqjm1.inst'.

CRS-0216: Could not stop resource 'ora.keqjm.keqjm2.inst'.

[oracle@keqsi1 admin]$ crs_start -all
Attempting to start `ora.keqsi1.ASM1.asm` on member `keqsi1`
Attempting to start `ora.keqsi1.vip` on member `keqsi1`
Attempting to start `ora.keqsi2.ASM2.asm` on member `keqsi2`
Attempting to start `ora.keqsi2.vip` on member `keqsi2`
Start of `ora.keqsi1.vip` on member `keqsi1` succeeded.
Attempting to start `ora.keqsi1.LISTENER_KEQSI1.lsnr` on member `keqsi1`
Start of `ora.keqsi2.vip` on member `keqsi2` succeeded.
Attempting to start `ora.keqsi2.LISTENER_KEQSI2.lsnr` on member `keqsi2`
Start of `ora.keqsi1.LISTENER_KEQSI1.lsnr` on member `keqsi1` succeeded.
Start of `ora.keqsi2.LISTENER_KEQSI2.lsnr` on member `keqsi2` succeeded.
Start of `ora.keqsi1.ASM1.asm` on member `keqsi1` succeeded.
Attempting to start `ora.keqjm.keqjm1.inst` on member `keqsi1`
Start of `ora.keqsi2.ASM2.asm` on member `keqsi2` succeeded.
Attempting to start `ora.keqjm.keqjm2.inst` on member `keqsi2`
Start of `ora.keqjm.keqjm1.inst` on member `keqsi1` succeeded.
Start of `ora.keqjm.keqjm2.inst` on member `keqsi2` succeeded.
CRS-1002: Resource 'ora.keqsi1.ons' is already running on member 'keqsi1'

CRS-1002: Resource 'ora.keqsi2.ons' is already running on member 'keqsi2'

CRS-1002: Resource 'ora.keqjm.db' is already running on member 'keqsi2'

Attempting to start `ora.keqjm.orcl.cs` on member `keqsi2`
Attempting to start `ora.keqsi1.gsd` on member `keqsi1`
Attempting to start `ora.keqjm.orcl.keqjm1.srv` on member `keqsi1`
Attempting to start `ora.keqjm.orcl.keqjm2.srv` on member `keqsi2`
Attempting to start `ora.keqsi2.gsd` on member `keqsi2`
Start of `ora.keqjm.orcl.cs` on member `keqsi2` succeeded.
Start of `ora.keqjm.orcl.keqjm1.srv` on member `keqsi1` succeeded.
Start of `ora.keqsi1.gsd` on member `keqsi1` succeeded.
Start of `ora.keqjm.orcl.keqjm2.srv` on member `keqsi2` succeeded.
Start of `ora.keqsi2.gsd` on member `keqsi2` succeeded.
CRS-0223: Resource 'ora.keqjm.db' has placement error.

CRS-0223: Resource 'ora.keqsi1.ons' has placement error.

CRS-0223: Resource 'ora.keqsi2.ons' has placement error.

[oracle@keqsi1 admin]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.keqjm.db   application    ONLINE    ONLINE    keqsi2
ora....m1.inst application    ONLINE    ONLINE    keqsi1
ora....m2.inst application    ONLINE    ONLINE    keqsi2
ora....orcl.cs application    ONLINE    ONLINE    keqsi2
ora....jm1.srv application    ONLINE    ONLINE    keqsi1
ora....jm2.srv application    ONLINE    ONLINE    keqsi2
ora....SM1.asm application    ONLINE    ONLINE    keqsi1
ora....I1.lsnr application    ONLINE    ONLINE    keqsi1
ora.keqsi1.gsd application    ONLINE    ONLINE    keqsi1
ora.keqsi1.ons application    ONLINE    ONLINE    keqsi1
ora.keqsi1.vip application    ONLINE    ONLINE    keqsi1
ora....SM2.asm application    ONLINE    ONLINE    keqsi2
ora....I2.lsnr application    ONLINE    ONLINE    keqsi2
ora.keqsi2.gsd application    ONLINE    ONLINE    keqsi2
ora.keqsi2.ons application    ONLINE    ONLINE    keqsi2
ora.keqsi2.vip application    ONLINE    ONLINE    keqsi2
[oracle@keqsi1 admin]$

修改数据库中的初始化参数LOCAL_LISTENER和REMOTE_LISTENER

oracle@keqsi1 admin ]$ sqlplus  / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 28 00:26:39 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options


SQL> ALTER SYSTEM
2 SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = 10.53.1.237)(PORT = 1521))'
3 SID = 'keqsi1';

系统已更改。


SQL> ALTER SYSTEM
2 SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = 10.53.1.238)(PORT = 1521))'
3 SID = 'keqsi2';

系统已更改。
SQL> ALTER SYSTEM
2 SET REMOTE_LISTENER = 'LISTENERS_KEQJM'
3 SID = '*';
系统已更改。

其中’LISTENERS_KEQJM’对应于tnsnames.ora中的LISTENERS_KEQJM
连接串,该参数用于rac的负载均衡

[oracle@keqsi1 admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/10gR2/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

KEQJM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi2-vip)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = keqjm)
    )
  )

KEQJM2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = keqjm)
      (INSTANCE_NAME = keqjm2)
    )
  )

KEQJM1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = keqjm)
      (INSTANCE_NAME = keqjm1)
    )
  )

LISTENERS_KEQJM =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi2-vip)(PORT = 1521))
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi2-vip)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
)

下面是测看各个节点只监听自己的vip地址时通过remote_listener能不能实现
rac的负载均衡
在本机测试

[oracle@test admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 28 00:31:45 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn test/test@keqsi
Connected.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
keqjm1

SQL>exit

[oracle@test admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 28 00:31:45 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn test/test@keqsi
Connected.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
keqjm2

经测试rac是可以负载均衡的

自定义show_space过程来显示数据段的利用信息

SHOW_SPACE过程
SHOW_SPACE例程用于打印数据库段空间利用率信息:

sys@DEVELOP> create or replace procedure show_space
  2  ( p_segname in varchar2,
  3    p_owner in varchar2 default user,
  4    p_type in varchar2 default 'TABLE',
  5    p_partition in varchar2 default NULL )
  6  -- this procedure uses authid current user so it can query DBA_*
  7  -- views using privileges from a ROLE, and so it can be installed
  8  -- once per database, instead of once per user who wanted to use it
  9    authid current_user
 10  as
 11    l_free_blks number;
 12    l_total_blocks number;
 13    l_total_bytes number;
 14    l_unused_blocks number;
 15    l_unused_bytes number;
 16    l_LastUsedExtFileId number;
 17    l_LastUsedExtBlockId number;
 18    l_LAST_USED_BLOCK number;
 19    l_segment_space_mgmt varchar2(255);
 20    l_unformatted_blocks number;
 21    l_unformatted_bytes number;
 22    l_fs1_blocks number; l_fs1_bytes number;
 23    l_fs2_blocks number; l_fs2_bytes number;
 24    l_fs3_blocks number; l_fs3_bytes number;
 25    l_fs4_blocks number; l_fs4_bytes number;
 26    l_full_blocks number; l_full_bytes number;
 27  -- inline procedure to print out numbers nicely formatted
 28  -- with a simple label
 29  procedure p( p_label in varchar2, p_num in number )
 30  is
 31  begin
 32    dbms_output.put_line( rpad(p_label,40,'.') ||
 33    to_char(p_num,'999,999,999,999') );
 34  end;
 35  begin
 36  -- this query is executed dynamically in order to allow this procedure
 37  -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
 38  -- via a role as is customary.
 39  -- NOTE: at runtime, the invoker MUST have access to these two
-- views!
 40   41  -- this query determines if the object is an ASSM object or not
 42    begin
 43      execute immediate
 44      'select ts.segment_space_management
 45      from dba_segments seg, dba_tablespaces ts
 46      where seg.segment_name = :p_segname
    and (:p_partition is null or
 47   48      seg.partition_name = :p_partition)
 49      and seg.owner = :p_owner
 50      and seg.tablespace_name = ts.tablespace_name'
 51      into l_segment_space_mgmt
 52      using p_segname, p_partition, p_partition, p_owner;
 53    exception
 54    when too_many_rows then
 55      dbms_output.put_line
 56        ( 'This must be a partitioned table, use p_partition => ');
 57      return;
 58    end;
 59  -- if the object is in an ASSM tablespace, we must use this API
 60  -- call to get space information, otherwise we use the FREE_BLOCKS
 61  -- API for the user-managed segments
 62    if l_segment_space_mgmt = 'AUTO'   then
 63       dbms_space.space_usage
 64       ( p_owner, p_segname, p_type, l_unformatted_blocks,
 65       l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
 66       l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
 67       l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);
 68       p( 'Unformatted Blocks ', l_unformatted_blocks );
 69       p( 'FS1 Blocks (0-25) ', l_fs1_blocks );
 70       p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
 71       p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
 72       p( 'FS4 Blocks (75-100)', l_fs4_blocks );
 73       p( 'Full Blocks ', l_full_blocks );
 74    else
 75       dbms_space.free_blocks(
 76       segment_owner => p_owner,
 77       segment_name => p_segname,
 78       segment_type => p_type,
 79       freelist_group_id => 0,
 80       free_blks => l_free_blks);
 81       p( 'Free Blocks', l_free_blks );
 82    end if;
 83  -- and then the unused space API call to get the rest of the
 84  -- information
 85    dbms_space.unused_space
 86     ( segment_owner => p_owner,
 87       segment_name => p_segname,
 88       segment_type => p_type,
 89       partition_name => p_partition,
 90       total_blocks => l_total_blocks,
 91       total_bytes => l_total_bytes,
 92       unused_blocks => l_unused_blocks,
 93       unused_bytes => l_unused_bytes,
 94       LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
 95       LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
 96       LAST_USED_BLOCK => l_LAST_USED_BLOCK );
 97       p( 'Total Blocks', l_total_blocks );
 98       p( 'Total Bytes', l_total_bytes );
 99       p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
100       p( 'Unused Blocks', l_unused_blocks );
101       p( 'Unused Bytes', l_unused_bytes );
102       p( 'Last Used Ext FileId', l_LastUsedExtFileId );
103       p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
104       p( 'Last Used Block', l_LAST_USED_BLOCK );
105  end;
106  /

Procedure created.

show_space过程包含以下参数:

sys@DEVELOP> desc show_space
PROCEDURE show_space
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_SEGNAME                      VARCHAR2                IN
 P_OWNER                        VARCHAR2                IN     DEFAULT
 P_TYPE                         VARCHAR2                IN     DEFAULT
 P_PARTITION                    VARCHAR2                IN     DEFAULT

参数如下:
• P_SEGNAME:段名(例如,表或索引名)。
• P_OWNER:默认为当前用户,不过也可以使用这个例程查看另外某个用户。
• P_TYPE:默认为TABLE,这个参数表示查看哪种类型的对象(段)。例如,SELECT DISTINCT SEGMENT_TYPE FROM DBA_SEGMENTS
会列出合法的段类型。
• P_PARTITION:显示分区对象的空间时所用的分区名。SHOW_SPACE一次只显示一个分区的空间利用率。

这个过程的输出如下,这里段位于一个自动段空间管理(Automatic Segment Space Management, ASSM)表空间中:

sys@DEVELOP> exec show_space('AC01','HNSIC');
Unformatted Blocks .....................               0
FS1 Blocks (0-25) ......................               1
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................              12
Full Blocks ............................          16,305
Total Blocks............................         146,304
Total Bytes.............................   1,198,522,368
Total MBytes............................           1,143
Unused Blocks...........................         129,920
Unused Bytes............................   1,064,304,640
Last Used Ext FileId....................              10
Last Used Ext BlockId...................         348,800
Last Used Block.........................           8,192

PL/SQL procedure successfully completed.

报告的各项结果说明如下:
• Unformatted Blocks:为表分配的位于高水位线(high-water mark, HWM)之下但未用的块数。把未格式化和未用的块加在一起,就是已为表分配但从未用于保存ASSM对象数据的总块数。
• FS1 Blocks-FS4 Blocks:包含数据的格式化块。项名后的数字区间表示各块的“空闲度”。例如,(0-25)是指空闲度为
0~25%的块数。
• Full Blocks:已满的块数,不能再对这些执行插入。
• Total Blocks、Total bytes、Total Mbytes:为所查看的段分配的总空间量,单位分别是数据库块、字节和兆字节。
• Unused Blocks、Unused Bytes:表示未用空间所占的比例(未用空间量)。这些块已经分配给所查看的段,但目前在段的HWM之上。
• Last Used Ext FileId:最后使用的文件的文件ID,该文件包含最后一个含数据的区段(extent)。
• Last Used Ext BlockId:最后一个区段开始处的块ID;这是最后使用的文件中的块ID。
• Last Used Block:最后一个区段中最后一个块的偏移量。

如果对象在用户空间管理的表空间中,使用SHOW_SPACE查看时,输出如下:

sys@DEVELOP> exec show_space('T1');
Free Blocks.............................               3
Total Blocks............................          17,408
Total Bytes.............................     142,606,336
Total MBytes............................             136
Unused Blocks...........................             869
Unused Bytes............................       7,118,848
Last Used Ext FileId....................               1
Last Used Ext BlockId...................       1,696,896
Last Used Block.........................             155

PL/SQL procedure successfully completed.

这里惟一的区别是报告中最前面的Free Blocks项。这是段的第一个freelist(自由列表)组中的块数。
脚本只测试了第一个freelist组。如果想测试多个freelist组,还需要修改这个脚本。

自定义统计结果脚本mystat

mystat.sql和相应的mystat2.sql用于展示完成某操作之前和之后的某些Oracle“统计结果”的变化情况。mystat.sql只是获得统计结果的开始值:
mystat.sql脚本内容如下:

set echo off
set verify off
column value new_val V
define S="&1"
set autotrace off
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on

mystat2.sql用于报告统计结果的变化情况(差值):

set echo off
set verify off
select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on

例如,要查看某个UPDATE生成的redo数,可以使用以下命令:

sys@DEVELOP> @/oracle/product/11.2/sqlplus/mystat.sql "redo size"

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         756293676
redo size for lost write detection                                        0
redo size for direct writes                                               0

sys@DEVELOP> update t1 set aac009='1';

795680 rows updated.

sys@DEVELOP> @/oracle/product/11.2/sqlplus/mystat2.sql

NAME                                                                      V DIFF
---------------------------------------------------------------- ---------- ----------------
redo size                                                        1139306496    1,139,306,496
redo size for lost write detection                                        0                0
redo size for direct writes                                               0                0

由此可见,795,680行记录的UPDATE会生成1,139,306,496字节的redo.

自定义性能统计存储过程包runstats

runstats是一个工具,能对做同一件事的两个不同方法进行比较,得出孰优孰劣的结果。只需提供两个不同的方法, 余下的事情都由runstats负责。runstats只是测量3个要素:
? 耗用时间(elapsed time):知道耗用时间很有用,不过这不是最重要的信息。
? 系统统计结果:会并排显示每个方法做某件事(如执行一个解析调用)的次数,并展示二者之差。
? 闩锁(latching):这是这个报告的关键输出。
闩锁(latch)是一种轻量级的锁。锁(lock)是一种串行化设备,而串行化设备不支持并发。如果应用不支 持并发,可扩缩性就比较差,只能支持较少的用户,而且需要更多的资源。构建应用时,我们往往希望应用能很好地扩缩,也就是说,为1位用户服务与为1,000或10,000位用户服务应该是一样的。应用中使用的闩锁越少,性能就越好。如果一种方法从耗用时间来看运行时间较长,但是只使用了另一种方法10%的闩锁,我可能会选择前者。因为与使用更多闩锁的方法相比,使用较少闩锁的方法能更好地扩缩。
runstats最后独立使用,也就是说,最好在一个单用户数据库上运行。它会测量各个方法的统计结果和闩锁(锁定)活动。 runstats在运行过程中,不希望其他任务对系统的负载或闩锁产生影响。只需一个小的测试数据库就能很好地完成这些测试。要使用runstats,需要能访问几个V$视图,并创建一个表来存储统计结果,还要创建runstats包。为此,需要访问3个V$表:V$STATNAME、V$MYSTAT和V$LATCH。以下是我使用的视图:

create or replace view stats
as select 'STAT...' || a.name name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
union all
select 'LATCH.' || name, gets
from v$latch;

如果你能得到V$STATNAME、V$MYSTAT、V$LATCH和V$TIMER的直接授权,就能直接对这些表执行SELECT操作(相应地可以自行创建 视图);否则,可以由其他人对这些表执行SELECT操作为你创建视图,并授予你在这个视图上执行SELECT的权限。
一旦建立视图,接下来只需要一个小表来收集统计结果:

sys@DEVELOP> create global temporary table run_stats
  2  ( runid varchar2(15),
  3  name varchar2(80),
  4  value int )
  5  on commit preserve rows;

Table created.

最后,需要创建runstats包。其中包含3个简单的API调用:
runstats测试开始时调用RS_STAT(runstats开始)。
正如你想象的,RS_MIDDLE会在测试之间调用。
完成时调用RS_STOP,打印报告。
创建runstats包的规范如下:

sys@DEVELOP> create or replace package runstats_pkg
  2  as
  3  procedure rs_start;
  4  procedure rs_middle;
  5  procedure rs_stop(p_difference_threshold in number default 0);
  6  end;
  7  /

Package created.

参数P_DIFFERENCE_THRESHOLD用于控制最后打印的数据量。runstats会收集并得到每次运行的统计结果和闩信息,然后打印一个 报告,说明每次测试(每个方法)使用了多少资源,以及不同测试(不同方法)的结果之差。可以使用这个输入参数来控制只查 看差值大于这个数的统计结果和闩信息。由于这个参数默认为0,所以默认情况下可以看到所有输出。
下面我们逐一分析包体中的过程。包前面是一些全局变量,这些全局变量用于记录每次运行的耗用时间:
RS_START过程。这个过程只是清空保存统计结果的表,并填入“上一次”(before)得到的统计结果和闩信息。然后获得 当前定时器值,这是一种时钟,可用于计算耗用时间(单位百分之一秒):

接下来是RS_MIDDLE过程。这个过程只是把第一次测试运行的耗用时间记录在G_RUN1中。然后插入当前的一组统计结果和闩信息。 如果把这些值与先前在RS_START中保存的值相减,就会发现第一个方法使用了多少闩,以及使用了多少游标(一种统计结果), 等等。
最后,记录下一次运行的开始时间:

完整的包体创建语句如下:

sys@DEVELOP> create or replace package body runstats_pkg
  2   as
  3
  4   g_start number;
  5   g_run1 number;
  6   g_run2 number;
  7
  8   procedure rs_start
  9   is
 10   begin
 11     delete from run_stats;
 12
 13     insert into run_stats
 14     select 'before', stats.* from stats;
 15
 16     g_start := dbms_utility.get_time;
 17   end;
 18
 19   procedure rs_middle
 20   is
 21   begin
 22     g_run1 := (dbms_utility.get_time-g_start);
 23
 24     insert into run_stats
 25     select 'after 1', stats.* from stats;
 26     g_start := dbms_utility.get_time;
 27
 28   end;
 29
 30   procedure rs_stop(p_difference_threshold in number default 0)
 31   is
 32   begin
 33     g_run2 := (dbms_utility.get_time-g_start);
 34
 35     dbms_output.put_line
 36       ( 'Run1 ran in ' || g_run1 || ' hsecs' );
 37     dbms_output.put_line
 38       ( 'Run2 ran in ' || g_run2 || ' hsecs' );
 39     dbms_output.put_line
 40       ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
 41       '% of the time' );
 42     dbms_output.put_line( chr(9) );
 43
 44     insert into run_stats
 45     select 'after 2', stats.* from stats;
 46
 47     dbms_output.put_line
 48       ( rpad( 'Name', 30 ) || lpad( 'Run1', 10 ) ||
 49       lpad( 'Run2', 10 ) || lpad( 'Diff', 10 ) );
 50
 51     for x in
 52       ( select rpad( a.name, 30 ) ||
 53         to_char( b.value-a.value, '9,999,999' ) ||
 54         to_char( c.value-b.value, '9,999,999' ) ||
 55         to_char( ( (c.value-b.value)-(b.value-a.value)), '9,999,999' ) data
 56         from run_stats a, run_stats b, run_stats c
 57         where a.name = b.name
 58         and b.name = c.name
 59         and a.runid = 'before'
 60         and b.runid = 'after 1'
 61         and c.runid = 'after 2'
 62         and (c.value-a.value) > 0
 63         and abs( (c.value-b.value) - (b.value-a.value) )
 64         > p_difference_threshold
 65         order by abs( (c.value-b.value)-(b.value-a.value))
 66       ) loop
 67       dbms_output.put_line( x.data );
 68     end loop;
 69
 70     dbms_output.put_line( chr(9) );
 71     dbms_output.put_line
 72       ( 'Run1 latches total versus runs -- difference and pct' );
 73     dbms_output.put_line
 74       ( lpad( 'Run1', 10 ) || lpad( 'Run2', 10 ) ||
 75         lpad( 'Diff', 10 ) || lpad( 'Pct', 8 ) );
 76
 77     for x in
 78       ( select to_char( run1, '9,999,999' ) ||
 79       to_char( run2, '9,999,999' ) ||
 80       to_char( diff, '9,999,999' ) ||
 81       to_char( round( run1/run2*100,2 ), '999.99' ) || '%' data
 82       from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
 83       sum( (c.value-b.value)-(b.value-a.value)) diff
 84       from run_stats a, run_stats b, run_stats c
 85       where a.name = b.name
 86       and b.name = c.name
 87       and a.runid = 'before'
 88       and b.runid = 'after 1'
 89       and c.runid = 'after 2'
 90       and a.name like 'LATCH%'
 91          )
 92       ) loop
 93       dbms_output.put_line( x.data );
 94     end loop;
 95   end;
 96
 97  end;
 98   /

Package body created.

下面可以使用runstats了。我们将通过例子来说明如何使用runstats对批量插入(INSERT)和逐行处理进行比较,
看看哪种方法效率更高。首先建立两个表,要在其中插入795,680行记录:

sys@DEVELOP> create table t1 as select * from hnsic.ac01 where 1=0;

Table created.

sys@DEVELOP> create table t2 as select * from hnsic.ac01 where 1=0;

Table created.

接下来使用第一种方法插入记录,也就是使用单独一条SQL语句完成批量插入。首先调用RUNSTATS_PKG.RS_START:

sys@DEVELOP> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

sys@DEVELOP> insert into t1 select * from hnsic.ac01;

795680 rows created.

sys@DEVELOP> commit;

Commit complete.

下面准备执行第二种方法,即逐行地插入数据:

sys@DEVELOP> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

sys@DEVELOP> begin
  2   for x in (select * from hnsic.ac01)
  3   loop
  4     insert into t2 values x;
  5   end loop;
  6   commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

最后生成报告:

sys@DEVELOP> exec runstats_pkg.rs_stop;
Run1 ran in 4835 hsecs
Run2 ran in 14497 hsecs
run 1 ran in 33.35% of the time

Name                                                                                            Run1              

  Run2
Diff
LATCH.space background state object latch                                                1         0        -1
LATCH.file cache latch                                                                  93        94         1
LATCH.ktm global data                                                                    5         4        -1
LATCH.KTF sga latch                                                                      1         0        -1
LATCH.dml lock allocation                                                                1         2         1
LATCH.tablespace key chain                                                               1         2         1
LATCH.deferred cleanup latch                                                             1         2         1
LATCH.kcbtsemkid latch                                                                   2         1        -1
LATCH.threshold alerts latch                                                             1         2         1
LATCH.cp sga latch                                                                       1         2         1
LATCH.hash table modification latch                                                      1         2         1
STAT...parse count (hard)                                                                1         2         1
STAT...parse count (total)                                                               5         4        -1
STAT...sql area evicted                                                                  1         0        -1
LATCH.ASM network state latch                                                            1         2         1
STAT...write clones created in foreground                                                3         2        -1
STAT...shared hash latch upgrades - no wait                                             93        94         1
LATCH.ncodef allocation latch                                                            1         2         1
STAT...deferred (CURRENT) block cleanout applications                                    1         2         1
STAT...commit txn count during cleanout                                                  0         1         1
STAT...IMU Flushes                                                                       0         1         1
STAT...table fetch by rowid                                                             94        95         1
STAT...index scans kdiixs1                                                              94        95         1
STAT...Requests to/from client                                                           6         4        -2
STAT...session cursor cache count                                                        5         3        -2
STAT...redo log space wait time                                                          2         0        -2
STAT...redo log space requests                                                           4         2        -2
LATCH.ksv class latch                                                                    3         5         2
STAT...non-idle wait time                                                                2         0        -2
STAT...calls to get snapshot scn: kcmgss                                               184       186         2
STAT...SQL*Net roundtrips to/from client                                                 6         4        -2
LATCH.MinActiveScn Latch                                                                 0         2         2
STAT...db block gets from cache (fastpath)                                             103       105         2
LATCH.ksv allocation latch                                                               4         6         2
STAT...redo entries                                                                849,543   849,545         2
LATCH.message pool operations parent latch                                               4         1        -3
STAT...parse time cpu                                                                    1         4         3
STAT...user calls                                                                        9         6        -3
LATCH.kwqbsn:qsga                                                                        2         5         3
LATCH.Shared B-Tree                                                                      3         6         3
LATCH.interrupt manipulation                                                             1         5         4
LATCH.SQL memory manager latch                                                           1         5         4
LATCH.kokc descriptor allocation latch                                                   2         6         4
STAT...redo buffer allocation retries                                                    5         1        -4
LATCH.multiblock read objects                                                            6         2        -4
LATCH.object stats modification                                                          8        13         5
STAT...enqueue releases                                                              3,711     3,716         5
STAT...enqueue requests                                                              3,710     3,716         6
STAT...redo subscn max counts                                                       23,411    23,405        -6
LATCH.corrupted undo seg latch                                                          18        12        -6
STAT...heap block compress                                                               7         0        -7
STAT...free buffer requested                                                        23,406    23,414         8
LATCH.ksuosstats global area                                                             5        14         9
STAT...calls to kcmgas                                                              10,299    10,289       -10
STAT...redo ordering marks                                                          10,201    10,191       -10
STAT...calls to kcmgcs                                                              33,174    33,187        13
LATCH.loader state object freelist                                                      14        28        14
LATCH.qmn task queue latch                                                              15        30        15
LATCH.job_queue_processes parameter latch                                               11        31        20
LATCH.parallel query alloc buffer                                                        7        31        24
STAT...consistent changes                                                               31        56        25
STAT...db block gets from cache                                                    870,280   870,307        27
STAT...db block gets                                                               870,280   870,307        27
STAT...db block changes                                                          1,672,435 1,672,465        30
STAT...active txn count during cleanout                                             16,027    15,995       -32
STAT...cleanout - number of ktugct calls                                            16,027    15,995       -32
STAT...consistent gets - examination                                                16,032    16,000       -32
LATCH.session timer                                                                     16        48        32
LATCH.Change Notification Hash table latch                                              16        48        32
LATCH.KMG MMAN ready and startup request latch                                          16        49        33
STAT...workarea memory allocated                                                        40        -5       -45
STAT...messages sent                                                                   370       322       -48
LATCH.sort extent pool                                                                   2        61        59
STAT...undo change vector size                                                  ####################        60
LATCH.ASM db client latch                                                               36        99        63
LATCH.simulator lru latch                                                            6,903     6,977        74
LATCH.parameter list                                                                    36       125        89
LATCH.FOB s.o list latch                                                                 6        97        91
LATCH.active checkpoint queue latch                                                    143       237        94
LATCH.global tx hash mapping                                                            19       129       110
LATCH.transaction branch allocation                                                     32       162       130
LATCH.space background task latch                                                       89       270       181
LATCH.post/wait queue                                                                   68       251       183
LATCH.SGA IO buffer pool latch                                                         193       442       249
STAT...change write time                                                             1,393     1,648       255
LATCH.Real-time plan statistics latch                                                  383       763       380
LATCH.shared pool simulator                                                            106       508       402
STAT...bytes sent via SQL*Net to client                                              1,438       948      -490
LATCH.JS queue state obj latch                                                         360     1,044       684
STAT...bytes received via SQL*Net from client                                        2,313     1,623      -690
LATCH.lgwr LWN SCN                                                                     401     1,402     1,001
LATCH.Consistent RBA                                                                   399     1,401     1,002
LATCH.session switching                                                                  7     1,009     1,002
LATCH.resmgr:actses change group                                                         9     1,018     1,009
LATCH.resmgr group change latch                                                          7     1,016     1,009
LATCH.compile environment latch                                                          8     1,017     1,009
LATCH.global KZLD latch for mem in SGA                                                   7     1,016     1,009
LATCH.mostly latch-free SCN                                                            408     1,427     1,019
LATCH.PL/SQL warning settings                                                           53     1,086     1,033
LATCH.In memory undo latch                                                             298     1,793     1,495
LATCH.channel handle pool latch                                                         15     2,024     2,009
LATCH.dummy allocation                                                                  14     2,024     2,010
LATCH.ksz_so allocation latch                                                           13     2,023     2,010
LATCH.OS process: request allocation                                                    13     2,023     2,010
LATCH.resmgr:active threads                                                             13     2,023     2,010
LATCH.resmgr:free threads list                                                          13     2,023     2,010
LATCH.process allocation                                                                14     2,024     2,010
LATCH.process group creation                                                            13     2,023     2,010
LATCH.list of block allocation                                                       1,665     3,678     2,013
LATCH.Event Group Locks                                                                 17     2,035     2,018
LATCH.session state list latch                                                          13     2,042     2,029
LATCH.transaction allocation                                                            24     2,079     2,055
STAT...IMU undo allocation size                                                      3,256     1,080    -2,176
LATCH.DML lock allocation                                                               73     2,251     2,178
LATCH.parameter table management                                                        69     2,249     2,180
LATCH.OS process allocation                                                            113     2,315     2,202
LATCH.redo allocation                                                                2,664     5,249     2,585
LATCH.sequence cache                                                                    21     3,048     3,027
LATCH.undo global data                                                              23,400    26,617     3,217
LATCH.active service list                                                              146     3,396     3,250
LATCH.cache buffer handles                                                              42     3,356     3,314
LATCH.redo writing                                                                   1,389     4,733     3,344
LATCH.channel operations parent latch                                                  273     3,729     3,456
STAT...CPU used when call started                                                    2,405     6,330     3,925
STAT...CPU used by this session                                                      2,403     6,330     3,927
STAT...DB time                                                                       2,416     6,351     3,935
LATCH.OS process                                                                        29     4,041     4,012
LATCH.call allocation                                                                   26     4,252     4,226
LATCH.session allocation                                                               200     4,567     4,367
STAT...free buffer inspected                                                         9,080    13,902     4,822
LATCH.messages                                                                       1,938     7,030     5,092
STAT...hot buffers moved to head of LRU                                              3,719     9,033     5,314
STAT...recursive cpu usage                                                               6     5,494     5,488
LATCH.client/application info                                                           52     7,115     7,063
STAT...consistent gets from cache                                                   49,254    57,049     7,795
STAT...consistent gets                                                              49,254    57,049     7,795
STAT...table scan blocks gotten                                                     16,318    24,129     7,811
STAT...no work - consistent read gets                                               16,507    24,320     7,813
STAT...buffer is not pinned count                                                   16,695    24,510     7,815
STAT...session logical reads                                                       919,534   927,356     7,822
STAT...consistent gets from cache (fastpath)                                        33,128    40,954     7,826
LATCH.enqueue hash chains                                                            8,891    17,620     8,729
LATCH.object queue header heap                                                      12,561    21,765     9,204
STAT...session cursor cache hits                                                        97     9,347     9,250
STAT...IMU Redo allocation size                                                        476    11,228    10,752
STAT...redo size                                                                ####################    17,552
LATCH.session idle bit                                                               5,575    23,796    18,221
LATCH.cache buffers lru chain                                                       18,487    42,303    23,816
LATCH.checkpoint queue latch                                                        17,631    49,092    31,461
LATCH.object queue header operation                                                122,486   188,006    65,520
STAT...session uga memory max                                                      123,488    31,848   -91,640
LATCH.simulator hash latch                                                         237,377   443,767   206,390
LATCH.SQL memory manager workarea list latch                                         1,219   237,704   236,485
STAT...session pga memory max                                                            0   262,144   262,144
STAT...execute count                                                                   101   795,782   795,681
STAT...opened cursors cumulative                                                       102   795,789   795,687
STAT...recursive calls                                                                 848   804,498   803,650
LATCH.shared pool                                                                    7,037   884,303   877,266
LATCH.row cache objects                                                             25,994 1,205,408 1,179,414
LATCH.cache buffers chains                                                      #################### 7,690,411
STAT...logical read bytes from cache                                            ##############################

Run1 latches total versus runs -- difference and pct
Run1      Run2      Diff     Pct
##############################  53.64%

PL/SQL procedure successfully completed.

本文参考Expert.Oracle.Database.Architecture.9i.and.10g.Programming.Techniques.and.Solutions

linux系统中的调度周期任务:cron

linux系统中的调度周期任务:cron
主要概念
cron工具用来调度经常重复的任务
crontab命令是编辑crontab文件的一个前端程序
crontab文件使用5个字段来规定计时信息
cron作业中的标准输出会作为邮件寄给用户

执行周期任务
人们经常发现自己会定期执行一些任务.在系统管理中,这些任务包括从/tmp目录下删除旧的,不使用的文件,或者经常
检查记录登录信息的文件以确保其不会变得过大.其他用户可能会有自己的任务,如检查不再使用的大型文件,或者查看
网站上是否公布了新的信息.

cron工具允许用户配置要定期运行的命令,如每隔十分钟,每周四一次,或每月两次.用户用crontab命令配置自己的任务
计划(cron table),指定何种命令在何时运行.这些任务由传统的linux(和unix)守护进程,即crond守护进程管理.

cron服务
crond守护进程是代表系统或个人用户执行周期任务的守护进程.通常这个守护进程随着系统的启动而启动,因此大多数
用户都不会注意到.通过列出所有进程且搜索crond,你可以确定crond守护进程有没有在运行.

[root@sidatabase /]# ps aux | grep crond
root      3204  0.0  0.0 117204  1368 ?        Ss   Aug09   0:11 crond
root      4687  0.0  0.0 103244   872 pts/0    S+   14:52   0:00 grep crond

如果crond守护进程没有在运行,系统管理员需要以根用户身份来启动crond守护进程.

crontab语法
用户通过配置一个称为”cron table”(经常缩写成”crontab”)的文件指定要运行哪些作业以及何时运行.下面列出了一个
crontab文件的例子.

30 23 * * 6  su - sybx -c "/sydata/app/db/bin/rman target / msglog=/sybak/bak0.log  cmdfile=/sybak/sybx_rman_script/bak0"
30 23 * * 0,1,2,3,4,5  su - sybx -c "/sydata/app/db/bin/rman target / msglog=/sybak/bak1.log  cmdfile=/sybak/sybx_rman_script/bak1"
30 2 * * 0,1,2,3,4,5,6  su - sybx -c "/sydata/app/db/bin/rman target / msglog=/sybak/bakarch.log  cmdfile=/sybak/sybx_rman_script/bakarch"
30 3 * * 0,1,2,3,4,5,6  su - sybx -c "/sydata/app/db/bin/rman target / msglog=/sybak/delbackup.log  cmdfile=/sybak/sybx_rman_script/delbackup"

crontab文件是一个以行为运行单位的配置文件,每行执行三种功能中的一种:
注释
首字符(非空格)是一个#的行被认为是注释,可忽略.

环境变量
具有name=value格式的所有行被用来定义环境变量

cron命令
其他的任何(非空)行被认为是cron命令,由下面描述的六个字段组成.
cron命令行包括六个用空白分隔的字段.前五个字段用来指定何时运行命令,剩余的第六个字段(包括所有在第五个字
段后的部分)指定要运行的命令.前五个字段指定下列时间信息:

minute      hour   day of month    month(1=January,....)   day of week (0=Sunday,....)    command to run

25          04     1               *                       *                              echo "HI"

前五种字段的每一种都必须含有一个使用下列语法的标记
crontab时间表示语法标记
标记 含义 例子 解释(如果用在第一个字段中)
* 每次 * 每分钟
n 在指定时间 10 在每小时过10分时
n,n,… 在任何指定时间 22,52 在每小时过22分和每小时过52分时
*/n 每隔n次 */15 每隔15分钟(在每个整点,一刻钟,半点,或差一刻整点时)

使用crontab命令
用户很少直接管理自己的crontab文件(甚至不知道crontab文件被保存在哪里),而是使用crontab命令来编辑,列出或者
删除它.
crontab {[-e] | [-l] | [-r]}
crontab file
编辑,列出或删除当胶crontab文件,或者用file取代当前crontab文件.crontab命令行选项释义如下
crontab命令行选项
选项 作用
-e 编辑当前文件
-l 列出当前文件
-r 删除当前文件

直接编辑crontab文件
用户经常用crontab -e 直接编辑自己的crontab文件.crontab命令将把当前crontab配置打开到用户默认的编辑器中.
当用户编辑完文件并退出编辑器时,修改过的文件内容作为新的crontab配置被添加.

默认的编辑器是/bin/vi,然而crontab像其他许多命令一样,检查editor环境变量.如果变理已经被设置,它将会被用来
替代默认编辑器.

环境变量与cron
配置cron作业时,用户应该知道一个微妙的细节.当crond守护进程启动用户命令时,它没有从shell中运行命令,而是
直接对这个命令派生和执行(fork和exec).这有一个重要的含义:启动时被shell配置的任何环境变量或别名(alias),
例如在/etc/profile或.bash_profile中被定义的任何环境变量,不会在cron执行命令时出现.

如果用户想定义一个环境变量,需要在自己的crontab配置中定义该变量.

Proudly powered by WordPress | Indrajeet by Sus Hill.