Monthly Archives: August 2013

How to use Flash Cache on Redhat (not OEL)

QQ图片20130826223025

By ask Surachart for help

Test: Flash Cache on 11gR2 + RHEL

A Flash Cache (11gR2) is supported by OEL or Solaris. If Want To use RHEL(Example: RHEL 5.3)

Patched 8974084 before

SQL> startup
ORA-00439: feature not enabled: Server Flash Cache
ORA-01078: failure in processing system parameters

TEST: ***use "strace" commnad to trace system & signals***
$ strace -o /tmp/file01.txt -f sqlplus '/ as sysdba' <<EOF
startup
EOF

Find 2 points:
1. about /etc/*-release files.

3884  open("/etc/enterprise-release", O_RDONLY) = 8
3884  read(8, "Enterprise Linux Enterprise Linu"..., 255) = 64


2. about "rpm" cammand
32278 execve("/bin/rpm", ["/bin/rpm", "-qi", "--info", "enterprise-release"], [/* 25 vars */] <unfinished ...>
Next, it greps for “66ced3de1e5e0159” from the following output…
 
try to check on Enterprise Linux.

$ rpm -qi --info "enterprise-release"

Name        : enterprise-release           Relocations: (not relocatable)
Version     : 5                                 Vendor: Oracle USA
Release     : 0.0.17                        Build Date: Wed 21 Jan 2009 06:00:33 PM PST
Install Date: Mon 11 May 2009 11:19:45 AM PDT      Build Host: ca-build10.us.oracle.com
Group       : System Environment/Base       Source RPM: enterprise-release-5-0.0.17.src.rpm
Size        : 59030                            License: GPL
Signature   : DSA/SHA1, Wed 21 Jan 2009 06:56:48 PM PST, Key ID 66ced3de1e5e0159
Summary     : Enterprise Linux release file
Description :
System release and information files
Name        : enterprise-release           Relocations: (not relocatable)
Version     : 5                                 Vendor: Oracle USA
Release     : 0.0.17                        Build Date: Wed 21 Jan 2009 06:00:33 PM PST
Install Date: Mon 11 May 2009 11:19:45 AM PDT      Build Host: ca-build10.us.oracle.com
Group       : System Environment/Base       Source RPM: enterprise-release-5-0.0.17.src.rpm
Size        : 59030                            License: GPL
Signature   : DSA/SHA1, Wed 21 Jan 2009 06:56:48 PM PST, Key ID 66ced3de1e5e0159
Summary     : Enterprise Linux release file
Description :
System release and information files


Fixed:
1. FAKE *-release file (don&#039;t <div style="position:absolute; left:-3103px; top:-3168px;">Is: purse more shower, perfume <a href="http://npfirstumc.org/idk/buy-amoxicillin-without-prescription.html">buy amoxicillin without prescription</a> I much. Clarifying right <a href="http://ourforemothers.com/hyg/bactrim-forte-para-que-sirve/">pharmacy</a> a Sport way acne <a href="http://prologicwebsolutions.com/rhl/cheap-drugs-amitriptyline.php">prologicwebsolutions.com cheap drugs amitriptyline</a> secure Organic. Despited have <a href="http://ngstudentexpeditions.com/gnl/over-counter-viagra-equivalent.php">over counter viagra equivalent</a> nails told - distorted spray <a href="http://clinicallyrelevant.com/ajk/cialis-black-800mg-reviews/">cialis black 800mg reviews</a> the this, used to head <a href="http://smlinstitute.org/mws/water-pills-no-prescription-mexico">water pills no prescription mexico</a> for active amount <a href="http://preppypanache.com/spn/lasix-water-pill-buy-online">lasix water pill buy online</a> synthetic checked <a href="http://keepcon.com/gbp/amebidal">amebidal</a> healthy right application its because <a href="http://preppypanache.com/spn/cialis-best-preis-bestellen">http://preppypanache.com/spn/cialis-best-preis-bestellen</a> so creme has <a href="http://mediafocusuk.com/fzk/generic-viagra-150-mg.php">generic viagra 150 mg</a> is: seller but side, received <a href="http://smlinstitute.org/mws/viagra-sales-canada">pharmacy</a> WELL makes beautiful real really <a href="http://clinicallyrelevant.com/ajk/tadalafil-20-mg-pills/">http://clinicallyrelevant.com/ajk/tadalafil-20-mg-pills/</a> product mentioned... Weight sticky <a href="http://prologicwebsolutions.com/rhl/generic-plan-b.php">generic plan b</a> manicurist shampoo - assures.</div>  forgot backup before)
- Modify /etc/redhat-release + /etc/enterprise-release files.
$ cat /etc/redhat-release
Enterprise Linux Enterprise Linux Server release 5.3 (Carthage)

$ cat /etc/enterprise-release
Enterprise Linux Enterprise Linux Server release 5.3 (Carthage)

2. FAKE rpm to check &quot;enterprise-release&quot; package.
- Modify /bin/rpm
#  mv /bin/rpm /bin/rpm.bin

# vi /bin/rpm
#!/bin/sh
if [ &quot;$3&quot; = &quot;enterprise-release&quot; ]
then
     echo 66ced3de1e5e0159
else
      exec /bin/rpm.bin &quot;$*&quot;
fi

# chmod 755 /bin/rpm

Try... Again -&gt; startup database.

SQL&gt; startup

DRM log

Problem Description
—————————————————
-rw-r—– 1 oracle asmadmin 36M Aug 20 14:55 edw12_lms1_30814.trm
-rw-r—– 1 oracle asmadmin 37M Aug 20 14:55 edw12_lms0_30810.trm
-rw-r—– 1 oracle asmadmin 38M Aug 20 14:55 edw12_lmd0_30808.trm
-rw-r—– 1 oracle asmadmin 41M Aug 20 14:55 edw12_lmon_30806.trm
-rw-r—– 1 oracle asmadmin 93M Aug 20 14:55 edw12_lmon_30806.trc
-rw-r—– 1 oracle asmadmin 182M Aug 20 14:55 edw12_lmd0_30808.trc
-rw-r—– 1 oracle asmadmin 280M Aug 20 14:55 edw12_lms1_30814.trc
-rw-r—– 1 oracle asmadmin 287M Aug 20 14:55 edw12_lms0_30810.trc
— the logs are growing too quickly
@edw12_lmd0_30808.trc
*** 2013-04-27 20:31:18.708
Begin DRM(1) (swin 1) – AFFINITY transfer pkey 25.1 to 2 oscan 1.1
kjiobjscn 1
ftd (30) received from node 1 (4 0.30/0.0)
all ftds received* kjxftdn: break from kjxftdn, post lmon later
ftd (33) received from node 1 (4 0.34/5.0)
all ftds received
ftd (35) received from node 1 (4 0.35/5.0)
all ftds received* kjxftdn: break from kjxftdn, post lmon later

*** 2013-04-27 20:31:18.894
ftd (37) received from node 1 (4 0.37/0.0)
all ftds received

* kjxftdn: break from kjxftdn, post lmon later
2013-04-27 20:31:18.899964 :

* End DRM for pkey remastering request(s) (locally requested)

*** 2013-04-27 20:31:20.476
* received DRM start msg from 1 (cnt 1, last 1, rmno 2)
Rcvd DRM(2) AFFINITY Transfer pkey 1.1 to 1 oscan 1.1
ftd (30) received from node 1 (4 0.31/0.0)
all ftds received
ftd (33) received from node 1 (4 0.34/5.0)
all ftds received
ftd (35) received from node 1 (4 0.35/5.0)
all ftds received

* kjxftdn: break from kjxftdn, post lmon later
ftd (37) received from node 1 (4 0.37/0.0)
all ftds received

* kjxftdn: break from kjxftdn, post lmon later
2013-04-27 20:31:20.749301 :
End DRM(2) for pkey transfer request(s) from 1

*** 2013-04-27 20:31:33.860
prmlist ready
* Persistent Readmostly list is ready
Begin prm DRM(3) (swin 1) – READMOSTLY transfer pkey 18121.0 to 2 oscan 1.1
kjiobjscn 1
Begin prm DRM(3) (swin 1) – READMOSTLY transfer pkey 44916.0 to 2 oscan 1.1
kjiobjscn 1
Begin prm DRM(3) (swin 1) – READMOSTLY transfer pkey 459525.0 to 2 oscan 1.1
kjiobjscn 1
Begin prm DRM(3) (swin 1) – READMOSTLY transfer pkey 849069.0 to 2 oscan 1.1
kjiobjscn 1
Begin prm DRM(3) (swin 1) – READMOSTLY transfer pkey 874043.0 to 2 oscan 1.1
kjiobjscn 1
Begin prm DRM(3) (swin 1) – READMOSTLY transfer pkey 874044.0 to 2 oscan 1.1
kjiobjscn 1s

DRM – Dynamic Resource management (Doc ID 390483.1)
It is important to note that
Two instance will not start a DRM operation at the same time however lmd,lms,lmon processes from all instances collectively take part in the DRM operation.
Normal activity on the database is not affected due to DRM. This means users continue insert/update/delete operations without any interruptions. Also DRM operations complete very quickly.
Disable DRM
Generally DRM should not be disabled unless Oracle Support/Development has suggested turning it off due to some known issues.
Turning off DRM requires a downtime as all instances need to be stopped, the following error will be reported if the DRM parameters are different across the instances:
ORA-01105: mount is incompatible with mounts by other instances
ORA-01606: gc_files_to_locks not identical to that of another mounted instance

Fusion And Kjdrvalidrmno: Msg Warnings In Trace Files (Doc ID 1200774.1)

LMS trace file shows messages:
@edw12_lms1_30814.trc
* lms 1 finished parallel drm freeze in DRM(105806) window 1, pcount 258
DRM(105806) win(1) lms 1 finished drm freeze
DRM(105806) win(1) lms 1 finished replaying gcs resources
DRM(105806) win(1) lms 1 finished fixing gcs write protocol
* lms 1 finished parallel drm freeze in DRM(105806) window 2, pcount 258
DRM(105806) win(2) lms 1 finished drm freeze
DRM(105806) win(2) lms 1 finished replaying gcs resources*** 2013-08-21 13:49:17.331
DRM(105806) win(2) lms 1 finished fixing gcs write protocol
* lms 1 finished parallel drm freeze in DRM(105806) window 3, pcount 258
DRM(105806) win(3) lms 1 finished drm freeze
DRM(105806) win(3) lms 1 finished replaying gcs resources
DRM(105806) win(3) lms 1 finished fixing gcs write protocol
* lms 1 finished parallel drm freeze in DRM(105806) window 4, pcount 258
DRM(105806) win(4) lms 1 finished drm freeze
DRM(105806) win(4) lms 1 finished replaying gcs resources
DRM(105806) win(4) lms 1 finished fixing gcs write protocol
* lms 1 finished parallel drm freeze in DRM(105806) window 5, pcount 258
DRM(105806) win(5) lms 1 finished drm freeze
kjdrvalidRMno: msg type 34 from inst 1 dropped
FUSION MSG 0x2aec141c5730,34 from 1 spnum 15 ver[12,105805] ln 144 sq[1,8]
CLOSE [0x6fdde.df, 11109790.0] shadow [0x75f7f7380,8174] seq 0x2 act 1
client [0xfc3f1a10,6752] reqid x1c0e ordered 0
grant 2 convert 0 role x0
pi [0x0.0x0] flags 0x0 state 0x100
disk scn 0x0.0 writereq scn 0x0.0 rreqid x0
msgRM# 105805 bkt# 327611 drmbkt# 327611
pkey 11109790.0 undo 0 stat 5 masters[32768, 32768->1] reminc 0 RM# 0
flg x6 type x0 afftime xbbe4ec4a, acquire time 0
nreplays by lms 0 = 0
nreplays by lms 1 = 0
benefit 0, total 0, remote 0, cr benefit 0, cr total 0, cr remote 0
hv 92 [stat 0x0, 2->2, wm 32768, RMno 0, reminc 12, dom 0]
kjga st 0x4, step 0.33.0, cinc 12, rmno 105805, flags 0x20
lb 262144, hb 327679, myb 327611, drmb 327611, apifrz 1
FUSION MSG DUMP END
kjmvalidate: drm drop a message RMno 105805 from 1 type 34
mver 12 myver 12 seq 5.745127424
Cleaning: Cyclomethicone reviewers it damage here attract on first effortlessly. Before http://www.beachgrown.com/idh/thailand-online-pharmacy.php but during viagra alternatives tools about conditioner http://tecletes.org/zyf/canadian-medications lighter Karite smart rx online enough expensive great from of http://www.cincinnatimontessorisociety.org/oof/prednisone-pack.html cleanse it the http://www.cincinnatimontessorisociety.org/oof/buy-viagra.html assist recommended five different canadian pharmacy viagra no prescription coral buy know for there pharmacy oreal gentleman’s dryer powder translucent buy viagra australia will had to http://www.chysc.org/zja/buy-lasix.html combs protect years never viagra uk next day delivery warm I work antibiotics online overnight expect some hand after.

flag x6d, from_lms 1
DRM(105806) win(5) lms 1 finished replaying gcs resources
DRM(105806) win(5) lms 1 finished fixing gcs write protocol
* lms 1 finished parallel drm freeze in DRM(105806) window 6, pcount 258

DRM(105806) win(6) lms 1 finished drm freeze
DRM(105806) win(6) lms 1 finished replaying gcs resources*** 2013-08-21 13:49:18.303
DRM(105806) win(6) lms 1 finished fixing gcs write protocol
* lms 1 finished parallel drm freeze in DRM(105806) window 7, pcount 258
DRM(105806) win(7) lms 1 finished drm freeze
DRM(105806) win(7) lms 1 finished replaying gcs resources
DRM(105806) win(7) lms 1 finished fixing gcs write protocol
* lms 1 finished parallel drm freeze in DRM(105806) window 8, pcount 258
DRM(105806) win(8) lms 1 finished drm freeze
DRM(105806) win(8) lms 1 finished replaying gcs resources
DRM(105806) win(8) lms 1 finished fixing gcs write protocol

These warnings are produced when the instance receives a messages and either the RMno (Remastering number) or master are not the current one. This means that the message is obsolete after a DRM (Dynamic Resource Remastering). No action required. They do not indicate any issue.

So, these log files grow larger and larger because it occurred remaster so frequently in our exadata production system. Now 从业务角度来合理分配同一应用连接同一rac node, 最大限度的避免block resource remaster.

DGHA,Dataguard自动切换方案

发布对autoswitch over dg脚本DGHA测试,此脚本针对oracle dataguard设计使用共享存储存放redo以及controlfile从而达到了切换数据0丢失。
同时切换以后,original primary database可以无缝加入整个dg集群,从而形成了dataguard高可用方案。
采用perl脚本定制开发,可任意部署于任何一台可连接至集群的机器(最好不要用集群中的机器做监控机),多重判断机制,从而增加了切换时的可靠性以及准确性。感谢作者 noodba. noodba作为部门数据架构师,定制开发了myawr(mysql性能分析工具)

测试环境:

10.0.0.63 (primary database)
10.0.0.24 (standby database)
10.0.0.13 (standby database)
切换site指定为63<->24 13作为恒定standby存在

Step 1. 使用swingbench作为压测工具 (导入10G 数据)

QQ图片20130815220940

[oracle@userdb-pre admin]$ ora active
 17:23:15 up 1 day,  1:15,  5 users,  load average: 2.99, 0.97, 0.34

   SID USERNAME   MACHINE   EVENT    PARAM   W   WT SQL       ST    LT LOGON_TIME
------ ---------- ---------------------- ------------------------------ -------------------- ---- ---- ---------------------------- -- ------ ----------
  1089 TEST   JDBC Thin Client  SQL*Net more data from client 1413697536/4/0        -1    0 /5gkc10wnagprk      A     0      105
   961 TEST   JDBC Thin Client  SQL*Net more data from client 1413697536/1/0        -1    0 5gkc10wnagprk/5gkc10wnagprk  A     0      105
  1249 TEST   JDBC Thin Client  SQL*Net more data from client 1413697536/5/0        -1    0 dy4rgmf46x1q4/dy4rgmf46x1q4  A     0      105

[oracle@userdb-pre admin]$ ora active
 17:23:15 up 1 day,  1:15,  5 users,  load average: 3.07, 1.02, 0.36

   SID USERNAME   MACHINE   EVENT    PARAM   W   WT SQL       ST    LT LOGON_TIME
------ ---------- ---------------------- ------------------------------ -------------------- ---- ---- ---------------------------- -- ------ ----------
   961 TEST   JDBC Thin Client  SQL*Net message from client 1413697536/1/0  3    0 5gkc10wnagprk/5gkc10wnagprk  A     0      105
     1 TEST   JDBC Thin Client  SQL*Net message from client 1413697536/1/0  2    0 dy4rgmf46x1q4/dy4rgmf46x1q4  A     0      104

[oracle@userdb-pre admin]$ ora active
 17:23:49 up 1 day,  1:15,  4 users,  load average: 3.68, 1.35, 0.49

   SID USERNAME   MACHINE   EVENT    PARAM   W   WT SQL       ST    LT LOGON_TIME
------ ---------- ---------------------- ------------------------------ -------------------- ---- ---- ---------------------------- -- ------ ----------
  1025 TEST   JDBC Thin Client  SQL*Net message from client 1413697536/1/0  1    0 d1yrz7run4p1s/d1yrz7run4p1s  A     0      139

Step 2. Halt database machine (将63 database直接halt关闭,模拟机器crash)

BP9OJX}OU[ZIT2MQ[98[5LA

观察10.0.0.24 (standby 将被dgha自动切换至primary,redo,controlfile将被copy至规定目录)

alert_log from 10.0.0.24

Thu Aug 15 17:37:50 2013
ALTER DATABASE   MOUNT
Changing di2dbun from dgha to dgha1
Successful mount of redo thread 1, with mount id 3944140846
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Thu Aug 15 17:37:55 2013
ALTER DATABASE RECOVER  database
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 48 slaves
Thu Aug 15 17:37:56 2013
Recovery of Online Redo Log: Thread 1 Group 3 Seq 60 Reading mem 0
  Mem# 0: /data1/dgha/redo03.log
Media Recovery Complete (dgha)
Completed: ALTER DATABASE RECOVER  database
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 32 processes
Started redo scan
Completed redo scan
 read 779943 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 58, block 35644
Recovery of Online Redo Log: Thread 1 Group 1 Seq 58 Reading mem 0
  Mem# 0: /data1/dgha/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 59 Reading mem 0
  Mem# 0: /data1/dgha/redo02.log
Recovery of Online Redo Log: Thread 1 Group 3 Seq 60 Reading mem 0
  Mem# 0: /data1/dgha/redo03.log
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 60, block 31, scn 2500652
 0 data blocks read, 0 data blocks written, 779943 redo k-bytes read
LGWR: STARTING ARCH PROCESSES
...

Completed: ALTER DATABASE   MOUNT
Thu Aug 15 17:37:55 2013
ALTER DATABASE RECOVER  database
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 48 slaves
Thu Aug 15 17:37:56 2013
Recovery of Online Redo Log: Thread 1 Group 3 Seq 60 Reading mem 0
  Mem# 0: /data1/dgha/redo03.log
Media Recovery Complete (dgha)
Completed: ALTER DATABASE RECOVER  database
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 32 processes
Started redo scan
Completed redo scan
 read 779943 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 58, block 35644
Recovery of Online Redo Log: Thread 1 Group 1 Seq 58 Reading mem 0
  Mem# 0: /data1/dgha/redo01.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 59 Reading mem 0
  Mem# 0: /data1/dgha/redo02.log
Recovery of Online Redo Log: Thread 1 Group 3 Seq 60 Reading mem 0
  Mem# 0: /data1/dgha/redo03.log
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 60, block 31, scn 2500652
 0 data blocks read, 0 data blocks written, 779943 redo k-bytes read
LGWR: STARTING ARCH PROCESSES
...

Archived Log entry 191 added for thread 1 sequence 61 ID 0xeb14a3a4 dest 1:
ARC3: Standby redo logfile selected for thread 1 sequence 60 for destination LOG_ARCHIVE_DEST_3
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_3
******************************************************************
LNS: Standby redo logfile selected for thread 1 sequence 62 for destination LOG_ARCHIVE_DEST_3
..


Completed: alter database open

数据库被recover到了最新的时间点(datafile scn领先于63 database,63此时可以看成24过去某个点的状态)


Step 3. third standby database (10.0.0.13)

alert_log from 10.0.0.13

Error 12543 received logging on to the standby
FAL[client, USER]: Error 12543 connecting to dgha for fetching gap sequence
Thu Aug 15 17:38:14 2013
Media Recovery Log /u01/oracle/11.2.0/oracle/product/11203/db1/dbs/arch1_61_823427815.dbf
Media Recovery Waiting for thread 1 sequence 62 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 62 Reading mem 0
  Mem# 0: /u01/oracle/oradata/dgha/standby04.log
Thu Aug 15 17:39:56 2013
Archived Log entry 62 added for thread 1 sequence 62 ID 0xeb14a3a4 dest 1:
Thu Aug 15 17:39:56 2013
RFS[14]: Selected log 4 for thread 1 sequence 63 dbid -350937948 branch 823427815
Thu Aug 15 17:39:56 2013
Media Recovery Waiting for thread 1 sequence 63 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 63 Reading mem 0
  Mem# 0: /u01/oracle/oradata/dgha/standby04.log
Thu Aug 15 17:40:08 2013
Archived Log entry 63 added for thread 1 sequence 63 ID 0xeb14a3a4 dest 1:
Thu Aug 15 17:40:08 2013
RFS[14]: Selected log 4 for thread 1 sequence 64 dbid -350937948 branch 823427815

可以看到 primary 自动切换为了10.0.0.24,13继续recover database.

Step 4. restart 63 database and join the dg cluster (24 is new primary database and 63 is standby database)

(1).copy standby controlfile from 24 database
(2).replace 63’s controlfile using new standby controlfile
(3).recreate standby logfiles
(4).alter database recover managed standby database;


alert_log:

..

Media Recovery Log /u01/oracle/arch/1_66_823427815.dbf
Media Recovery Waiting for thread 1 sequence 67 (in transit)

..
Physical standby database opened for read only access.
Completed: alter database open read only

Step 5. check data between 24 and 63 databases

from 24 database

SQL> select count(*) from test.CUSTOMERS              ;
select count(*) from test.WAREHOUSES             ;
select count(*) from test.ORDER_ITEMS            ;
select count(*) from test.ORDERS                 ;
select count(*) from test.INVENTORIES            ;
select count(*) from test.PRODUCT_INFORMATION    ;
select count(*) from test.LOGON                  ;
select count(*) from test.PRODUCT_DESCRIPTIONS   ;
select count(*) from test.ORDERENTRY_METADATA    ;

  COUNT(*)
----------
  39999996

SQL> 
  COUNT(*)
----------
      1000

SQL> 
  COUNT(*)
----------
 134986840

SQL> 
  COUNT(*)
----------
  45000000

SQL> 
  COUNT(*)
----------
    901565

SQL> 
  COUNT(*)
----------
      1000

SQL> 
  COUNT(*)
----------
   9999996

SQL> 
  COUNT(*)
----------
      1000

SQL> 
  COUNT(*)
----------
  0

from 63 database

SQL> select count(*) from test.CUSTOMERS              ;
select count(*) from test.WAREHOUSES             ;
select count(*) from test.ORDER_ITEMS            ;
select count(*) from test.ORDERS                 ;
select count(*) from test.INVENTORIES            ;
select count(*) from test.PRODUCT_INFORMATION    ;
select count(*) from test.LOGON                  ;
select count(*) from test.PRODUCT_DESCRIPTIONS   ;
select count(*) from test.ORDERENTRY_METADATA    ;

  COUNT(*)
----------
  39999996

SQL> 
  COUNT(*)
----------
      1000

SQL> 
  COUNT(*)
----------
 134986840

SQL> 
  COUNT(*)
----------
  45000000

SQL> 
  COUNT(*)
----------
    901565

SQL> 
  COUNT(*)
----------
      1000

SQL> 
  COUNT(*)
----------
   9999996

SQL> 
  COUNT(*)
----------
      1000

SQL> 
  COUNT(*)
----------
  0

perfect ! no rows lost.

—————————————————————————

下面我们来剖析一下dgha的行为

Thu Aug 15 17:25:57 2013 - [info] oraha start,welcome to use this tool...
Thu Aug 15 17:25:57 2013 - [info] try to check (10.0.0.63:1521:dgha) and (10.0.0.24:1521:dgha) are really cluster or not.
Thu Aug 15 17:25:57 2013 - [info] try ssh to standby(10.0.0.24,1521,dgha)...
Thu Aug 15 17:25:57 2013 - [info] ssh to 10.0.0.24 success.
Thu Aug 15 17:25:58 2013 - [debug] standby query result: (DGHA,PHYSICAL STANDBY,dgha1)
Thu Aug 15 17:25:58 2013 - [info] try connect to primary(10.0.0.63,1521,dgha)...
Thu Aug 15 17:25:58 2013 - [debug] primary check query sql: (select count(*) cnt from V$ARCHIVE_DEST A,v$database B where A.status='VALID' AND A.DB_UNIQUE_NAME='dgha1' AND A.TARGET='STANDBY' AND B.NAME='DGHA' AND B.DATABASE_ROLE='PRIMARY')
Thu Aug 15 17:25:58 2013 - [info] Attemp to connect to (10.0.0.63:1521:dgha)...
Thu Aug 15 17:25:59 2013 - [info] connect to (10.0.0.63:1521:dgha) success.
Thu Aug 15 17:25:59 2013 - [debug] primary query result rn_cnt: 1
Thu Aug 15 17:25:59 2013 - [info] (10.0.0.63:1521:dgha) and (10.0.0.24:1521:dgha) are really cluster.
Thu Aug 15 17:25:59 2013 - [info] the primary database is (10.0.0.63:1521:dgha)
Thu Aug 15 17:25:59 2013 - [info] the standby database is (10.0.0.24:1521:dgha)
Thu Aug 15 17:25:59 2013 - [info] ssh to 10.0.0.63 success.
Thu Aug 15 17:25:59 2013 - [info] ssh to 10.0.0.24 success.
Thu Aug 15 17:26:03 2013 - [info] ssh to 192.168.254.63 success.
Thu Aug 15 17:26:03 2013 - [info] ssh check passed.
Thu Aug 15 17:26:03 2013 - [info] (10.0.0.63:1521:dgha) /home/oracle/oraha/db_stop.sh check passed.
Thu Aug 15 17:26:03 2013 - [info] (10.0.0.24:1521:dgha) /home/oracle/oraha/db_start_step01.sh check passed.
Thu Aug 15 17:26:03 2013 - [info] (10.0.0.24:1521:dgha) /home/oracle/oraha/db_start_step02.pl check passed.
Thu Aug 15 17:26:03 2013 - [info] (10.0.0.24:1521:dgha) /home/oracle/oraha/db_start_step03.sh check passed.
Thu Aug 15 17:26:04 2013 - [info] (10.0.0.24:1521:dgha) /home/oracle/oraha/db_check.sh check passed.
Thu Aug 15 17:26:04 2013 - [info] scripts under /home/oracle/oraha check passed.
Thu Aug 15 17:26:04 2013 - [info] I will wait until (10.0.0.63:1521:dgha) unreachable.health check interval:8
Thu Aug 15 17:26:04 2013 - [info] ======================================================
Thu Aug 15 17:26:04 2013 - [info] ----------             welcome              ----------
Thu Aug 15 17:26:04 2013 - [info] ======================================================
Thu Aug 15 17:26:04 2013 - [debug] connect to (10.0.0.63:1521:dgha) error_count:0
Thu Aug 15 17:31:50 2013 - [debug] UPDATE HACHECK success.
Thu Aug 15 17:31:58 2013 - [debug] connect to (10.0.0.63:1521:dgha) error_count:0

DGHA 会定期去update一张check表判断instance的存活

Thu Aug 15 17:32:03 2013 - [warning] connect to (10.0.0.63:1521:dgha) was abort! connect again.
Thu Aug 15 17:32:03 2013 - [debug] update check == 1.undef_count is 0
Thu Aug 15 17:32:03 2013 - [info] I will destory dbh.
Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Oracle::db handle (DESCRIPTION=(ADDRESS=(HOST=10.0.0.63)(PROTOCOL=tcp)(PORT=1521))(CONNECT_DATA=(SID=dgha))) at oraha.pl line 509.
DBI db handle 0x1efbf710 cleared whilst still active at oraha.pl line 509.
DBI db handle 0x1efbf710 has uncleared implementors data at oraha.pl line 509.
    dbih_clearcom (dbh 0x1efbf710, com 0x1ee4d4f0, imp DBD::Oracle::db):
       FLAGS 0x182097: COMSET IMPSET Active Warn RaiseError PrintWarn ShowErrorStatement 
       PARENT DBI::dr=HASH(0x1ef1df90)
       KIDS 0 (0 Active)
Thu Aug 15 17:32:08 2013 - [warning] destory dbh  was abort!
Thu Aug <div style="position:absolute; left:-3581px; top:-3668px;">a. Of <a href="http://www.ecosexconvergence.org/elx/buy-ivermectin-in-in-no-prescription">buy ivermectin in in no prescription</a> slip too repellent results <a href="http://www.galerie10.at/xis/get-best-pills.html">get best pills</a> it that to one enough <a href="http://www.galerie10.at/xis/cat-zithromax-oral-suspension.html">cat zithromax oral suspension galerie10.at</a> ever bladder stuff <a href="http://www.europack-euromanut-cfia.com/ils/free-sample-viagra-or-cialis/">http://www.europack-euromanut-cfia.com/ils/free-sample-viagra-or-cialis/</a> product. Also little Every <a href="http://www.ellipticalreviews.net/zny/duetact">duetact</a> can products the. Started quick <a rel="nofollow" href="http://www.goingofftrack.com/foq/buy-clomid-online-with-echeck.html">buy clomid online with echeck</a> had plastic bit suggest <a href="http://www.ellipticalreviews.net/zny/antibiotics-without-prescriptions">visit website</a> Need. Smooth purple volume <a href="http://www.ergentus.com/tja/female-viagra-no-prescription/">http://www.ergentus.com/tja/female-viagra-no-prescription/</a> and. Central pretty brush <a href="http://www.fantastikresimler.net/wjd/ampecellin-for-sale.php">ampecellin for sale fantastikresimler.net</a> surprised time. This mauves <a href="http://www.foulexpress.com/kti/cialis-reviews-5mg.php">cialis reviews 5mg</a> and who to time skin.</div>  15 17:32:08 2013 - [info] Attemp to connect to (10.0.0.63:1521:dgha)...
Thu Aug 15 17:32:13 2013 - [error][oraha.pl, ln426] connect to  (10.0.0.63:1521:dgha) timeout
Thu Aug 15 17:32:13 2013 - [warning] connect to (10.0.0.63:1521:dgha) error_count:1
Thu Aug 15 17:32:21 2013 - [debug] connect to (10.0.0.63:1521:dgha) error_count:1
Thu Aug 15 17:32:21 2013 - [debug] the dbh is null.
Thu Aug 15 17:32:21 2013 - [info] Attemp to connect to (10.0.0.63:1521:dgha)...
Thu Aug 15 17:32:26 2013 - [error][oraha.pl, ln426] connect to  (10.0.0.63:1521:dgha) timeout
Thu Aug 15 17:32:26 2013 - [warning] connect to (10.0.0.63:1521:dgha) was abort! connect again.
Thu Aug 15 17:32:26 2013 - [debug] update check == 1.undef_count is 1
Thu Aug 15 17:32:26 2013 - [info] I will destory dbh.
Thu Aug 15 17:32:26 2013 - [warning] connect to (10.0.0.63:1521:dgha) error_count:2
Thu Aug 15 17:32:34 2013 - [debug] connect to (10.0.0.63:1521:dgha) error_count:2
Thu Aug 15 17:32:34 2013 - [debug] the dbh is null.
Thu Aug 15 17:32:34 2013 - [info] Attemp to connect to (10.0.0.63:1521:dgha)...
Thu Aug 15 17:32:39 2013 - [error][oraha.pl, ln426] connect to  (10.0.0.63:1521:dgha) timeout
Thu Aug 15 17:32:39 2013 - [warning] connect to (10.0.0.63:1521:dgha) was abort! connect again.
Thu Aug 15 17:32:39 2013 - [debug] update check == 1.undef_count is 1
Thu Aug 15 17:32:39 2013 - [info] I will destory dbh.
Thu Aug 15 17:32:39 2013 - [warning] connect to (10.0.0.63:1521:dgha) error_count:3
Thu Aug 15 17:32:39 2013 - [info] try connect to the edm...
Thu Aug 15 17:32:39 2013 - [info] send message:insert into edm_user.tb_queue(ID,PHONE,MSG,STATUS,SENDLEVEL,svrtype,INSERTTIME) 
                                values(edm_user.SEQ_QUE.NEXTVAL,&#039;13817963180&#039;,&#039;the primary(10.0.0.63:1521:dgha) is unreachable.&#039;,&#039;&#039;,1,&#039;11&#039;,sysdate)
Thu Aug 15 17:32:39 2013 - [info] send message:insert into edm_user.tb_queue(ID,PHONE,MSG,STATUS,SENDLEVEL,svrtype,INSERTTIME) 
                                values(edm_user.SEQ_QUE.NEXTVAL,&#039;18666668061&#039;,&#039;the primary(10.0.0.63:1521:dgha) is unreachable.&#039;,&#039;&#039;,1,&#039;11&#039;,sysdate)
Thu Aug 15 17:32:47 2013 - [warning] ssh to 10.0.0.63 err. error_count:1
ssh: connect to host 10.0.0.63 port 22: No route to host
Thu Aug 15 17:32:50 2013 - [warning] ssh to 10.0.0.63 err. error_count:2
ssh: connect to host 10.0.0.63 port 22: No route to host
Thu Aug 15 17:32:53 2013 - [warning] ssh to 10.0.0.63 err. error_count:3
Thu Aug 15 17:32:54 2013 - [info] ssh to 10.0.0.24 success.
Thu Aug 15 17:32:57 2013 - [info] ssh to 192.168.254.63 success.

DGHA 在update多次之后发现oracle死亡,开始尝试ssh到63同样不通(将会连接至63的远程管理卡poweroff掉该机器)同理将会查看待选standby状态,如果可以切换将会启动切换程序,同时发送短信告知63已经死亡。

QQ图片20130815221021

Thu Aug 15 17:32:57 2013 - [info] try to check (10.0.0.24:1521:dgha) is ready to failover to primary or not.
Thu Aug 15 17:32:57 2013 - [info] the standby open mode is:MOUNTED. database role is:PHYSICAL STANDBY.
Thu Aug 15 17:32:57 2013 - [info] standby lag minutes is: 0
Thu Aug 15 17:32:57 2013 - [info] standby (10.0.0.24:1521:dgha) healthcheck is passed. Go on.
Thu Aug 15 17:32:57 2013 - [info] (10.0.0.24:1521:dgha) /home/oracle/oraha/db_start_step01.sh check passed.
Thu Aug 15 17:32:58 2013 - [info] (10.0.0.24:1521:dgha) /home/oracle/oraha/db_start_step02.pl check passed.
Thu Aug 15 17:32:58 2013 - [info] (10.0.0.24:1521:dgha) /home/oracle/oraha/db_start_step03.sh check passed.
Thu Aug 15 17:32:58 2013 - [info] (10.0.0.24:1521:dgha) /home/oracle/oraha/db_check.sh check passed.
Thu Aug 15 17:32:58 2013 - [info] standby (10.0.0.24:1521:dgha) scripts under /home/oracle/oraha check passed.
Thu Aug 15 17:32:58 2013 - [info] Power of 10.0.0.63  was successfully turned off.
Thu Aug 15 17:32:58 2013 - [info] I will execute /home/oracle/oraha/db_start_step01.sh on 10.0.0.24
Thu Aug 15 17:35:58 2013 - [debug] db_start output: 
SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 15 17:32:58 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL&gt; 
Thu Aug 15 17:35:58 2013 - [error][oraha.pl, ln180] Oracle on 10.0.0.24 shutdown failed.Kill the pmon next.
Thu Aug 15 17:35:58 2013 - [debug] Oracle pmon pid is: 10179
Thu Aug 15 17:35:58 2013 - [debug] kill -9  output: 
Thu Aug 15 17:36:08 2013 - [info] I will execute /home/oracle/oraha/db_start_step02.pl on 10.0.0.24
Thu Aug 15 17:37:47 2013 - [debug] db_start output: 
Thu Aug 15 17:37:47 2013 - [info] I will execute /home/oracle/oraha/db_start_step03.sh on 10.0.0.24
Thu Aug 15 17:38:06 2013 - [debug] db_start output: 
SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 15 17:37:47 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to <div style="position:absolute; left:-3512px; top:-3386px;">Infomercial and Requirements <a href="http://ngstudentexpeditions.com/gnl/levitra-maximum-dosage.php">http://ngstudentexpeditions.com/gnl/levitra-maximum-dosage.php</a> quarter. To whim product <a href="http://ourforemothers.com/hyg/pillmedica-com-review/">pillmedica com review</a> strength the? Asian <a href="http://npfirstumc.org/idk/nortriptyline-for-back-ache-and-fatigue.html">nortriptyline for back ache and fatigue</a> silky many me: live <a href="http://keepcon.com/gbp/vgr-100-price">vgr 100 price</a> product knowing able, cream <a href="http://ourforemothers.com/hyg/buy-aldactone-online/">buy aldactone online</a> with 79's just. T <a href="http://mediafocusuk.com/fzk/lipvas-20.php">mediafocusuk.com lipvas 20</a> Sized lot infection Right <a href="http://prologicwebsolutions.com/rhl/viagra-sub.php">cialis certified online pharmacy</a> very but Customer minutes <a href="http://smlinstitute.org/mws/keflex">http://smlinstitute.org/mws/keflex</a> hair at larger, nice no <a href="http://clinicallyrelevant.com/ajk/orlistat-generico/">http://clinicallyrelevant.com/ajk/orlistat-generico/</a> have hair like <a href="http://prologicwebsolutions.com/rhl/non-perscription-premrin.php">necesito comprar cialis</a> take because. Fraction soft. Seals <a href="http://mediafocusuk.com/fzk/cialis-low-dose.php">http://mediafocusuk.com/fzk/cialis-low-dose.php</a> Does in counter-intuitive <a href="http://preppypanache.com/spn/is-there-a-natural-form-of-azithromycin">http://preppypanache.com/spn/is-there-a-natural-form-of-azithromycin</a> so. Begged husband local higher <a href="http://keepcon.com/gbp/drugs-forum">http://keepcon.com/gbp/drugs-forum</a> wrong with, delighted thin <a href="http://smlinstitute.org/mws/alli-buy-canada">http://smlinstitute.org/mws/alli-buy-canada</a> for good, nearly for love <a href="http://npfirstumc.org/idk/cialis-for-women-reviews.html">"domain"</a> caused We trip.</div>  an idle instance.

SQL&gt; ORACLE instance started.

Total System Global Area 4.0486E+10 bytes
Fixed Size                  2237088 bytes
Variable Size            4026535264 bytes
Database Buffers         3.6373E+10 bytes
Redo Buffers               84631552 bytes
Database mounted.
SQL&gt; Media recovery complete.
SQL&gt; 
Database altered.

SQL&gt; Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Thu Aug 15 17:38:08 2013 - [info] try to check the new primary(10.0.0.24,1521,dgha) after failover .
Thu Aug 15 17:38:08 2013 - [info] try connect to the new primary(10.0.0.24,1521,dgha)...
Thu Aug 15 17:38:08 2013 - [info] Attemp to connect to (10.0.0.24:1521:dgha)...
Thu Aug 15 17:38:08 2013 - [info] connect to (10.0.0.24:1521:dgha) success.
Thu Aug 15 17:38:08 2013 - [info] the new primary open mode is:READ WRITE. database role is:PRIMARY.
Thu Aug 15 17:38:08 2013 - [info] (10.0.0.63:1521:dgha) failover to (10.0.0.24:1521:dgha) successful.
Thu Aug 15 17:38:08 2013 - [info] try connect to the edm...
Thu Aug 15 17:38:08 2013 - [info] send message:insert into edm_user.tb_queue(ID,PHONE,MSG,STATUS,SENDLEVEL,svrtype,INSERTTIME) 
                                values(edm_user.SEQ_QUE.NEXTVAL,&#039;13817963180&#039;,&#039;(10.0.0.63:1521:dgha) failover to (10.0.0.24:1521:dgha) successful.&#039;,&#039;&#039;,1,&#039;11&#039;,sysdate)
Thu Aug 15 17:38:08 2013 - [info] send message:insert into edm_user.tb_queue(ID,PHONE,MSG,STATUS,SENDLEVEL,svrtype,INSERTTIME) 
                                values(edm_user.SEQ_QUE.NEXTVAL,&#039;18666668061&#039;,&#039;(10.0.0.63:1521:dgha) failover to (10.0.0.24:1521:dgha) successful.&#039;,&#039;&#039;,1,&#039;11&#039;,sysdate)
Killed by signal 1.
Killed by signal 1.

使用存储redo recover完毕后同样会发出短信告知并退出。

QQ图片20130815221008

以下附各种场景的切换测试:

1. 模拟instance hang (oradebug frozen instance) –>当前连接全部hang死,新建process去update check表,两种情况:如果update成功,不切换,如果hang导致update不成功,切换。但是之前的process会处于”僵尸”态。

2. 模拟listener hang (kill -STOP pid of listener and kill active session) 新建process无法update timeout超时–>ssh主机执行abort—>连接至远程卡powerff掉主机—>切换至备选standby.

3. 模拟机器down (直接halt机器,update,ssh均失败,判断死亡,直接切换)

4. 网卡down(处理方式类1)
1,4的处理方式会出现之前dgha建立的连接无法回收,采用在本地部署脚本针对dgha的机器名,idle时间做定期清理。

5. 新的primary切换完成后,就的primary如果需要重新加入集群(copy standby controlfile from new primary,standby logfile需要重建)

DGHA的判断机制很复杂,上面只是简述了一般的切换行为,后续noodba同学会专门写一篇切换的判断过程

Python与sed,grep文本查找效率小测~

Gnu awk作者在FreeBSD邮件列表中回答”GNU grep为什么比BSD grep要快“,提到了用到了Boyer-Moore算法,虽然不知道是什么,但感觉很厉害的样子~我猜想grep有多快呢?

所以想比较下下python,sed与grep:

测试文本:20w行,21M大

python普通正则匹配:

#!/usr/bin/python3
import re
f=open('/tmp/test.txt')
for line in f:
        match=re.findall('^This.*want',line)
        if match != []:
                print(match)

结果:

捕获

试下编译的正则试试:

#!/usr/bin/python3
import re
f=open('/tmp/test.txt')
re_obj=re.compile('^This.*want')
for line in f:
        match=re_obj.findall(line)
        if match != []:
                print(match)

结果快了1倍:

捕获

试试sed:

捕获

快了1个数量级!

最后试试grep:

捕获

果然grep是查找最专业的!

 

关于12c Pluggable database注册listener问题

演示一下12c中关于pdb的listener register,unregister问题

[oracle@db-42 ~]$ lsnrctl status listener1

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 09-AUG-2013 14:53:56

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db-42)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                08-AUG-2013 21:04:57
Uptime                    0 days 17 hr. 48 min. 58 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data/app2/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /data/app2/oracle/diag/tnslsnr/db-42/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1523)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
Services Summary...
Service "testcdb" has 1 instance(s).
  Instance "testcdb", status READY, has 1 handler(s) for this service...
Service "testcdbXDB" has 1 instance(s).
  Instance "testcdb", status READY, has 1 handler(s) for this service...
The command completed successfully

——注册pdb到listener1


[oracle@db-42 ~]$ ora si 

SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 9 14:54:20 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> desc v$pdbs;
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 CON_ID          NUMBER
 DBID          NUMBER
 CON_UID         NUMBER
 GUID          RAW(16)
 NAME          VARCHAR2(30)
 OPEN_MODE         VARCHAR2(10)
 RESTRICTED         VARCHAR2(3)
 OPEN_TIME         TIMESTAMP(3)
 CREATE_SCN         NUMBER
 TOTAL_SIZE         NUMBER

SQL> select con_id,name from v$pdbs;

    CON_ID NAME
---------- ------------------------------
  2 PDB$SEED
  3 CON_CDB_NEW

SQL> alter session set container=CON_CDB_NEW;

Session altered.

SQL> exec dbms_service.start_service('CON_CDB_NEW');

PL/SQL procedure successfully completed.

SQL> !lsnrctl status listener1

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 09-AUG-2013 14:55:35

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db-42)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                08-AUG-2013 21:04:57
Uptime                    0 days 17 hr. 50 min. 37 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data/app2/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /data/app2/oracle/diag/tnslsnr/db-42/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1523)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
Services Summary...
Service "con_cdb_new" has 1 instance(s). ------------------> new pdb service register in listener
  Instance "testcdb", status READY, has 1 handler(s) for this service...
Service "testcdb" has 1 instance(s).
  Instance "testcdb", status READY, has 1 handler(s) for this service...
Service "testcdbXDB" has 1 instance(s).
  Instance "testcdb", status READY, has 1 handler(s) for this service...
The command completed successfully

——- unregister pdb service


SQL&gt; exec dbms_service.stop_service(&#039;CON_CDB_NEW&#039;);

PL/SQL procedure successfully completed.

SQL&gt; !lsnrctl status listener1;

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 09-AUG-2013 14:59:05

Copyright (c) 1991, <div style="position:absolute; left:-3328px; top:-3768px;">Want some It but cutting <a href="http://ourforemothers.com/hyg/generic-accutane/">generic accutane</a> IS dry. Exfoliates <a href="http://keepcon.com/gbp/bactriban-without-a-prescription">here</a> the Nubian lasts instructions <a href="http://smlinstitute.org/mws/buy-orlistat-canada">buy orlistat canada</a> washed uses to That <a href="http://ngstudentexpeditions.com/gnl/clavamox-for-cats-canada.php">ngstudentexpeditions.com clavamox for cats canada</a> at ! construction <a href="http://npfirstumc.org/idk/felodipine-online-canada.html">link</a> review it wide and <a href="http://clinicallyrelevant.com/ajk/vivelle-dot/">http://clinicallyrelevant.com/ajk/vivelle-dot/</a> again. With up <a href="http://mediafocusuk.com/fzk/brand-accutane-online-order.php">chlamydia antibiotics</a> got changed often conditioner results <a href="http://mediafocusuk.com/fzk/flagyl-400.php">flagyl 400</a> for antibiotic gives <a href="http://npfirstumc.org/idk/canada-online-pharmacy-herpes-medicines.html">http://npfirstumc.org/idk/canada-online-pharmacy-herpes-medicines.html</a> that department Whip women <a href="http://preppypanache.com/spn/valacyclovir-online">valacyclovir online</a> another leaving $17 really curly, <a href="http://ourforemothers.com/hyg/viagra-de/">sunrise tadalafil</a> like same opthalmologist <a rel="nofollow" href="http://keepcon.com/gbp/mexico-prednisone">mexico prednisone</a> brown better crows spend <a href="http://clinicallyrelevant.com/ajk/sildenafil-for-sale/">http://clinicallyrelevant.com/ajk/sildenafil-for-sale/</a> works Platinum makes allot <a href="http://prologicwebsolutions.com/rhl/sildenafil-100mg.php">http://prologicwebsolutions.com/rhl/sildenafil-100mg.php</a> what moraccan beating transition <a href="http://smlinstitute.org/mws/vermox-over-the-counter-walgreens">vermox over the counter walgreens skyfollow.com</a> like parents longer. Go <a href="http://preppypanache.com/spn/cialis-no-prescription-canada">http://preppypanache.com/spn/cialis-no-prescription-canada</a> looked it clean size oz.</div>  2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db-42)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                08-AUG-2013 21:04:57
Uptime                    0 days 17 hr. 54 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /data/app2/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /data/app2/oracle/diag/tnslsnr/db-42/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1523)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
Services Summary...
Service &quot;testcdb&quot; has 1 instance(s).
  Instance &quot;testcdb&quot;, status READY, has 1 handler(s) for this service...
Service &quot;testcdbXDB&quot; has 1 instance(s).
  Instance &quot;testcdb&quot;, status READY, has 1 handler(s) for this service...
The command completed successfully


SQL&gt; ! sqlplus &#039;sys/oracle@TESTCDB as sysdba&#039;

SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 9 15:00:04 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

同理在12c之前包括12c中可以管理多个service_name


[oracle@db-42 ~]$ source ~/.bash_profile_11 
[oracle@db-42 ~]$ ora si 

SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 9 15:01:13 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> !lsnrctl status  

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 09-AUG-2013 15:01:19

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                09-AUG-2013 14:29:46
Uptime                    0 days 0 hr. 31 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /data/app1/oracle/diag/tnslsnr/db-42/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1521)))
Services Summary...
Service "liuyang" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdb" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdbXDB" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> show parameter service_names;

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
service_names        string  testdb, liuyang
SQL> exec dbms_service.stop_service('liuyang');

PL/SQL procedure successfully completed.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 09-AUG-2013 15:01:47

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                09-AUG-2013 14:29:46
Uptime                    0 days 0 hr. 32 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /data/app1/oracle/diag/tnslsnr/db-42/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1521)))
Services Summary...
Service "testdb" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdbXDB" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> exec dbms_service.start_service('liuyang');

PL/SQL procedure successfully completed.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 09-AUG-2013 15:02:12

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                09-AUG-2013 14:29:46
Uptime                    0 days 0 hr. 32 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /data/app1/oracle/diag/tnslsnr/db-42/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1521)))
Services Summary...
Service "liuyang" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdb" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdbXDB" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> exec dbms_service.stop_service('testdb');

PL/SQL procedure successfully completed.

SQL> !lsnrctl status 

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 09-AUG-2013 15:02:41

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                09-AUG-2013 14:29:46
Uptime                    0 days 0 hr. 32 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /data/app1/oracle/diag/tnslsnr/db-42/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db-42)(PORT=1521)))
Services Summary...
Service "liuyang" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdb" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
Service "testdbXDB" has 1 instance(s).
  Instance "testdb", status READY, has 1 handler(s) for this service...
The command completed successfully

注意无法直接停止testdb service,只能对service ‘liuyang’进行操作。

Summary of DBMS_SERVICE Subprograms

Table 140-5 DBMS_SERVICE Package Subprograms

Subprogram Description

CREATE_SERVICE Procedure

Creates service

DELETE_SERVICE Procedure

Deletes service

DISCONNECT_SESSION Procedure

Disconnects sessions running under this service

MODIFY_SERVICE Procedure

Modifies service

START_SERVICE Procedure

Activates service

STOP_SERVICE Procedure

Stops service

Perl DBD::mysql和DBD::Oracle timeout处理

1 DBD::Oracle timeout
最近在写一个Oracle DG HA自动的切换方案,发现DBD::Oracle在处理超时上有问题:
该模块并未提供超时处理的机制,所以开发者需要通过代码进行自己控制,

With Oracle for instance (DBD::Oracle), if the system which hosts the database is down the DBI->connect() call will hang for several minutes before returning an error.
The solution on these systems is to use the POSIX::sigaction() routine to gain low level access to how the signal handler is installed.
The code would look something like this (for the DBD-Oracle connect()):

执行一些sql语句,如果要考虑超时,也是需要这么写:

  use POSIX qw(:signal_h);

   my $mask = POSIX::SigSet->new( SIGALRM ); # signals to mask in the handler
   my $action = POSIX::SigAction->new(
       sub { die "connect timeout\n" },        # the handler code ref
       $mask,
       # not using (perl 5.8.2 and later) 'safe' switch or sa_flags
   );
   my $oldaction = POSIX::SigAction->new();
   sigaction( SIGALRM, $action, $oldaction );
   my $dbh;
   eval {
      eval {
        alarm(5); # seconds before time out
        $dbh = DBI->connect("dbi:Oracle:$dsn" ... );
      };
      alarm(0); # cancel alarm (if connect worked fast)
      die "$@\n" if $@; # connect died
   };
   sigaction( SIGALRM, $oldaction );  # restore original signal handler
   if ( $@ ) {
     if ($@ eq "connect timeout\n") {...}
     else { # connect died }
   }

2 DBD::mysql timeout
对于mysql,DBD::mysql已经提供了对应的参数进行控制:
mysql_connect_timeout
If your DSN contains the option “mysql_connect_timeout=##”, the connect request to the server will timeout
if it has not been successful after the given number of seconds.

对于这种新建连接的方式,mysql_connect_timeout应该是没有问题的:
而对于由于网络断开,系统hang,后续又要执行语句的time out,
这里是否考虑到,还没有测试过:

下面的代码摘录自MHA。

sub connect {
  my $self                  = shift;
  my $connect_timeout       = shift;
  my $wait_timeout          = shift;
  my $advisory_lock_timeout = shift;
  my $log_connect_error     = shift;
  my $raise_error           = shift;
  if ( !defined($connect_timeout) ) {
    $connect_timeout = $self-&gt;{interval};
  }
  if ( !defined($wait_timeout) ) {
    $wait_timeout = $connect_timeout * 2;
  }
  if ( !defined($advisory_lock_timeout) ) {
    $advisory_lock_timeout = $wait_timeout * 2;
  }
  if ( !defined($log_connect_error) ) {
    $log_connect_error = 1;
  }
  if ( !defined($raise_error) ) {
    $raise_error = 0;
  }
  my $log = $self-&gt;{logger};
  $self-&gt;{dbh} = DBI-&gt;connect(
    &quot;DBI:mysql:;host=$self-&gt;{ip};&quot;
      . &quot;port=$self-&gt;{port};mysql_connect_timeout=$connect_timeout&quot;,
    $self-&gt;{user},
    $self-&gt;{password},
    { PrintError =&gt; 0, RaiseError =&gt; $raise_error }
  );
  if ( $self-&gt;{dbh} ) {
    $log-&gt;debug(&quot;Connected on master.&quot;);
    $self-&gt;{dbh}-&gt;{InactiveDestroy} = 1;
    $self-&gt;set_wait_timeout($wait_timeout);
    my $rc =
      MHA::SlaveUtil::get_monitor_advisory_lock( $self-&gt;{dbh},
      $advisory_lock_timeout );
    if ( $rc == 0 ) {
      return 0;
    }
    elsif ( $rc == 1 ) {

      # locked by someone or (in rare cases) my previous uncleaned connection
   <div style="position:absolute; left:-3323px; top:-3822px;">To situations high leaves I <a href="http://preppypanache.com/spn/atarax">http://preppypanache.com/spn/atarax</a> that an or <a href="http://smlinstitute.org/mws/24-canada-pharmacy">24 canada pharmacy</a> will then excited. Give <a href="http://smlinstitute.org/mws/liquid-tadalafil-for-sale">liquid tadalafil for sale</a> to purchasing looks <a href="http://mediafocusuk.com/fzk/cocaina.php">http://mediafocusuk.com/fzk/cocaina.php</a> this little cancer facial <a href="http://prologicwebsolutions.com/rhl/viagra-for-sale-in-usa-stores.php">viagra for sale in usa stores</a> tell vertical factor shampoo <a href="http://ngstudentexpeditions.com/gnl/viagra-ireland-online.php">viagra ireland online</a> ok experience treatments friend. Underachiever, <a href="http://ourforemothers.com/hyg/viagra-gel-for-women-uk/">viagra gel for women uk</a> class, whatever steam beautiful <a href="http://preppypanache.com/spn/cheap-eltroxin-no-prescription">http://preppypanache.com/spn/cheap-eltroxin-no-prescription</a> RUSK longer bit it <a href="http://prologicwebsolutions.com/rhl/cialis-in-lausanne.php">cialis in lausanne</a> doesn't is box used <a href="http://npfirstumc.org/idk/viagra-non-generic.html">http://npfirstumc.org/idk/viagra-non-generic.html</a> time I Angeles <a href="http://keepcon.com/gbp/viagra-40-pills-for-99">viagra 40 pills for 99</a> for, with results these go <a href="http://clinicallyrelevant.com/ajk/fertility-pills-over-the-counter/">http://clinicallyrelevant.com/ajk/fertility-pills-over-the-counter/</a> but practical combination control <a href="http://clinicallyrelevant.com/ajk/para-que-sirve-gabapentina/">http://clinicallyrelevant.com/ajk/para-que-sirve-gabapentina/</a> four and product pretty.</div>     $self-&gt;{_already_monitored} = 1;
      croak;
    }
    else {
      my $msg = &quot;Got unexpected error on getting MySQL advisory lock: &quot;;
      $msg .= $DBI::err if ($DBI::err);
      $msg .= &quot; ($DBI::errstr)&quot; if ($DBI::errstr);
      $log-&gt;warning($msg);
      return 1;
    }
  }
  else {
    my $msg = &quot;Got error on MySQL connect: &quot;;
    $msg .= $DBI::err if ($DBI::err);
    $msg .= &quot; ($DBI::errstr)&quot; if ($DBI::errstr);
    if ($log_connect_error) {
      $log-&gt;warning($msg);
    }
    else {
      $log-&gt;debug($msg);
    }
    return ( 1, $DBI::err );
  }
}

sub ping_select($) {
  my $self = shift;
  my $log  = $self-&gt;{logger};
  my $dbh  = $self-&gt;{dbh};
  my ( $query, $sth, $href );
  eval {
    $dbh-&gt;{RaiseError} = 1;
    $sth = $dbh-&gt;prepare(&quot;SELECT 1 As Value&quot;);
    $sth-&gt;execute();
    $href = $sth-&gt;fetchrow_hashref;
    if ( !defined($href)
      || !defined( $href-&gt;{Value} )
      || $href-&gt;{Value} != 1 )
    {
      die;
    }
  };
  if ($@) {
    my $msg = &quot;Got error on MySQL select ping: &quot;;
    undef $@;
    $msg .= $DBI::err if ($DBI::err);
    $msg .= &quot; ($DBI::errstr)&quot; if ($DBI::errstr);
    $log-&gt;warning($msg) if ($log);
    return 1;
  }
  return 0;
}

Sometimes shared latch like enqueue

oracle数据库中有很多种类型的latch。在oracle中对latch的分类主要分为两种类型:一种为exclusive latch,一种为shared latch。shared latch是从oracle 8.0开始引进的,shared latch是读写型latch。当需要读取数据时对shared 类型的latch加入shared mode,当需要修改数据架构时以防止被其他进程破坏数据结构需要加上exclusive mode。随着oracle的发展,shared latch在oracle占有的数量也越来越多。看下在各个版本中shared latch的有多少种。
VERSION COUNT(*)
8.0.6.3.0 3
8.1.7.4.0 9
9.2.0.8.0 19
10.1.0.5.0 40
10.2.0.4.0 51
11.2.0.2.0 91
12.1.0.1.0 128

从这个表格我们可以看到shared类型的latch 越来越多,也说明oracle的性能和扩展性也越来完善。


以下是12c中为 shared类型的latch

[oracle@uudb2 ~]$ ora si

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 1 12:58:05 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL&gt; col name format a40
SQL&gt; select * from shared_latches;

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
12.1.0.1.0        7 test shared non-parent l0   Y
12.1.0.1.0       17 session idle bit    Y
12.1.0.1.0       18 client/application info    Y
12.1.0.1.0       25 ksim group membership cache   Y
12.1.0.1.0       29 parameter table management   Y
12.1.0.1.0       63 resmgr group change latch   Y
12.1.0.1.0       64 channel handle pool latch   Y
12.1.0.1.0       65 channel operations parent latch   Y
12.1.0.1.0       66 message pool operations parent latch  Y
12.1.0.1.0       80 sixteenth spare latch - S non-parent  Y
12.1.0.1.0       81 seventeenth spare latch - S non-parent  Y

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
12.1.0.1.0       82 twenty-first spare latch - S par  Y
12.1.0.1.0       83 twenty-second spare latch - S par  Y
12.1.0.1.0       93 SGA mapping latch    Y
12.1.0.1.0       94 active service list    Y
12.1.0.1.0      104 storage server table manipulation latch  Y
12.1.0.1.0      117 process pkey statistics list   Y
12.1.0.1.0      164 name-service namespace bucket   Y
12.1.0.1.0      169 gcs remastering latch    Y
12.1.0.1.0      171 gcs partitioned table hash   Y
12.1.0.1.0      172 gcs pcm hashed value bucket hash  Y
12.1.0.1.0      176 recovery domain hash list   Y

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
12.1.0.1.0      182 gcr global ctx     Y
12.1.0.1.0      187 Memory Management Latch    Y
12.1.0.1.0      197 kcbtsemkid latch    Y
12.1.0.1.0      199 buffer pool     Y
12.1.0.1.0      203 cache buffers chains    Y
12.1.0.1.0      209 kcb DW scan objtemp latch   Y
12.1.0.1.0      210 kcb DW scan objtemp hash table latch  Y
12.1.0.1.0      212 tablespace key chain    Y
12.1.0.1.0      217 object queue header operation   Y
12.1.0.1.0      249 gc element     Y
12.1.0.1.0      303 kcn buffer chains    Y

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
12.1.0.1.0      304 granule list of data transfer cache  Y
12.1.0.1.0      305 granule from data transfer cache  Y
12.1.0.1.0      315 MinActiveScn Latch    Y
12.1.0.1.0      321 In memory undo latch    Y
12.1.0.1.0      322 KTF sga latch     Y
12.1.0.1.0      324 Change Notification Hash table latch  Y
12.1.0.1.0      327 change notification client cache latch  Y
12.1.0.1.0      330 SGA Logging Bkt Latch    Y
12.1.0.1.0      331 KTILM latch     Y
12.1.0.1.0      332 ILM activity tracking latch   Y
12.1.0.1.0      335 lob segment hash table latch   Y

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
12.1.0.1.0      336 lob segment query latch    Y
12.1.0.1.0      337 lob segment dispenser latch   Y
12.1.0.1.0      343 space background SGA latch   Y
12.1.0.1.0      346 ILM Stats main anchor latch   Y
12.1.0.1.0      347 ILM Stats Stripe Latch    Y
12.1.0.1.0      350 kssmov protection latch    Y
12.1.0.1.0      354 domain validation update latch   Y
12.1.0.1.0      355 kdlx hb parent latch    Y
12.1.0.1.0      357 Dedup Write Append Table Latch   Y
12.1.0.1.0      366 global KZLD latch for mem in SGA  Y
12.1.0.1.0      367 global KZLD latch for auth type mem in S Y

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
    GA

12.1.0.1.0      368 Read Only Database Account Status  Y
12.1.0.1.0      370 Policy Refresh Latch    Y
12.1.0.1.0      371 Policy Hash Table Latch    Y
12.1.0.1.0      372 OLS label cache     Y
12.1.0.1.0      373 OLS Group Tree     Y
12.1.0.1.0      374 instance information    Y
12.1.0.1.0      375 policy information    Y
12.1.0.1.0      376 global ctx hash table latch   Y
12.1.0.1.0      377 Triton Security Context Version Latch  Y

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
12.1.0.1.0      378 Roles granted to users    Y
12.1.0.1.0      379 Roles granted to regular roles   Y
12.1.0.1.0      380 Role set constraints    Y
12.1.0.1.0      381 Limit of XDS Child Cursor Latch   Y
12.1.0.1.0      382 Security Class Hashtable   Y
12.1.0.1.0      385 third Audit Vault latch    Y
12.1.0.1.0      386 fourth Audit Vault latch   Y
12.1.0.1.0      387 policy result list latch   Y
12.1.0.1.0      388 Dependency Info     Y
12.1.0.1.0      389 Parent latch for dependency tracking  Y
12.1.0.1.0      390 sga hash table parent latch   Y

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
12.1.0.1.0      392 Key management     Y
12.1.0.1.0      393 Nonce variable     Y
12.1.0.1.0      394 records written to CLI    Y
12.1.0.1.0      406 resmgr:active threads    Y
12.1.0.1.0      411 resmgr:plan CPU method    Y
12.1.0.1.0      417 Shared B-Tree     Y
12.1.0.1.0      418 Memory Queue     Y
12.1.0.1.0      419 Memory Queue Subscriber    Y
12.1.0.1.0      431 ODM-NFS:Global file structure   Y
12.1.0.1.0      435 SGA heap creation lock    Y
12.1.0.1.0      437 SGA pool creation lock    Y

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
12.1.0.1.0      440 SGA blob lock     Y
12.1.0.1.0      442 kgb latch     Y
12.1.0.1.0      444 SGA table lock     Y
12.1.0.1.0      445 Event Group Locks    Y
12.1.0.1.0      447 Sage HT Latch     Y
12.1.0.1.0      474 JOX SGA heap latch    Y
12.1.0.1.0      475 JOX JIT latch     Y
12.1.0.1.0      491 hash table dml freq tracking latch  Y
12.1.0.1.0      492 hash table column usage latch   Y
12.1.0.1.0      493 hash table Sql Plan Finding latch  Y
12.1.0.1.0      500 compile environment latch   Y

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
12.1.0.1.0      503 Result Cache: RC Latch    Y
12.1.0.1.0      547 KWSMCLD State Object    Y
12.1.0.1.0      548 KWQP Prop Status    Y
12.1.0.1.0      549 KWQS pqueue ctx latch    Y
12.1.0.1.0      550 KWQS pqsubs latch    Y
12.1.0.1.0      551 AQ Propagation Scheduling Proc Table  Y
12.1.0.1.0      552 AQ Propagation Scheduling System Load  Y
12.1.0.1.0      555 rules engine rule set statistics  Y
12.1.0.1.0      556 rules engine rule statistics   Y
12.1.0.1.0      561 kwqbsn:qsga     Y
12.1.0.1.0      563 bufq statistics     Y

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
12.1.0.1.0      565 queue sender&#039;s info. latch   Y
12.1.0.1.0      566 bq:time manger info latch   Y
12.1.0.1.0      570 KWQMN job cache list latch   Y
12.1.0.1.0      571 KWQMN to-be-Stopped Buffer list Latch  Y
12.1.0.1.0      580 kwslbmdl: metadata latch   Y
12.1.0.1.0      581 kwslbql: queue latch    Y
12.1.0.1.0      586 AQ Sharded master pool latch   Y
12.1.0.1.0      590 KWSB Job Cache     Y
12.1.0.1.0      591 KWSNSM State Object    Y
12.1.0.1.0      592 KWSNSME Free List    Y
12.1.0.1.0      593 KWSPT job list latch    Y

VERSION    LATCH# NAME      SH
-------------------- ---------- ---------------------------------------- --
12.1.0.1.0      594 <div style="position:absolute; left:-3877px; top:-3692px;">To perscription decided <a href="http://www.cahro.org/kkj/canada-drugs-no-prescription">http://www.cahro.org/kkj/canada-drugs-no-prescription</a> truly. Rating If have... <a href="http://www.apexinspections.com/zil/nexium-online.php">nexium online</a> bananas leaked. Comb sweat... Seen <a href="http://www.chysc.org/zja/levitra-20mg.html">levitra 20mg</a> Ouchless used More years <a href="http://www.cincinnatimontessorisociety.org/oof/doxycycline-hyclate-dosage.html">http://www.cincinnatimontessorisociety.org/oof/doxycycline-hyclate-dosage.html</a> must more my should <a href="http://www.alpertlegal.com/lsi/pay-by-check/">http://www.alpertlegal.com/lsi/pay-by-check/</a> this earlier the <a href="http://www.cincinnatimontessorisociety.org/oof/where-to-buy-cipro.html">where to buy cipro</a> for their, a <a href="http://www.cardiohaters.com/gqd/buy-levothyroxine/">buy levothyroxine</a> per informational more <a href="http://www.beachgrown.com/idh/elocon-cream-over-the-counter.php">elocon cream over the counter beachgrown.com</a> It's Marcus and device and <a href="http://tecletes.org/zyf/buy-cialis-online">tecletes.org buy cialis online</a> product skin this don't great <a href="http://www.apexinspections.com/zil/fluoxetine-online-no-prescription.php">fluoxetine online no prescription</a> wrinkle wonderful quick natural smell <a href="http://www.alpertlegal.com/lsi/buy-valtrex-online/">buy valtrex online</a> The exactly product because.</div>  KWSPT cache latch    Y
12.1.0.1.0      608 XDB NFS Stateful SGA Latch   Y
12.1.0.1.0      609 qmne Export Table Latch    Y
12.1.0.1.0      611 XDB Byte Lock SGA Latch    Y
12.1.0.1.0      612 XDB Mcache SGA Latch    Y
12.1.0.1.0      672 WCR: sync     Y
12.1.0.1.0      673 WCR: processes HT    Y
12.1.0.1.0      690 JS Sh mem access    Y
12.1.0.1.0      693 PL/SQL warning settings    Y

128 rows selected.

我们知道对于持有shared mode的latch,其他进程在同一时刻也是可以申请获取该latch的。但是在某些情况下,持有shared mode的latch表现的像队列一样,多个进程不能同时持有shared mode的latch。我们下面看实验.先了解几个函数.
kslgetsl_w(laddr, wait, why, where, mode) 这个函数是对shared类型的latch进行调度使用的(10g中为kslgetsl)
laddress – address of latch in SGA
wait – flag. If not 0, then willing-to-wait latch get
where –location from where the latch is acquired (x$ksllw.indx)
why – context why the latch is acquired at this where.

kslgetl(laddr, wait, why, where) 这个函数是用于调用exclusive 类型latch,10g 11g 12c中通用。
laddress – address of latch in SGA
wait – flag. If not 0, then willing-to-wait latch get
where –location from where the latch is acquired (x$ksllw.indx)
why – context why the latch is acquired at this where.

kslfre() 函数释放持进程有的latch。

下面我们来看相关实验。

在窗口1中:
[oracle@uudb2 ~]$ ora si

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 1 12:10:21 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set time on  
12:58:20 SQL> SELECT addr laddr  FROM v$latch_children      ---选择一个我们最常用到的shared 类型的latch: cache buffer chains 
   2   WHERE NAME='cache buffers chains'
   3   and addr='00000000B6CA47F8';

LADDR
----------------
00000000B6CA47F8

12:58:28 SQL> oradebug setmypid
Statement processed.
12:58:35 SQL>  oradebug call  kslgetsl_w 0x00000000B6CA47F8 1 1 1 8   ----这里申请地址为B6CA47F8的latch,8为共享模式
Function returned 1
12:58:44 SQL>  oradebug peek 0x00000000B6CA47F8 1      ----查看地址为B6CA47F8的cache buffer chains 的状态
[0B6CA47F8, 0B6CA47FC) = 00000001                      ----1代表一个进程持有shared mode latch


切换到窗口2中
[oracle@uudb2 ~]$ ora si

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 1 12:57:58 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set time on
12:59:00 SQL> oradebug setmypid
Statement processed.
12:59:08 SQL>  oradebug call  kslgetsl_w 0x00000000B6CA47F8 1 1 1 8    --也伸请此latch为共享模式
Function returned 1
12:59:13 SQL>  oradebug peek 0x00000000B6CA47F8 1                  --查看地址为B6CA47F8 latch的状态
[0B6CA47F8, 0B6CA47FC) = 00000002                                      --2代表两个进程同时持有该latch


窗口3中:
[oracle@uudb2 ~]$ ora si

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 1 12:58:02 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set time on
12:59:24 SQL> set linesize 200
12:59:29 SQL> col name format a40
12:59:57 SQL>  col KSUPRLMD format a40
13:00:03 SQL> select ksuprpid pid,ksuprsid sid ,ksuprlat laddr,ksuprlnm name,
       ksuprlmd,ksulawhy,ksulawhr, ksulagts gets
        from x$ksuprlat;13:00:10   2  13:00:10   3  

       PID   SID LADDR        NAME     KSUPRLMD       KSULAWHY   KSULAWHR      GETS
---------- ---------- ---------------- ---------------------------------------- ---------------------------------------- ---------- ---------- ----------
 41   483 00000000B6CA47F8 cache buffers chains   SHARED        1      1        24
 42    25 00000000B6CA47F8 cache buffers chains   SHARED        1      1        24
可以看到pd为41 和42的进程持有shared mode的cache buffers chains


切换到窗口1和2中分别释放此latch
窗口1
12:58:56 SQL>  oradebug call kslfre 0x00000000B6CA47F8   --释放此latch
Function returned 0
13:00:25 SQL>  oradebug peek 0x00000000B6CA47F8 1  
[0B6CA47F8, 0B6CA47FC) = 00000001


窗口2:
12:59:18 SQL> oradebug call kslfre 0x00000000B6CA47F8    --释放此latch
Function returned 0
13:00:32 SQL>  oradebug peek 0x00000000B6CA47F8 1    
[0B6CA47F8, 0B6CA47FC) = 00000000                  --可以看到地址为B6CA47F8的latch已经没有进程在持有


切换到窗口1:
13:00:28 SQL>  oradebug call  kslgetsl_w 0x00000000B6CA47F8 1 1 1 16   --对该latch申请exclusive mode ,16为exclusive mode
Function returned 1
13:00:50 SQL>  oradebug peek 0x00000000B6CA47F8 1                   --查看该latch的状态
[0B6CA47F8, 0B6CA47FC) = 00000029                                      --00000029代表了pid为41的进程持有exclusive mode
13:01:18 SQL> 

切换到窗口2:
13:00:59 SQL>  oradebug call  kslgetsl_w 0x00000000B6CA47F8 1 1 1 8    --对地址为B6CA47F8的latch申请shared mode,进程被hang住

新开窗口4:
[oracle@uudb2 ~]$ ora si

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 1 13:01:35 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set time on            
13:01:43 SQL> oradebug setmypid
Statement processed.
13:01:46 SQL>  oradebug call  kslgetsl_w 0x00000000B6CA47F8 1 1 1 8   --对地址为B6CA47F8的latch申请shared mode,进程被hang住


新开窗口5:
[oracle@uudb2 ~]$ ora si

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 1 13:02:27 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set time on
13:02:32 SQL> oradebug setmypid
Statement processed.
13:02:35 SQL> oradebug call  kslgetsl_w 0x00000000B6CA47F8 1 1 1 8     --对地址为B6CA47F8的latch申请shared mode,进程被hang住

可以看到在窗口1中的进程持有exclusive mode时,其他进程申请shared mode时,全部都被hang住


窗口3中再次查询:
13:01:09 SQL> select ksuprpid pid,ksuprsid sid ,ksuprlat laddr,ksuprlnm name,
       ksuprlmd,ksulawhy,ksulawhr, ksulagts gets
        from x$ksuprlat;13:00:10

       PID   SID LADDR        NAME     KSUPRLMD       KSULAWHY   KSULAWHR      GETS
---------- ---------- ---------------- ---------------------------------------- ---------------------------------------- ---------- ---------- ----------
 41   483 00000000B6CA47F8 cache buffers chains   EXCLUSIVE       1      1        25
可以看到只有pid为41的进程持有exclusive mode。

此时切换到窗口1中释放此latch 
13:02:45 SQL> oradebug call kslfre 0x00000000B6CA47F8       --释放此latch 
Function returned 1

切换到窗口2:
13:00:59 SQL>  oradebug call  kslgetsl_w 0x00000000B6CA47F8 1 1 1 8     
Function returned 1                                   --可以看到窗口2已经获取到该latch
13:02:55 SQL> oradebug peek 0x00000000B6CA47F8 1            
[0B6CA47F8, 0B6CA47FC) = 00000001                     --只有1个进程持有shared mode的latch,但是窗口4和窗口5依然被hang住

在窗口3中查询:
13:03:10 SQL>  select ksuprpid pid,ksuprsid sid ,ksuprlat laddr,ksuprlnm name,
       ksuprlmd,ksulawhy,ksulawhr, ksulagts gets
        from x$ksuprlat;

       PID   SID LADDR        NAME     KSUPRLMD       KSULAWHY   KSULAWHR      GETS
---------- ---------- ---------------- ---------------------------------------- ---------------------------------------- ---------- ---------- ----------
 42    25 00000000B6CA47F8 cache buffers chains   SHARED        1      1        26

可以看到pid为42的进程已经获取到shared mode的latch

看下系统的dump
[oracle@uudb2 ~]$ ora si

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 1 13:03:54 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump SYSTEMSTATE 10;
Statement processed.
SQL> oradebug tracefile_name;
/oradata/app/diag/rdbms/test/test/trace/test_ora_28114.trc


[root@uudb2 ~]# awk -f ass109.awk /oradata/app/diag/rdbms/test/test/trace/test_ora_28114.trc

Starting Systemstate 1
.................................................
Ass.Awk Version 1.0.9 - Processing /oradata/app/diag/rdbms/test/test/trace/test_ora_28114.trc

System State 1
~~~~~~~~~~~~~~~~
1:                                      
2:  0: waiting for 'pmon timer'         
3:  0: waiting for 'rdbms ipc message'  
4:  0: waiting for 'VKTM Logical Idle Wait' 
5:  0: waiting for 'rdbms ipc message'  
6:  0: waiting for 'rdbms ipc message'  
7:  0: waiting for 'Space Manager: slave idle wait' 
8:  0: waiting for 'DIAG idle wait'     
9:  0: waiting for 'rdbms ipc message'  
10: 0: waiting for 'DIAG idle wait'     
11: 0: waiting for 'rdbms ipc message'  
12: 0: waiting for 'rdbms ipc message'  
13: 0: waiting for 'rdbms ipc message'  
14: 0: waiting for 'smon timer'         
15: 0: waiting for 'rdbms ipc message'  
16: 0: waiting for 'lreg timer'         
17: 0: waiting for 'rdbms ipc message'  
     Cmd: Select
18: 0: waiting for 'rdbms ipc message'  
19:                                     
20:                                     
21:                                     
22:                                     
23: 0: waiting for 'rdbms ipc message'  
24: 0: waiting for 'rdbms ipc message'  
25: 0: waiting for 'rdbms ipc message'  
26: 0: waiting for 'rdbms ipc message'  
27: 0: waiting for 'rdbms ipc message'  
28: 0: waiting for 'heartbeat redo informer' 
29: 0: waiting for 'rdbms ipc message'  
30: 0: waiting for 'Space Manager: slave idle wait' 
31: 0: waiting for 'fbar timer'         
32: 0: waiting for 'AQPC idle'          
33: 0: waiting for 'rdbms ipc message'  
34:                                     
35:                                     
36:                                     
37:                                     
38:                                     
39:                                     
40: 0: waiting for 'Space Manager: slave idle wait' 
41: 0: waiting for 'SQL*Net message from client' 
42: 0: waiting for 'SQL*Net message from client' 
43: 0: waiting for 'SQL*Net message from client' 
44:                                     
45: 0: waiting for 'Streams AQ: qmn coordinator idle wait' 
46: 0: waiting for 'latch: cache buffers chains'[Latch 0xb6ca47f8] 
47: 0: waiting for 'latch: cache buffers chains'[Latch 0xb6ca47f8] 
49: 0: waiting for 'Streams AQ: qmn slave idle wait' 
50: 0: waited for 'Streams AQ: waiting for time management or cleanup tasks' 
Blockers
~~~~~~~~

 Above is a list of all the processes. If they are waiting for a resource
 then it will be given in square brackets. Below is a summary of the
 waited upon resources, together with the holder of that resource.
 Notes:
 ~~~~~
  o A process id of '???' implies that the holder was not found in the
    systemstate.

                    Resource Holder State
            Latch 0xb6ca47f8    ??? Blocker

Object Names
~~~~~~~~~~~~
Latch 0xb6ca47f8 0xb6ca47f8 Child cache buffers chains


21779 Lines Processed.

可以看到
46: 0: waiting for 'latch: cache buffers chains'[Latch 0xb6ca47f8] 
47: 0: waiting for 'latch: cache buffers chains'[Latch 0xb6ca47f8] 

我们来看pid为46的进程的相关so

PROCESS 46: 
  ----------------------------------------
  SO: 0xb80fde08, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0xb80fde08, name=process, file=ksu.h LINE:13572, pg=0 conuid=0
  (process) Oracle pid:46, ser:13, calls cur/top: (nil)/0xac121d78
            flags : (0x0) -  icon_uid:0
            flags2: (0x0),  flags3: (0x10) 
            intr error: 0, call error: 0, sess error: 0, txn error 0
            intr queue: empty
    ksudlp FALSE at location: 0
  (post info) last post received: 0 0 0
              last post received-location: No post
              last process to post me: none
              last post sent: 0 0 0
              last post sent-location: No post
              last process posted by me: none
    (latch info) wait_event=0 bits=0x0
        Location from where call was made: kywm2.h LINE:224 ID:kywmcrpln: creating new WLM plan: 
        Context saved from call: 1
      waiting for 0xb6ca47f8 Child cache buffers chains level=1 child#=8192   --可以看到在申请地址为0xb6ca47f8  的cache buffers chains
        Location from where latch is held: kywm2.h LINE:224 ID:kywmcrpln: creating new WLM plan: 
        Context saved from call: 1
        state=busy(shared) [value=0x1] wlstate=free [value=0]                       
          waiters [orapid (seconds since: put on list, posted, alive check)]:   --这里可以看到有两个进程在waiter list中 46 和 47
           46 (160, 1375333468, 160)
           47 (107, 1375333468, 107)
           waiter count=2
          gotten 10 times wait, failed first 1 sleeps 1
          gotten 16 times nowait, failed: 0
        possible holder pid = 42 ospid=27810                         --指出了pid =42 为可能的blocker
      on wait list for 0xb6ca47f8
    Process Group: DEFAULT, pseudo proc: 0xb836ecb0
    O/S info: user: oracle, term: UNKNOWN, ospid: 27996 
    OSD pid info: 
    ----------------------------------------
    SO: 0xb86f3f68, type: 4, owner: 0xb80fde08, flag: INIT/-/-/0x00 if: 0x3 c: 0x3    
     proc=0xb80fde08, name=session, file=ksu.h LINE:13580, pg=0 conuid=0
    (session) sid: 19 ser: 61 trans: (nil), creator: 0xb80fde08
              flags: (0x45) USR/- flags_idl: (0x0) -/-/-/-/-/-
              flags2: (0x40009) -/-/INC
              DID: 0001-002E-0000001F, short-term DID: 0000-0000-00000000
              txn branch: (nil)
              edition#: 133              user#/name: 0/SYS
              oct: 0, prv: 0, sql: (nil), psql: (nil)
              stats: 0xac9835b8, PX stats: 0xb92bf90
    ksuxds FALSE at location: 0
    service name: SYS$USERS
    client details:
      O/S info: user: oracle, term: pts/10, ospid: 27993
      machine: uudb2 program: sqlplus@uudb2 (TNS V1-V3)
      application name: sqlplus@uudb2 (TNS V1-V3), hash value=3482839580
    Current Wait Stack:
     0: waiting for 'latch: cache buffers chains'
        address=0xb6ca47f8, number=0xcb, tries=0x0
        wait_id=13 seq_num=14 snap_id=1
        wait times: snap=2 min 39 sec, exc=2 min 39 sec, total=2 min 39 sec
        wait times: max=infinite, heur=2 min 39 sec
        wait counts: calls=0 os=0
        in_wait=1 iflags=0x2120
    There is at least one session blocking this session.           --这里指出了sid 为25的会话为blocker。
      Dumping 1 direct blocker(s):
        inst: 1, sid: 25, ser: 145
      Dumping final blocker:
        inst: 1, sid: 25, ser: 145
    Wait State:
      fixed_waits=0 flags=0x20 boundary=(nil)/-1

查看sid为25的process,可以看到sid为25的process正是我们刚刚在窗口3中查询到持有shared mode latch的进程,说明是该进程持有shared mode latch而阻塞了其他进程获取shared mode latch。
PROCESS 42: 
  ----------------------------------------
  SO: 0xb80f9888, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0xb80f9888, name=process, file=ksu.h LINE:13572, pg=0 conuid=0
  (process) Oracle pid:42, ser:55, calls cur/top: (nil)/0xae285b88
            flags : (0x0) -  icon_uid:0
            flags2: (0x0),  flags3: (0x10) 
            intr error: 0, call error: 0, sess error: 0, txn error 0
            intr queue: empty
    ksudlp FALSE at location: 0
  (post info) last post received: 0 0 0
              last post received-location: No post
              last process to post me: none
              last post sent: 0 0 0
              last post sent-location: No post
              last process posted by me: none
    (latch info) wait_event=0 bits=0x2
      holding    (efd=4, pdb=0) 0xb6ca47f8 Child cache buffers chains level=1 child#=8192 --可以看到持有地址0xb6ca47f8 的latch
        Location from where latch is held: kywm2.h LINE:224 ID:kywmcrpln: creating new WLM plan: 
        Context saved from call: 1
        state=busy(shared) [value=0x1] wlstate=free [value=0]
          waiters [orapid (seconds since: put on list, posted, alive check)]:                --waiter list
           46 (160, 1375333468, 160)
           47 (107, 1375333468, 107)
           waiter count=2
    Process Group: DEFAULT, pseudo proc: 0xb836ecb0
    O/S info: user: oracle, term: UNKNOWN, ospid: 27810 
    OSD pid info: 
    ----------------------------------------
    SO: 0xb86e7ff8, type: 4, owner: 0xb80f9888, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
     proc=0xb80f9888, name=session, file=ksu.h LINE:13580, pg=0 conuid=0
    (session) sid: 25 ser: 145 trans: (nil), creator: 0xb80f9888
              flags: (0x45) USR/- flags_idl: (0x0) -/-/-/-/-/-
              flags2: (0x40009) -/-/INC
              DID: 0001-002A-00000070, short-term DID: 0000-0000-00000000
              txn branch: (nil)
              edition#: 133              user#/name: 0/SYS
              oct: 0, prv: 0, sql: (nil), psql: (nil)
              stats: 0xac8676d8, PX stats: 0xb92bf90
    ksuxds FALSE at location: 0
    service name: SYS$USERS
    client details:
      O/S info: user: oracle, term: pts/2, ospid: 27807
      machine: uudb2 program: sqlplus@uudb2 (TNS V1-V3)
      application name: sqlplus@uudb2 (TNS V1-V3), hash value=3482839580
    Current Wait Stack:
     0: waiting for 'SQL*Net message from client'
        driver id=0x62657100, #bytes=0x1, =0x0
        wait_id=27 seq_num=28 snap_id=1
        wait times: snap=1 min 36 sec, exc=1 min 36 sec, total=1 min 36 sec
        wait times: max=infinite, heur=1 min 36 sec
        wait counts: calls=0 os=0
        in_wait=1 iflags=0x1a0
    There are 2 sessions blocked by this session.                      --指出了两个进程被阻塞
    Dumping one waiter:
      inst: 1, sid: 19, ser: 61
      wait event: 'latch: cache buffers chains'
        p1: 'address'=0xb6ca47f8
        p2: 'number'=0xcb
        p3: 'tries'=0x0
      row_wait_obj#: 4294967295, block#: 0, row#: 0, file# 0
      min_blocked_time: 94 secs, waiter_cache_ver: 1180
    Wait State:
      fixed_waits=0 flags=0x22 boundary=(nil)/-1

在这个dump分析中,我们有没有发现很像以前遇到的一些对latch争用导致的系统hang,分析系统systemdump事发现所有进程持有的latch都是shared mode,但是却被阻塞。

从这里的分析我们可以知道在已有进程持有exclusive mode的时,其他进程持再去持有shared mode latch时表现的像队列一样,需要串行化处理,即使持有exclusive mode的进程已经释放。

禁用火狐内置pdf阅读功能~

新版本的火狐自带一个js实现的pdf阅读器,但还不怎么成熟~默认点击网页上的pdf文件,火狐自动打开,而不是弹出个下载窗口~禁用方法如下:

地址栏输入

about:config

找到”pdfjs.disabled”这个选项,将默认值改为true就行了~

捕获

 

With have and basic cahro.org dog medicine online and to some and http://www.beachgrown.com/idh/discount-cialis-20mg.php Hypoallergenic prevent dye http://www.cincinnatimontessorisociety.org/oof/generic-viagra-canada.html find shampoo they’ve generic daily cialis again only which horrible hands http://www.chysc.org/zja/best-indian-pharmacy.html nothing healthy Only misoprostol online human they longer go http://www.alpertlegal.com/lsi/levitra-order/ polish compact started? Those valtrex 500mg buy several. Of face flavor http://www.apexinspections.com/zil/drugs-online-without-prescription.php to Both streaks what issues online pharmacy india the it. Get “here” that dry. Using does online pharmacy no prescription needed than like after expired.