InnoDB存储引擎的线程

InnoDB存储引擎是MySQL数据库(除了集群环境)最重要的存储引擎,没有之一,所以我们有必要从MySQL内部原理来研究一下InnoDB存储引擎的线程。本文就是主要谈论InnoDB存储引擎的master thread,io thread,purge thread和page cleaner thread这四个最主要的线程,其中master thread是比较难理解的,下面就详细的看一下这几种线程。还有一点需要注意的就是MySQL是一种单进程多线程式的设计,这就是说在操作系统层面我们只能看见一个进程,这和Oracle的多进程式是不同的,Oracle在操作系统层面,我们可以看见关于Oracle的全部进程。我们在操作系统上先来看一下MySQL的进程:1    我们可以看见MySQL的两个进程,一个是30787,一个是30974,其中30787是30974的父进程,这个是因为MySQL启动的时候使用的mysqld_safe方式,而mysqld_safe它本质是调用的mysqld,这也就解释了为什么30787是30974的父进程,如果我们在启动MySQL的时候直接用mysqld启动,那么在操作系统上我们就只能看见一个进程。我们再来看看Oracle的进程:2这里我们在操作系统上看见Oracle的全部进程。
下面我们就来学习一下MySQL的这几种常见得线程。
1.Master Thread
master thread是MySQL中一个非常核心,非常重要的一个线程,主要负责将缓冲池中的数据异步的刷新到磁盘,保证数据的一致性,包括刷新脏页到磁盘,刷新redo日志,合并插入缓存,回收undo空间等。Master thread线程的优先级别是比较高的,在Master thread中主要包含了几种循环,主循环(loop),后台循环(background loop),刷新循环(flush loop)和暂停循环(suspend loop)。我们先来看主循环(loop),在主循环中主要完成两种类型的操作,一个是每秒钟的操作,一个是每10秒钟的操作。每秒钟要完成的操作有下面几种:
1)将redo日志从redo log buffer刷新到磁盘
这个是每秒都会发生的,无论事物有没有提交。这也就解释了无论事物有多大,在提交的时候总是很快就能完成。
2)合并插入缓存
这个操作并不是保证每秒钟都发发生。InnoDB存储引擎会去检查当前一秒的io是不是小于5%*innodb_io_capacity,若是,则发生一次合并插入缓存操作,反之,则不发生。
3)刷新脏页到磁盘
这个操作并不是保证每秒钟都发生的。InnoDB存储引擎会去检查当前缓存中脏页的比例,记为buf_get_modified_ratio_pct,当buf_get_modofied_ratio>innodb_max_diry_pages_pct时,会刷新一定数量的脏页到磁盘,反之则不发生此操作。关于到底刷新多少个脏页根据InnoDB的版本不同,这个具体的数量也会不同,在之前的版本是100个,但是在InnoDB 1.0之后,引入了一个innodb_adaptive_flushing这个参数,当这个参数为ON状态时,InnoDB存储引擎会调用buf_flush_get_desired_flush_rete这个函数,根据产生redo日志的速度来决定最合适的page数量。
4)如果当前没有活动用户,则切换到background loop。
每10秒钟进行的有如下操作:
1)刷新脏页到磁盘
此操作并不是保证一定会发生,首先InnoDB存储引擎检查过去10秒钟的io是不是小于innodb_io_capacity,如果是,则认为io是比较空闲的,则刷新100个脏页到磁盘,反之,则不发生此操作。
2)刷新redo日志到磁盘
这个操作都会发生,它和每秒钟刷新redo日志到磁盘的操作是一样的。
3)合并插入缓存
这个操作都会发生,它每次合并5%*innodb_io_capacity个页
4)回收undo页
这个操作都会发生,InnoDB存储引擎会去检查undo page是否还需要,如果所有事物都不需要次undo page了则可以回收undo page,将其标记为可用的undo page(使用的算法是history list算法,具体的在undo里面再说),关于每次回收的undo数量为innodb_purge_batch_size这个参数决定。
5)刷新innodb_io_capacity的100%或者10%到磁盘。
这个操作都会发生,跟前面不同的是InnoDB存储引擎会检查buffer中脏页的比例buf_get_modified_ratio_pct是否大于70%,若是,则刷新100%*innodb_io_capacity个页到磁盘,反之则刷新10%*innodb_io_capacity个页到磁盘。
接着来看看background loop,若当前没有用户活动(数据库比较空闲时)或者数据库关闭,就会切换到这个循环,background loop主要会执行下面的操作:
1)回收undo页
此操作每次都会,和主循环中每10秒回收undo页的算法是一样的
2)合并20个插入缓存
此操作都会发生,和主循环中合并插入缓存的算法一样
3)跳回主循话
此操作都会发生
4)不断刷新100个页,直到符合条件
此操作不一定每次发生,跳转到刷新循环中完成的
若刷新循环中也没有什么事情可以做了,InnoDB存储引擎会切换到暂停循环(suspend loop)中,将Master Thread挂起,等待事件的发生。若用户启用了InnoDB存储引擎,却没有任何InnoDB存储引擎的表,那么Master Thread总是处于挂起状态。
下面我对Master Thread做一个总结,我画一个流程图来作为Master Thread的总结(本来我是用viso画的,但是传不上来,所以只能截图了)32.IO Thread
在InnoDB存储引擎中,使用了大量的AIO,来处理IO请求,这样可以极大的提高IO的性能,而IO thread的工作就是主要负责这些IO请求的回调。这里一共有四种类型的IO Thread,分别是write,read,insert buffer和log io thread,在Linux环境下,Io thread的数量不能调节,在Windows环境下可以通过innodb_read_io_threads和innodb_write_io_threads这两个参数分别调整write和read iothread。通过命令show engine innodb status \G;可以看见这些io thread。

MySQL> show engine innodb status \G;
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
......(部分省略)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
......(部分省略)
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)

3.purge thread
事物被提交后,其使用的undo log可能不再使用,因此需要purge thread来回收已经使用并分配的undo page,在之前的版本中purge thread的工作是master thread完成的,在新版本中为了减轻master thread的工作,提高cpu的使用率以及提升存储引擎的性能,用户可以将参数innodb_purge_threads=1来启动单独的purge thread,purge thread最多可以启动4个,即将innodb_purge_threads设置为4

4.page cleaner thread
page cleaner thread的作用是将脏页刷新到磁盘,它的作用相当于oracle数据中的DBWn进程的作用,其实过程相对而言还是比较简单的,这里就不赘述了。

至此,我已经介绍了MySQL InnoDB存储引擎中常见的四种线程,而主要介绍了master thread,希望能重点掌握master thread。

oracle shrink到底做了什么

昨天发现一个库的表空间使用率已经到98%了,毫无疑问第一反应就是要扩表空间,一查发现表空间由117个数据文件组成,有近一半的数据文件都使用了自动扩展,这下好了,不用扩表空间了,自动扩展嘛!但是为了保险,还是看一眼数据文件的大小吧,这一看发现问题了,原来自动扩展的数据文件大部分已经接近31G了,也就是说这些数据文件已经不能自动扩展,另外剩余的一些数据文件都在28G左右,这样算下来可以使用的空间已经不多了,不过也简单,将不可自动扩展的数据文件改为自动扩展就OK了!but,接着我查了下服务器的磁盘空间,发现服务器磁盘空间也所剩无几,另外了解到这个服务器已经不能再扩充物理磁盘!!!(用唐长老的话说就是:“这可如何是好!”)因为这是一个ERP的数据,跟开发人员沟通后得知这个数据库有频繁的DML操作!得到这个信息后我觉得是否可以通过shrink表的方式来回收空间呢?经过对这个表空间的表做碎片分析后发现该表空间的表确实有很高的碎片,当天晚上经过shrink该表空间的中的表,表空间的使用率也从98%降到了76%,问题也就随之解决!哪么shrink操作,oracle内部到底为我们做了哪些操作呢?下面通过试验的方式了说明。

在我的测试环境有一个lilei用户,其默认表空间为ts_lilei,ts_lilei表空间中只有一个数据文件ts_lilei_data01.dbf,大小为100M,该用户下有一个表ll_enmotech,共有100000行数据

SQL> select username,default_tablespace from dba_users where username='LILEI';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
LILEI                          TS_LILEI



SQL> SELECT tablespace_name,file_id,file_name,(bytes/1024/1024) FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TS_LILEI';

TABLESPACE_NAME    FILE_ID FILE_NAME                                          (BYTES/1024/1024)
--------------- ---------- -------------------------------------------------- -----------------
TS_LILEI                 9 /u01/oracle/oradata/node1/ts_lilei_data01.dbf                    100

SQL> desc lilei.ll_enmotech
 Name Null? Type
 ----------------------------------------------------- -------- ------------------------------------
 ID NOT NULL NUMBER(38)
 NAME VARCHAR2(20)

表LILEI.LL_ENMOTECH在id列上有主键

SQL> select count(*) from lilei.ll_enmotech;

 COUNT(*)
----------
 100000

SQL> select owner,segment_name,segment_type from dba_segments where owner='LILEI';

OWNER      SEGMENT_NAME         SEGMENT_TYPE
---------- -------------------- ------------------
LILEI      SYS_C0011497         INDEX
LILEI      LL_ENMOTECH          TABLEEX

基于上面的这些信息,我们来看一下/u01/oracle/oradata/node1/ts_lilei_data01.dbf(9号数据文件)这个数据文件上数据的分布情况:

SQL> select owner,file_id,segment_name,block_id,blocks from dba_extents where owner='LILEI' order by 4;

OWNER         FILE_ID SEGMENT_NAME           BLOCK_ID     BLOCKS
---------- ---------- -------------------- ---------- ----------
LILEI               9 LL_ENMOTECH                 128          8
LILEI               9 SYS_C0011497                136          8
LILEI               9 LL_ENMOTECH                 144          8
LILEI               9 SYS_C0011497                152          8
LILEI               9 LL_ENMOTECH                 160          8
LILEI               9 LL_ENMOTECH                 168          8
LILEI               9 SYS_C0011497                176          8
LILEI               9 LL_ENMOTECH                 184          8
LILEI               9 SYS_C0011497                192          8
LILEI               9 LL_ENMOTECH                 200          8
LILEI               9 LL_ENMOTECH                 208          8

OWNER         FILE_ID SEGMENT_NAME           BLOCK_ID     BLOCKS
---------- ---------- -------------------- ---------- ----------
LILEI               9 SYS_C0011497                216          8
LILEI               9 LL_ENMOTECH                 224          8
LILEI               9 LL_ENMOTECH                 232          8
LILEI               9 SYS_C0011497                240          8
LILEI               9 LL_ENMOTECH                 248          8
LILEI               9 LL_ENMOTECH                 256          8
LILEI               9 SYS_C0011497                264          8
LILEI               9 LL_ENMOTECH                 272          8
LILEI               9 SYS_C0011497                280          8
LILEI               9 LL_ENMOTECH                 288          8
LILEI               9 LL_ENMOTECH                 296          8

OWNER         FILE_ID SEGMENT_NAME           BLOCK_ID     BLOCKS
---------- ---------- -------------------- ---------- ----------
LILEI               9 SYS_C0011497                304          8
LILEI               9 LL_ENMOTECH                 312          8
LILEI               9 LL_ENMOTECH                 320          8
LILEI               9 SYS_C0011497                328          8
LILEI               9 SYS_C0011497                336          8
LILEI               9 SYS_C0011497                344          8
LILEI               9 SYS_C0011497                352          8
LILEI               9 SYS_C0011497                360          8
LILEI               9 SYS_C0011497                368          8
LILEI               9 SYS_C0011497                376          8
LILEI               9 LL_ENMOTECH                 384        128

OWNER         FILE_ID SEGMENT_NAME           BLOCK_ID     BLOCKS
---------- ---------- -------------------- ---------- ----------
LILEI               9 SYS_C0011497                512        128
LILEI               9 LL_ENMOTECH                 640        128

35 rows selected.
SQL> select tablespace_name,file_id,block_id,bytes/1024/1024,blocks from dba_free_space where tablespace_name='TS_LILEI';

TABLESPACE_NAME    FILE_ID   BLOCK_ID BYTES/1024/1024     BLOCKS
--------------- ---------- ---------- --------------- ----------
TS_LILEI                 9        768              94      12032

说明一下:9号数据文件一共有12800个blck,其block分布情况如下:

1号块到(640+128)号块:是表LL_ENMOTECH和主键索引SYS_C0011497的block,共768个block

768号块到12800号块:是空闲的block,共12032个block

接下来我们再看一下LL_ENMOTECH和SYS_C0011497这两个对象的数据在块中的分布情况:

SQL> exec show_space('LL_ENMOTECH','LILEI','TABLE');
Total Blocks  ..........................384
Total Bytes   ..........................3145728
Total MBytes  ..........................3
Unused Blocks ..........................0
Unused Bytes  ..........................0
Unused KBytes ..........................0
Used Blocks   ..........................384
Used Bytes    ..........................3145728
Used KBytes   ..........................3072
Last Used Ext FileId....................9
Last Used Ext BlockId...................640
Last Used Block.........................128
The segment is analyzed below
FS1 Blocks (0-25)   ....................0
FS2 Blocks (25-50)  ....................0
FS3 Blocks (50-75)  ....................0
FS4 Blocks (75-100) ....................42
Unformatted Blocks  ....................0
Full Blocks         ....................328

PL/SQL procedure successfully completed.

SQL> exec show_space('SYS_C0011497','LILEI','INDEX');
Total Blocks  ..........................256
Total Bytes   ..........................2097152
Total MBytes  ..........................2
Unused Blocks ..........................0
Unused Bytes  ..........................0
Unused KBytes ..........................0
Used Blocks   ..........................256
Used Bytes    ..........................2097152
Used KBytes   ..........................2048
Last Used Ext FileId....................9
Last Used Ext BlockId...................512
Last Used Block.........................128
The segment is analyzed below
FS1 Blocks (0-25)   ....................0
FS2 Blocks (25-50)  ....................27
FS3 Blocks (50-75)  ....................0
FS4 Blocks (75-100) ....................0
Unformatted Blocks  ....................30
Full Blocks         ....................187

PL/SQL procedure successfully completed.

从上面的统计信息我们可知道以下信息:

(1)表 LL_ENMOTECH一共使用了384个block,其中328个block使用率是100%,42个block的使用率在75%-100%之间

(2)主键索引SYS_C0011497一共使用了256个block,其中187个block使用率是100%,27个block的使用率在25%-50%之间

表和索引数据的分布还是相当紧凑的!来看看表的碎片情况:

SQL> SELECT table_name, trunc(ROUND ((blocks * 8), 2)/1024,2) "High water levelM",
  2     trunc(ROUND ((num_rows * avg_row_len / 1024), 2)/1024,2) "Real  used spaceM",
  3     trunc(ROUND ((blocks * 10 / 100) * 8, 2)/1024,2) "Reserve space(pctfree) M",
  4    trunc( ROUND ((  blocks * 8
  5             - (num_rows * avg_row_len / 1024)
  6             - blocks * 8 * 10 / 100
  7            ),
  8            2
  9           ) /1024,2) "Waste spaceM"
 10    FROM dba_tables
 11    WHERE table_name = 'LL_ENMOTECH';

TABLE_NAME           High water levelM Real  used spaceM Reserve space(pctfree) M Waste spaceM
-------------------- ----------------- ----------------- ------------------------ ------------
LL_ENMOTECH                       2.89              1.81                      .28          .78

 

下面我手工的删除lilei.ll_enmotech表中id为奇数的行来模拟日常经常进行的DML操作:

SQL> delete from lilei.ll_enmotech where mod(id,2)=1;

50000 rows deleted.

现在看看表lilei.ll_enmotech的block使用情况和表的碎片情况:

SQL> exec dbms_stats.gather_table_stats(ownname=>'LILEI',TABNAME=>'LL_ENMOTECH');

PL/SQL procedure successfully completed.

SQL>  exec show_space('ll_enmotech','lilei','table');
Total Blocks  ..........................384
Total Bytes   ..........................3145728
Total MBytes  ..........................3
Unused Blocks ..........................0
Unused Bytes  ..........................0
Unused KBytes ..........................0
Used Blocks   ..........................384
Used Bytes    ..........................3145728
Used KBytes   ..........................3072
Last Used Ext FileId....................9
Last Used Ext BlockId...................640
Last Used Block.........................128
The segment is analyzed below
FS1 Blocks (0-25)   ....................0
FS2 Blocks (25-50)  ....................0
FS3 Blocks (50-75)  ....................328
FS4 Blocks (75-100) ....................42
Unformatted Blocks  ....................0
Full Blocks         ....................0

PL/SQL procedure successfully completed.

SQL> SELECT table_name, trunc(ROUND ((blocks * 8), 2)/1024,2) "High water levelM",
  2     trunc(ROUND ((num_rows * avg_row_len / 1024), 2)/1024,2) "Real  used spaceM",
  3     trunc(ROUND ((blocks * 10 / 100) * 8, 2)/1024,2) "Reserve space(pctfree) M",
  4    trunc( ROUND ((  blocks * 8
  5             - (num_rows * avg_row_len / 1024)
  6             - blocks * 8 * 10 / 100
  7            ),
  8            2
  9           ) /1024,2) "Waste spaceM"
 10    FROM dba_tables
 11    WHERE table_name = 'LL_ENMOTECH';

TABLE_NAME           High water levelM Real  used spaceM Reserve space(pctfree) M Waste spaceM
-------------------- ----------------- ----------------- ------------------------ ------------
LL_ENMOTECH                       2.89                .9                      .28         1.69

从上面的结果可以看出表还是使用了384个block,但是328个块的使用率 在50%-75%之间,42个block的使用率在75%-100%之间,另外从表的碎片情况可以看出来为表数据分配的空间是2.89M,但是只使用了不到1M,浪费了近1.7M,哪么现在我们对这个表做一次shrink:

SQL> alter table lilei.ll_enmotech enable row movement;

Table altered.

SQL> alter table lilei.ll_enmotech shrink space;

Table altered.

SQL> exec dbms_stats.gather_table_stats(ownname=>'LILEI',TABNAME=>'LL_ENMOTECH');

PL/SQL procedure successfully completed.

SQL> exec show_space('ll_enmotech','lilei','table');
Total Blocks  ..........................184
Total Bytes   ..........................1507328
Total MBytes  ..........................1.4375
Unused Blocks ..........................7
Unused Bytes  ..........................57344
Unused KBytes ..........................56
Used Blocks   ..........................177
Used Bytes    ..........................1449984
Used KBytes   ..........................1416
Last Used Ext FileId....................9
Last Used Ext BlockId...................384
Last Used Block.........................49
The segment is analyzed below
FS1 Blocks (0-25)   ....................0
FS2 Blocks (25-50)  ....................1
FS3 Blocks (50-75)  ....................1
FS4 Blocks (75-100) ....................0
Unformatted Blocks  ....................0
Full Blocks         ....................163

PL/SQL procedure successfully completed.

SQL> SELECT table_name, trunc(ROUND ((blocks * 8), 2)/1024,2) "High water levelM",
  2     trunc(ROUND ((num_rows * avg_row_len / 1024), 2)/1024,2) "Real  used spaceM",
  3     trunc(ROUND ((blocks * 10 / 100) * 8, 2)/1024,2) "Reserve space(pctfree) M",
  4    trunc( ROUND ((  blocks * 8
  5             - (num_rows * avg_row_len / 1024)
  6             - blocks * 8 * 10 / 100
  7            ),
  8            2
  9           ) /1024,2) "Waste spaceM"
 10    FROM dba_tables
 11    WHERE table_name = 'LL_ENMOTECH';

TABLE_NAME       High water levelM Real  used spaceM Reserve space(pctfree) M Waste spaceM
---------------- ----------------- ----------------- ------------------------ ------------
LL_ENMOTECH                   1.28                .9                      .12          .25

表做完shrink操作后,表只使用了184个block ,其中有163个block使用率是100%,1个block的使用率在0-25%,1个block的使用率在25%-50%之间,7个block还没有使用,剩下的12个block存储的应该是一些表的元数据,同时oracle为这个表的数据分配了1.28M的空间,使用了0.9M(和做shrink之前是一致的),只浪费了0.25M,这个表数据在block中的存储已经很理想了。到这里我们也清楚了,shrink操作发了物理操作,block中的数据放生了行迁移,使得block中的数据存储的更加集中,也收缩了HWM!

这里又牵出另外一个问题,就是在做完shrink的表,其上的索引发生了哪些变化呢?这个问题在下一篇博客中说明!

 

 

 

 

 

《数据块逻辑损坏和物理损坏学习》

数据块的损坏分两种情况,第一种是物理性的,第二种是逻辑性的。物理错误一般指数据块头部不可以访问、数据块校验值不合法。逻辑性一般是在物理性结构完整的情况下,数据的内容在含义上不正确,比如保存了不允许的字段值。
下面分别用两种情况说明数据块的物理错误和数据块的逻辑错误。

数据块的物理错误
以下通过dd命令模拟数据块物理错误
创建test表空间
SQL> create tablespace test datafile ‘/u01/app/oracle/oradata/PROD3/test01.dbf’ size 10m;
Tablespace created.
创建TOM用户
SQL> create user tom identified by oracle default tablespace test;
User created.
给TOM用户授予DBA权限
SQL> grant dba to tom;
Grant succeeded.
在TOM表空间创建test表
SQL> create table tom.test as select * from dba_objects where rownum <=100;
Table created.
SQL> select a.file_id,a.block_id,a.blocks,b.name from dba_extents a,v$datafile b  where a.file_id=b.file# and a.owner=’TOM’ and a.segment_name=’TEST’;
SQL> col name for a40
SQL> /
查询test存放着128开始的8个块中(128-135)
   FILE_ID   BLOCK_ID     BLOCKS NAME
———- ———- ———- —————————————-
         7        128          8 /u01/app/oracle/oradata/PROD3/test01.dbf
 查询test的行存放的数据块
SQL> select distinct dbms_rowid.rowid_block_number(rowid) from tom.test;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
————————————
                                 132
                                 131
改变132数据块的内容
[oracle@PROD3 ~]$ dd of=/u01/app/oracle/oradata/PROD3/test01.dbf bs=8192 conv=notrunc seek=132 <<EOF
> abcdefg
> EOF
0+1 records in
0+1 records out
8 bytes (8 B) copied, 0.00447092 s, 1.8 kB/s
使用DBV检查数据块的物理错误
[oracle@PROD3 ~]$ dbv file=/u01/app/oracle/oradata/PROD3/test01.dbf blocksize=8192
DBVERIFY: Release 11.2.0.4.0 – Production on Fri Jun 3 15:32:34 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY – Verification starting : FILE = /u01/app/oracle/oradata/PROD3/test01.dbf
Page 132 is marked corrupt
Corrupt block relative dba: 0x01c00084 (file 7, block 132)
Bad header found during dbv:
Data in bad block:
 type: 97 format: 2 rdba: 0x0a676665
 last change scn: 0x0000.00106c07 seq: 0x2 flg: 0x04
 spare1: 0x63 spare2: 0x64 spare3: 0x0
 consistency value in tail: 0x6c070602
 check value in block header: 0x5a7a
 computed block checksum: 0xc942
DBVERIFY – Verification complete
Total Pages Examined         : 1280
Total Pages Processed (Data) : 1
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 130
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1148
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1076234 (0.1076234)
可以查看到Page 132 is marked corrupt,Total Pages Marked Corrupt   : 1信息,得到当前数据文件第132块出现物理损坏。
DBV是一个外部命令,能够执行物理结构的完整性检查,DBV只能检查可缓存管理的块,所以只能用于检查数据文件,不能用于检查控制文件和重做日志文件的检查。DBV适用于offline或者online的数据文件,也可以验证备份文件,但备份必须是通过backup as copy 或者是cp 命令得到的备份文件,同样DBV可以检查ASM管理的数据文件。
使用DBV检查ASM管理的数据文件,但是要求数据库是open状态,同时要使用USERID,例如
dbv userid=system/oracle file=+DATA/PROD3/datafile/user01.dbf blocksize=8192
使用DBV检查backup as copy 备份的数据文件,因为数据文件已经损坏,备份前需要先设定备份坏块容忍度
run{
set maxcorrupt for datafile 7 to 2;
backup tablespace test;
}
 run {
2> set maxcorrupt for datafile 7 to 2;
3> backup datafile 7;
4> }
executing command: SET MAX CORRUPT
Starting backup at 03-JUN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/PROD3/test01.dbf
channel ORA_DISK_1: starting piece 1 at 03-JUN-16
channel ORA_DISK_1: finished piece 1 at 03-JUN-16
piece handle=/u01/app/oracle/fast_recovery_area/PROD3/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T155454_co2fyg3g_.bkp tag=TAG20160603T155454 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-JUN-16
备份后使用dbv进行备份文件的检查
dbv file=/u01/app/oracle/fast_recovery_area/PROD3/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T155454_co2fyg3g
DBVERIFY – Verification complete
Total Pages Examined         : 138
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 2
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 0
Total Pages Marked Corrupt   : 136
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1076974 (0.1076974)
可能是由于刚才误操作导致出现大量的块损坏
刚才使用DBV检查数据文件的方法是可以在数据库关闭的情况下进行检查的,在数据库open的情况下我们可以使用段的检查方式(需要sysdba的权限进行检查),可以缩小检查的范围
SQL> select tablespace_id,header_file,header_block
  2   from sys_dba_segs
  3  where owner=’TOM’ and segment_name=’TEST’;
TABLESPACE_ID HEADER_FILE HEADER_BLOCK
————- ———– ————
            8           7          130
dbv userid=system/oracle segment_id=8.7.130
DBVERIFY: Release 11.2.0.4.0 – Production on Fri Jun 3 16:06:46 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY – Verification starting : SEGMENT_ID = 8.7.130
Page 132 is marked corrupt
Corrupt block relative dba: 0x01c00084 (file 7, block 132)
Bad header found during dbv:
Data in bad block:
 type: 97 format: 2 rdba: 0x0a676665
 last change scn: 0x0000.00106c07 seq: 0x2 flg: 0x04
 spare1: 0x63 spare2: 0x64 spare3: 0x0
 consistency value in tail: 0x6c070602
 check value in block header: 0x5a7a
 computed block checksum: 0xc942
DBVERIFY – Verification complete
Total Pages Examined         : 8
Total Pages Processed (Data) : 1
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 2
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 3
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1076234 (0.1076234)
同样也可以使用dbms_hm.run_check存储过程进行坏块的检查,dbms_hm.run_check除了可以检查数据文件,也可以检查控制文件和日志文件(可以只检查一个区(extents))。
begin
dbms_hm.run_check(
check_name=>’Data Block Integrity Check’,
run_name=>’HM_CHECK132′,
        input_params=>’BLC_DF_NUM=7;BLC_BL_NUM=132′);
end;
/
select dbms_hm.get_run_report(‘HM_CHECK132’) from dual;
删除所有的报告
exec dbms_hm.drop_schema();
exp/expdp在导出数据时会扫描每个数据块,所以也会检查每个数据块的物理错误
[oracle@PROD3 ~]$ exp tom/oracle owner=tom
Export: Release 11.2.0.4.0 – Production on Fri Jun 3 16:23:03 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified users …
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TOM
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TOM
About to export TOM’s objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TOM’s tables via Conventional Path …
. . exporting table                           TEST
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 7, block # 132)
ORA-01110: data file 7: ‘/u01/app/oracle/oradata/PROD3/test01.dbf’
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
所有损坏的数据块都可以通过v$database_block_corruption查询到
SQL> select * from v$database_block_corruption;
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
         4        210          2                  0 CORRUPT
         4        524          1                  0 CORRUPT
         7        132          1                  0 CORRUPT
损坏存放行数据块的恢复
dbv file=/u01/app/oracle/oradata/PROD3/test02.dbf blocksize=8192
DBVERIFY: Release 11.2.0.4.0 – Production on Fri Jun 3 17:19:36 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY – Verification starting : FILE = /u01/app/oracle/oradata/PROD3/test02.dbf
Page 139 is marked corrupt
Corrupt block relative dba: 0x0200008b (file 8, block 139)
Bad header found during dbv:
Data in bad block:
 type: 97 format: 2 rdba: 0x0a676665
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05
 spare1: 0x63 spare2: 0x64 spare3: 0x0
 consistency value in tail: 0x00000001
 check value in block header: 0xa78b
 computed block checksum: 0xc88b
DBVERIFY – Verification complete
Total Pages Examined         : 1280
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 127
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1152
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1080609 (0.1080609)
使用备份的数据进行块的恢复
RMAN> recover datafile 8 block 139
2> ;
Starting recover at 03-JUN-16
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00008
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD3/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T171747_co2lsw49_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PROD3/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T171747_co2lsw49_.bkp tag=TAG20160603T171747
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 03-JUN-16
————-recover 命令不支持增量备份———————–
RMAN备份时的受损块,db_block_checking打开时的受损块和dbms_hm.run_check发现的受损块都可以通过v$database_block_corruption视图查询
—————-恢复方法
recover corruption list;
数据块的逻辑错误
创建range分区表
SQL> create table scott.emp1 (empno number(4),ename varchar2(10),deptno number(2))
  2  partition by range (deptno)
  3  (partition p1 values less than (10) tablespace users,
  4  partition p2 values less than (20) tablespace users,
  5  partition p3 values less than (30)) tablespace users;
Table created.
SQL> create table scott.dummy_y as select empno,ename,deptno
  2  from scott.emp
  3  where deptno < 10;
SQL> create table scott.dummy_x as select empno,ename,deptno
  2  from scott.emp
  3  where deptno < 20;
SQL> create table scott.dummy_z as select empno,ename,deptno
  2  from scott.emp
  3  where deptno >= 20 and deptno < 30;
SQL> alter table scott.emp1 exchange partition p1
  2  with table scott.dummy_y;
SQL> alter table scott.emp1 exchange partition p2
  2  with table scott.dummy_x;
alter table scott.emp1 exchange partition p3
  2  with table scott.dummy_z;
emp1表的数据存放在240,248开始的16个块中,数据文件为4号
SQL> col name for a40
SQL> select a.file_id,a.block_id,a.blocks,b.name from dba_extents a,v$datafile b  where a.file_id=b.file# and a.owner=’SCOTT’ and a.segment_name=’EMP1′;
  FILE_ID   BLOCK_ID     BLOCKS NAME
———- ———- ———- —————————————-
         4        240          8 /u01/app/oracle/oradata/PROD3/users01.db
                                 f
         4        248          8 /u01/app/oracle/oradata/PROD3/users01.db
                                 f
deptno是30的记录不能插入emp1表
SQL> insert into scott.emp1 values(1000,’SCOTT’,30);
insert into scott.emp1 values(1000,’SCOTT’,30)
                  *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
使用交换分区的方法把deptno=30的值插入emp1表
SQL> create table scott.emp2 (empno number(4),ename varchar2(10),deptno number(2)) tablespace users;
Table created.
SQL> insert into scott.emp2 values(1000,’SCOTT’,30);
1 row created.
SQL> alter table scott.emp1 exchange partition p3 with table scott.emp2 without validation;
Table altered.
deptno 为30的值已经插入到emp1
SQL> select * from scott.emp1 partition (p3);
     EMPNO ENAME          DEPTNO
———- ———- ———-
      1000 SCOTT              30
使用analyze字句可以检查出上述模拟的逻辑块错误
analyze语句要求有一张保存分析结果的表,执行utlvalid.sql脚本建立invalid_row表保存分析结果
SQL> @?/rdbms/admin/utlvalid.sql
Table created.
SQL> desc invalid_rows
 Name                                      Null?    Type
 —————————————– ——– —————————-
 OWNER_NAME                                         VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 PARTITION_NAME                                     VARCHAR2(30)
 SUBPARTITION_NAME                                  VARCHAR2(30)
 HEAD_ROWID                                         ROWID
 ANALYZE_TIMESTAMP                                  DATE
SQL> analyze table scott.emp1 validate structure;
Table analyzed.
SQL> select table_name,partition_name,head_rowid from invalid_rows;
TABLE_NAME                     PARTITION_NAME                 HEAD_ROWID
—————————— —————————— ——————
EMP1                           P3                             AAAVWAAAEAAAAEEAAA
analyze分析结果发现一条rowid为AAAVWAAAEAAAAEEAAA信息,这条记录就是deptno=30的记录
SQL> select * from scott.emp1 where rowid=’AAAVWAAAEAAAAEEAAA’;
     EMPNO ENAME          DEPTNO
———- ———- ———-
      1000 SCOTT              30
SQL>
关于逻辑坏块的修复,是不是直接删除就可以了?先谢过大神,帮我解答疑问吧。

oracle rac instance crash case

这是昨天帮北区同事分析的一个数据库故障,这里跟大家分享。故障发生在昨天下午,数据库实例的一个节点自动crash,并且自动启动时无法启动,最后通过手工启动成功。首先我们来分析一下为什么crash之后,crs无能自动将数据库实例open,如下是alert log的信息:

Sun May 29 18:02:16 2016
Errors in file /u01/app/oracle/admin/sundb/bdump/sundb1_lmsa_38077024.trc:
ORA-00600: internal error code, arguments: [kjdrisRMnovalid:msgRMno], [4294967295], [0], [0], [], [], [], []
.....
Errors in file /u01/app/oracle/admin/sundb/bdump/sundb1_lms2_38536062.trc:
ORA-00600: internal error code, arguments: [kjdrisRMnovalid:msgRMno], [4294967295], [0], [0], [], [], [], []
Sun May 29 18:02:16 2016
ALTER DATABASE   MOUNT
Sun May 29 18:02:16 2016
Errors in file /u01/app/oracle/admin/sundb/bdump/sundb1_lmse_37159724.trc:
ORA-00600: internal error code, arguments: [kjdrisRMnovalid:msgRMno], [4294967295], [0], [0], [], [], [], []
Sun May 29 18:02:16 2016
Errors in file /u01/app/oracle/admin/sundb/bdump/sundb1_lmsa_38077024.trc:
ORA-00600: internal error code, arguments: [kjdrisRMnovalid:msgRMno], [4294967295], [0], [0], [], [], [], []
Sun May 29 18:02:16 2016
Starting background process ASMB
......
Errors in file /u01/app/oracle/admin/sundb/bdump/sundb1_lmsb_38142882.trc:
ORA-00600: internal error code, arguments: [kjdrisRMnovalid:msgRMno], [4294967295], [0], [0], [], [], [], []
Sun May 29 18:02:21 2016
Errors in file /u01/app/oracle/admin/sundb/bdump/sundb1_lms0_39257032.trc:
ORA-00600: internal error code, arguments: [kjdrisRMnovalid:msgRMno], [4294967295], [0], [0], [], [], [], []
Sun May 29 18:02:21 2016
LMS0: terminating instance due to error 484
Sun May 29 18:02:21 2016
Dump system state for local instance only
System State dumped to trace file /u01/app/oracle/admin/sundb/bdump/sundb1_diag_39519196.trc
Sun May 29 18:02:21 2016
Trace dumping is performing id=[cdmp_20160529180221]
Sun May 29 18:02:22 2016
Shutting down instance (abort)
License high water mark = 1

 

我们可以发现,数据库实例在mount的过程中其实就没有成功。从日志上来看,几乎均为lms进程的错误,这里我们暂且不论这个ora-00600 错误是什么含义。首先先分析日志文件,看看其中的内容是什么?

Unix process pid: 38077024, image: oracle@cxhxrac1 (LMSa)

*** SERVICE NAME:() 2016-05-29 18:02:13.888
*** SESSION ID:(145.1) 2016-05-29 18:02:13.888
 0 GCS shadows cancelled, 0 closed
 0 GCS resources traversed, 0 cancelled
 LMS 10: 149696 GCS resources on freelist, 150016 on array, 150021 allocated
 0 GCS shadows traversed, 0 replayed, 0 duplicates
 lms 10 finished replaying gcs resources
 0 write requests issued in 14291 GCS resources
 1 PIs marked suspect, 0 flush PI msgs
 0 write requests issued in 0 GCS resources
 0 PIs marked suspect, 0 flush PI msgs
 lms 10 finished fixing gcs write protocol
 FUSION MSG 11076caf0,34 from[1,30146664] ver[10,-1] ln 128 sq[1,8]
      CLOSE [0x178155.12a0000, 298] shadow [0,0] seq 0x2 act 1
        client [7000002bbffca60,155] reqid 163 ordered 0
        grant 1 convert 0 role 0
        pi [0x0.0x0] flags 0x0 state 0x20
        disk scn 0x0.0 writereq scn 0x0.0 rreqid 0
        msgRM# -1 bkt# 298 drmbkt# 298
   pkey 298
   hv 2 [stat 0x0, 0->0, wm 32767, RMno 0, reminc 10, dom 0]
   kjga st 0x4, step 0.0.0, cinc 10, rmno 0, flags 0x0
   lb 0, hb 0, myb 298, drmb 298, apifrz 0
*** 2016-05-29 18:02:16.034
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kjdrisRMnovalid:msgRMno], [4294967295], [0], [0], [], [], [], []

 

上述信息是lmsa 进程的trace文件内容,我们不难看出,这部分信息跟Oracle DRM操作有关。实际上,我们也知道Oracle lms进程会参与DRM的操作,关于这一点,要从lms进程的原理说起。网上有很多文章描述,这里我们不多说。

首先从上述trace 内容关键字pkey 298我们可以看出,这是10g的rac,这是drm信息的标示之一。另外其实从kjdrisRMnovalid:msgRMno也可以看出,这是DRM的操作。既然如此,那么是不是drm的参数配置有问题呢?因为这是一个rac集群,所以我们继续来看下alert log内容即可。通过分析发现在上次数据库实例启动时为4月28号,启动的参数如下:

Thu Apr 28 22:36:58 2016
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
.....
  standby_file_management  = AUTO
  _gc_undo_affinity        = FALSE
  _gc_affinity_time        = 0

 

可以看出,配置了drm的参数,也就是屏蔽了drm功能。而这次数据库实例crash后,crs在自动拉数据库实例时并没有启用这2个参数。毫无疑问,这说明客户之前用的pfile,而crs启动数据库实例时使用的是spfile。由于spfile和pfile的内容不一致,也就出现来这个问题。

对于实例宕机后无法自动启动的疑问,我们已经能够解释清楚了。那么最开始的问题呢?数据库实例为什么会宕机呢?这才是关键关键。下面我们继续来看alert log:

Sun May 29 17:24:03 2016
Errors in file /u01/app/oracle/admin/xxxx/bdump/xxxx1_diag_35455882.trc:
ORA-07445: exception encountered: core dump [] [] [] [] [] []
Sun May 29 17:24:41 2016
Restarting dead background process DIAG
DIAG started with pid=3, OS id=35848586
Sun May 29 17:25:00 2016
Errors in file /u01/app/oracle/admin/xxxx/bdump/xxxx1_diag_35848586.trc:
ORA-00600: internal error code, arguments: [17147], [0x70000090C5F9378], [], [], [], [], [], []
Sun May 29 17:29:56 2016
Restarting dead background process DIAG
DIAG started with pid=262, OS id=34472420
Sun May 29 17:30:15 2016
Errors in file /u01/app/oracle/admin/xxxx/bdump/xxxx1_diag_34472420.trc:
ORA-00600: internal error code, arguments: [17147], [0x70000090C5F9378], [], [], [], [], [], []
Sun May 29 17:32:36 2016
Thread 1 advanced to log sequence 22645
  Current log# 10 seq# 22645 mem# 0: +NEWDATA/xxxx/onlinelog/group_10.1016.886632517
Sun May 29 17:35:14 2016
Restarting dead background process DIAG
DIAG started with pid=573, OS id=1835604
Sun May 29 17:35:33 2016
Errors in file /u01/app/oracle/admin/xxxx/bdump/xxxx1_diag_1835604.trc:
ORA-00600: internal error code, arguments: [17147], [0x70000090C5F9378], [], [], [], [], [], []
Sun May 29 17:38:32 2016
Errors in file /u01/app/oracle/admin/xxxx/udump/xxxx1_ora_64421944.trc:
ORA-07445: exception encountered: core dump [] [] [] [] [] []
Sun May 29 17:38:34 2016
......
Errors in file /u01/app/oracle/admin/xxxx/bdump/xxxx1_diag_12452748.trc:
ORA-00600: internal error code, arguments: [17147], [0x70000090C5F9378], [], [], [], [], [], []
Sun May 29 17:45:59 2016
Restarting dead background process DIAG
DIAG started with pid=153, OS id=17367786
Sun May 29 17:46:17 2016
Errors in file /u01/app/oracle/admin/xxxx/bdump/xxxx1_diag_17367786.trc:
ORA-00600: internal error code, arguments: [17147], [0x70000090C5F9378], [], [], [], [], [], []
Sun May 29 17:51:23 2016
Restarting dead background process DIAG
DIAG started with pid=859, OS id=13369506
Sun May 29 17:51:41 2016
Errors in file /u01/app/oracle/admin/xxxx/bdump/xxxx1_diag_13369506.trc:
ORA-00600: internal error code, arguments: [17147], [0x70000090C5F9378], [], [], [], [], [], []
Sun May 29 17:56:32 2016
Restarting dead background process DIAG
DIAG started with pid=665, OS id=11535338
Sun May 29 17:56:50 2016
Errors in file /u01/app/oracle/admin/xxxx/bdump/xxxx1_diag_11535338.trc:
ORA-00600: internal error code, arguments: [17147], [0x70000090C5F9378], [], [], [], [], [], []
Sun May 29 18:01:39 2016
Errors in file /u01/app/oracle/admin/xxxx/udump/xxxx1_ora_54133096.trc:
ORA-07445: exception encountered: core dump [] [] [] [] [] []
Sun May 29 18:01:41 2016
Errors in file /u01/app/oracle/admin/xxxx/bdump/xxxx1_pmon_49414536.trc:
ORA-07445: exception encountered: core dump [] [] [] [] [] []
Sun May 29 18:01:45 2016
DBW4: terminating instance due to error 472
Sun May 29 18:01:45 2016
Errors in file /u01/app/oracle/admin/xxxx/bdump/xxxx1_lmd0_6947108.trc:
ORA-00472: PMON  process terminated with error
......
Errors in file /u01/app/oracle/admin/xxxx/bdump/xxxx1_psp0_33620412.trc:
ORA-00472: PMON  process terminated with error
Sun May 29 18:01:45 2016
Shutting down instance (abort)

 

我们可以看出,数据库实例之所以会crash掉,是因为Oracle PMON进程出现了异常,最后dbw4进程强行将数据库实例终止。很明显,我们需要继续分析pmon进程为什么会出现异常,如下是pmon 进程的trace 内容:

*** 2016-05-29 18:01:41.700
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [] [] [] [] [] []
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedst+001c          bl       ksedst1              FFFFFFFFFFFC820 ?
                                                   5F707472676C0001 ?
ksedmp+0290          bl       ksedst               1048E0580 ?
ssexhd+030c          bl       ksedmp               300001D15 ?
000048BC             ?        00000000             
kglobfr+01f0         bl       kglobcl              000000022 ? 000000009 ?
                                                   7000009E4E3D240 ? 1054EC358 ?
                                                   FFFFFFFFFFFD1B0 ?
kgllccl+1414         bl       kglobfr              FFFFFFFFFFFD290 ? 000000000 ?
                                                   1003C5ACC ? 110224BC8 ?
kgllcu+01b4          bl       kgllccl              10011C920 ? 000000000 ?
                                                   FFFFFFFFFFFD350 ?
kslilcr+0240         bl       _ptrgl               
ksl_cleanup+063c     bl       kslilcr              7000009E4E3D240 ? 3000000B8 ?
                                                   FFFFFFFFFFFD7E0 ?
ksuxfl+0678          bl       ksl_cleanup          000000000 ? 700000A1F5A3A38 ?
                                                   FFFFFFFFFFFDA30 ?
ksuxda+0060          bl       ksuxfl               A00000000000A ?
ksucln+0814          bl       ksuxda               
ksbrdp+0408          bl       _ptrgl               
opirip+03fc          bl       03F5A0B4             
opidrv+0448          bl       opirip               11028BD90 ? 41028D6D0 ?
                                                   FFFFFFFFFFFF950 ?
sou2o+0090           bl       opidrv               3202AB3E7C ? 4A006F198 ?
                                                   FFFFFFFFFFFF950 ?
opimai_real+0150     bl       01FC5034             
main+0098            bl       opimai_real          000000000 ? 000000000 ?
__start+0070         bl       main                 000000000 ? 000000000 ?

 

从上面这部分call stack内容可以看出,pmon的异常出现在函数kglobfr调用上,该函数表示

KGL OBject Free all heaps。通过分析发现Oracle Bug 8420775 : PMON CRASHED WITH ORA-07445的描述与pmon 的call stack堆栈基本一致;该文档中oracle sr认为该现象与oracle bug 5721821 一致

同时我又分析了故障期间的server process的ora-07445错误,发现基本上都在kglobcl操作上出现异常,基于这点分析发现;Oracle Bug 5754574 : ORA-7445 [KGLOBCL+178] AND PMON CRASH 的描述与pmon trace描述也很接近,同时该bug的描述中 Oracle SR也提到可能为Oracle bug 5721821

基于上述分析,我认为第一次数据库crash 为Oracle bug 5721821导致,而且该Bug也有针对10.2.0.3 for aix的Patch。因此我们建议客户安装该Patch,然后进行观察。

oracle latch系列之CBC LATCH

上周突然有一个想法,想对oracle的知识做一个系列的总结。想着之前刚接触oracle的时候总是被oracle的lock搞得晕头转向的,那么这次的系列总结就从lock中的latch开始吧。故从今晚开始准备用差不多两周的时间来搞一个oracle latch系列,今晚先来一个CBC吧!!好了,废话不多说了。。。

接触过关系型数据库的同志们对于lock这个概念都不陌生吧!总的来说关系型数据库中之所以引进lock是处于两方面的考虑。1.增加数据库的并发性;2.保证数据库数据的一致性;我们说关系型数据和一般的文件系统的区别之一便是关系型数据库引进的锁机制。OK,这里就不再讨论lock了,关于oracle的lock等我写完latch系列之后会单独的再写一篇博客。

那么latch是一种轻量级的lock(所谓的轻量级是指latch的实现方式而不是它的功能),这也就是说latch的主要作用也是增加数据库的并发性和确保数据库数据的一致性,只是latch保证的是oracle SGA中的数据一致性,它是在SGA的share pool中分配(下面会证明),那么这里我提出一个问题,PGA需要LATH的保护吗?当然不需要啦,因为SGA是共享的,而PGA不是共享的,只有共享的资源才需要latch或者lock的保护。

oracle的cache buffers chain latch(下面都简称为cbc latch)在oracle中是一个非常常见的latch,如果对于cbc latch的内部原理不是很清楚的话,在当我们在遇见大量的cbc latch时候我们可能就会感觉一头雾水,不知从哪里下手。具体来说呢cbc latch是为了保护cache buffers chain链表的完整性,说到底就是为了保护buffer cache的一致性。所以为了能更好的理解abc latch的原理,我就从buffer cache的结构开始说起,然后再说cbc latch的原理,知道了cbc latch的原理后,在工作中遇见cbc latch的事件原因我们也就很清楚了。然后我会手工的来模拟cbc latch,最后我会说一下在生产环境中如果遇见cbc latch的时候,怎么收集cbc latch的相关信息,进而解决问题!下面我们就开始吧

1.buffer cache的结构

关于buffer cache的一些基本知识建议大家查看Oracle concept的14章中Database Buffer Cache这一节。buffer cache是SGA中一个非常重要的组成部分,它主要缓存了当前正在使用或者说最近使用过的数据库块。通过使用buffer cache Oracle可以达到一下两个目的:1.减少物理读I/O(就是平时我们所说的通过空间换取时间)。oracle将那些经常访问的数据库块(hot block)缓冲在buffer cache中;将那些不经常访问的数据库块(cold block)刷新保存到磁盘上。这样当发生hot block的读操作的时候不需要发生物理读,而只需要从buffer cache中取出相应的数据返回给客户端。另外,当数据库开启了Smart flash cache时,一部分buffer cache可以长期驻留在flash cache中,这些扩展的buffer cache是存放在一个闪存磁盘的设备上的,这个闪存磁盘是一个用作闪存内存的固态存储设备。数据库通过使用flash memory缓存buffer来减少从物理磁盘上读取数据,这都是通过减少物理读来提升数据库的性能。2.减少物理写I/O。数据库在buffer cache中进行数据库块的DML操作,并且把这些操作的元数据(redo)缓冲到redo log buffer中。当用户commit后,数据库只是确保将那些在redo log buffer中的元数据写到磁盘上,而不是立即将这些改变的数据库块更新到磁盘上,相反,在后台DBWn进程使用延迟写,将这些更新后的数据库块写到磁盘上;这样oracle通过使用WLA(write log ahead)和buffer cache机制,使得每次发生数据的DML操作后不用立马发生数据块的物理写的I/O操作,而是等待一定的触发条件才发生I/O以此来提高数据库的性能,

buffer cache的组成大概如下图所示(图片摘自oracle concept):
cncpt220

由上图我们可以看出buffer cache主要分为4个部分,default pool,keep pool,recycle pool和nK pool;default pool,keep pool,recycle pool的结构和管理方式(LRU算法,share pool和PGA是heap的方式)其实是完全一样的,这就好比是三个完全一摸一样的内存结构,只是这三个内存结构的名字不一样罢了。其中default pool是系统默认的块缓存的地方,这些块是非全表扫描操作所读取的块,换句话说default pool中的块是非全表扫面所读取块的默认缓存的内存区域。keep pool是缓存哪些经常会被访问的块(hot block),而recycle pool是缓存那些不经常访问的block(cold block)。之所以要将hot block缓存在keep pool中是因为,如果将这些块缓存在default pool,则可能很快会被age out,这样等下个数据库操作重用这些块的时候不得不发生磁盘级别的操作(物理读,慢);将这些hot block缓存在keep pool中可以推迟这些块被age out的时间,这样等需要重用这些block的时候只需要发生内存级别的操作(逻辑读,快);反之,将cold block存放在recycle pool中可以很快的将这些块age out,以此来提高buffer cache的使用效率,减少buffer cache的竞争。另外因为Oracle数据库中可以有不同大小的block表空间存在,必须为每一个非标准的block size的tablespace配置一个buffer cache,nk pool就是为这些相应的nk block size的tablespace配置的buffer cache。例如我需要配置一个没个block是16K的tablespace,那么我们就需要一个16K buffer cache pool的内存区域来缓存16K的block。好了,上面我们只是简单的介绍了一下buffer cache的结构。下面我们来看一下oracle如何访问buffer cache中的buffer,我们先从下面的几个对象的概念入手:

1.buffer:数据文件block在内存(buffer cache)中的一个拷贝,就是数据文件的block在内存中的另外一种叫法。

2.buffer header:一个描述buffer结构,状态,地址等信息的对象,每一个buffer都有一个buffer header

3.hash buckets:一个维护buffer header的数据结构

4.a hash cache buffer chain:多个相关的buffer header组成的一个双向链表

5.LRU:least recently used,最近最少使用算法,用来管理buffer cache中的buffers

上面这几个对象的关系如下图所示:

4

为了能更好的理解上面的这幅图,我先来解释一下hash 算法。想象这样的一个场景,假如现在有学号为1到n共n个学生,我们怎么将这n个学生分配到编号为0到m-1共m(n>>m)个班级中?一种可用的方案就是用学生的学号(学号记为stu#)除以m,得到一个余数,所得到的这个余数就是学号为sut#所要被分配到的班级编号,如stu#为1的学生会被分配到1号班级,stu#为2的学生会被分配到2号班级,stu#为m的学生会被分配到0号班级等等,以此类推,直到所有的学生被分配到对应的班级中,这就是hash取余散列法!接着我们继续上面的例子再思考这样的一个问题,我现在拿到一个学生的stu#,我怎么快速的找到这个学生的详细信息?一种可行的办法就是在每个班级中为每个学生创建一个档案,每个学生的档案放在相应的班级中,当我拿到一个stu#时,我先做一次hash取余散列,会得到这个学生所在的班级,然后我查找这个班级中所有的学生档案,便可找到这个学生的详细信息。而在buffer cache中寻找buffer就是通过上面的方法来实现的。

在上图中,DBA(data block address,实际上就是完全由文件号和块号决定的)相当于学生的学号,hash bucket相当学生的班级,BH(buffer header)相当于学生档案,buffer相当于学生。oracle 数据库在buffer cache要访问某一个block(在内存中可以认为block和buffer是等价的,所以下文中我并没有很严格的确分block和buffer)的时候就是将这个块的dba(database block address)除以hash bucket的数量(这个hash bucket的数量是由_db_block_hash_bucke这个隐含参数决定的)所得到的余数就是这个块对应的在这个数据库上被分配到的hash bucket的编号,而在每个hash bucket下面会维护一个由bh(buffer header)组成的双向链表,这个链表就是cbc(cache buffers chain),它上面就是块的dba通过散列后具有相同散列值的block的bh,bh就是buffer的描述信息,每一个buffer都会有且仅有一个与之对应的bh,bh之于buffer就是学生档案之于学生之间的关系。bh描述了buffer的所有的详细信息,然后oracle会扫描这个hash bucket上所维护这个cbc,如果找到了块对应的bh,则会从bh中拿到这个buffer的ba(abuffer ddress,它就是这个buffer在内存中的地址,通过这个地址我们就可以很容易的在buffer area中定位到这个buffer),再根据这个ba直接到到buffer中读出这个buffer中的数据。如果在扫描cbc的过程中找到了这个块对应的bh,则直接到buffer area读出这个buffer的数据,这个过程叫做逻辑读;如果在扫描过程中没有找到这个块对应的bh,则server process会从数据文件中读出这个块,然后放到对应的hash bucket的cbc上面(排除全表读的情况),这个过程就是物理读。在上图中,如果我们要读取3号文件的9号块,那么Oracle会先拿到这个块的dba,然后通过hash函数得到这个块的对应的hash bucket编号,例如在上图中3号文件的9号块会被散列到编号为3号的hash bucket上,然后Oracle会在3号hash bucket的cbc链表上查找是否有文件号为3,块号为9的buffer的bh,然后从bh中拿到ba(暂时不考虑buffer的多版本问题),下面oracle就根据ba直接到到buffer area中发生一次逻辑读将这个buffer的数据读出来。

OK,通过上面的介绍大家可能对buffer cache的结构以及oracle如何是读取块的过程有了一个大概的了解,如果还是不是很清楚,建议先读一下oracle官方文档中oracle concept的14章。

下面我通过一个实例来说明上面的理论知识:

SQL> select * from demo.ll_t1 where id=11;

 ID NAME
---------- ----------
 11 enmotech

在表demo.ll_t1中有一条数据,id=11,name=enmotech

SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from demo.ll_t1 where id=11;

 FILE# BLOCK#
---------- ----------
 6 135

上面这条数据位于6号文件的135号块上

SQL> select to_char(dbms_utility.make_data_block_address(6,135),'xxxxxxxxxxxxx') from dual;

TO_CHAR(DBMS_U
--------------
       1800087

6号文件的135号块的dba为1800087,dba是16进制的

SQL> select addr,HLADDR,NXT_HASH,PRV_HASH,ba from x$bh where file#=6 and dbablk=135;

ADDR HLADDR NXT_HASH PRV_HASH BA
---------------- ---------------- ---------------- ---------------- ----------------
00007F1890BF51A8 0000000077BDAEB8 0000000077BDB4B8 0000000077BDB4B8 000000006C09A000

当6号文件的135号块缓存在buffer cache中的时候,就会有一个唯一的bh与之对应,bh的信息都是存储在x$bh视图中的,那么我根据文件号块号从x$bh中找出6号文件135号块的bh信息,从x$bh中查询出了addr,hladdr,nxt_hash,prv_hash,ba;其中addr是buffer header在内存中的地址,hladdr是hash latch的地址,nxt_hash是该buffer header在cbc链表上下一个bh的地址,prv_hash是buffer header在cbc链表上上一个bh的地址,ba是该bh指定的buffer在内存中的地址。上面的查询结果说明6号文件的135号块的bh在内存中的地址的hash值是00007F1890BF51A8,它使用的hash latch地址为0000000077BDAEB8,它在cbc链表上的下一个bh地址为0000000077BDAEB8,上一个bh地址为0000000077BDB4B8,它所指定的buffer在buffer cache中的地址为000000006C09A000,下面我搞一个buffer cache的dump文件,来看看bh在内存到底存储了哪些内容:

SQL> alter session set events 'immediate trace name buffer level 1';

Session altered.

这里我的level是1,只是dump出了bh的结构收据,所以数据不是很大

SQL> select * from v$diag_info where name='Default Trace File';

   INST_ID NAME
---------- ----------
VALUE
----------------------------------------------------------------------------------------------------
         1 Default Tr
           ace File
/u01/oracle/diag/rdbms/node1/node1/trace/node1_ora_15188.trc

产生的dump文件为/u01/oracle/diag/rdbms/node1/node1/trace/node1_ora_15188.trc

[oracle@node2 admin]$ vim /u01/oracle/diag/rdbms/node1/node1/trace/node1_ora_15188.trc
race file /u01/oracle/diag/rdbms/node1/node1/trace/node1_ora_13980.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/oracle/app
System name: Linux
Node name: node2
Release: 3.10.0-229.el7.x86_64
Version: #1 SMP Fri Mar 6 11:36:42 UTC 2015
Machine: x86_64
Instance name: node1
Redo thread mounted by this instance: 1
Oracle process number: 29
Unix process pid: 13980, image: oracle@node2 (TNS V1-V3)


*** 2016-05-24 00:18:25.483
*** SESSION ID:(34.165) 2016-05-24 00:18:25.483
*** CLIENT ID:() 2016-05-24 00:18:25.483
*** SERVICE NAME:(SYS$USERS) 2016-05-24 00:18:25.483
*** MODULE NAME:(sqlplus@node2 (TNS V1-V3)) 2016-05-24 00:18:25.483
*** ACTION NAME:() 2016-05-24 00:18:25.483
 
Dump of buffer cache at level 1 for tsn=2147483647 rdba=0
BH (0x6c3f0468) file#: 1 rdba: 0x00411e40 (1/73280) class: 1 ba: 0x6c290000
 set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
 dbwrid: 0 obj: 250 objn: 250 tsn: 0 afn: 1 hint: f
 hash: [0x77b8d248,0x77b8d248] lru: [0x6c3f0680,0x6c3f0420]
 lru-flags: hot_buffer
 ckptq: [NULL] fileq: [NULL] objq: [0x6c7f6348,0x6c3f0448] objaq: [0x6c7f6358,0x6c3f0458]
 st: XCURRENT md: NULL fpin: 'kdswh05: kdsgrp' tch: 4
 flags:
 LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
-------------------省略----------------------------
BH (0x69be87b8) file#: 6 rdba: 0x01800087 (6/135) class: 1 ba: 0x6C09A000
 set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
 dbwrid: 0 obj: 76863 objn: 76862 tsn: 7 afn: 6 hint: f
 hash: [0x77bdb4b8,0x77bdb4b8] lru: [0x69be90f0,0x6b3dd7b0]
 lru-flags: hot_buffer
 ckptq: [NULL] fileq: [NULL] objq: [0x6d3e8b28,0x742b54c0] objaq: [0x69be9128,0x6abd8e48]
 st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 8
 flags: block_written_once redo_since_read
 LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
-------------------省略----------------------------

通过我们上面从x$bh中查询出来的6号文件的135号块的ba值,可以定位到6号文件135号块的bh信息。当然了,我们也可以根据dba来定位。上面我加粗斜体的部分就是6号文件的135号块的bh在buffer cache的信息。下面我来试着解读一下上面的这些信息。

BH (0x69be87b8):bh的hash值

file#: 6 :文件号为6

rdba: 0x01800087 (6/135) :相对data block address,这个值完全就是由文件号和块号决定的,16进制格式的,这个值和我们上面通过sql查询出来的值是保持一致的!有没有感觉很神奇????哈哈

class: 1:该buffer的类型,1=data block, 2=sort block,3=save undo block,4=segment header,5=save undo header, 6=free list,7=extent map,9=2nd level bmb, 10=3rd level bmb,11=bitmap block, 12=bitmap index block,13=unused, 14=undo header, 15=undo block

ba: 0x699be000:buffer在内存中的物理地址,下面我dump buffer cache中的数据,然后寻找699be000,看能不着找到6号文件135号块:

SQL>  alter session set events 'immediate trace name buffers level 2';

Session altered.

再一次注意,我这里level是2

SQL> select * from v$diag_info where name='Default Trace File';

   INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
         1 Default Trace File
/u01/oracle/diag/rdbms/node1/node1/trace/node1_ora_17806.trc

产生的dump文件为:/u01/oracle/diag/rdbms/node1/node1/trace/node1_ora_17806.trc

我们在dump文件中查找上面我们查询出来的ba值,699be000

[oracle@node2 admin]$ vi /u01/oracle/diag/rdbms/node1/node1/trace/node1_ora_17806.trc
Trace file /u01/oracle/diag/rdbms/node1/node1/trace/node1_ora_17806.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/oracle/app
System name:    Linux
Node name:      node2
Release:        3.10.0-229.el7.x86_64
Version:        #1 SMP Fri Mar 6 11:36:42 UTC 2015
Machine:        x86_64
Instance name: node1
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 17806, image: oracle@node2 (TNS V1-V3)


*** 2016-05-24 04:02:13.962
*** SESSION ID:(1.7) 2016-05-24 04:02:13.962
*** CLIENT ID:() 2016-05-24 04:02:13.962
*** SERVICE NAME:(SYS$USERS) 2016-05-24 04:02:13.962
*** MODULE NAME:(sqlplus@node2 (TNS V1-V3)) 2016-05-24 04:02:13.962
*** ACTION NAME:() 2016-05-24 04:02:13.962

Dump of buffer cache at level 2 for tsn=2147483647 rdba=0
BH (0x6c3eb278) file#: 1 rdba: 0x00411e40 (1/73280) class: 1 ba: 0x6c206000
  set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 79,28
  dbwrid: 0 obj: 250 objn: 250 tsn: 0 afn: 1 hint: f
  hash: [0x77b8d248,0x77b8d248] lru: [0x6c3eb490,0x6c3eb230]
  ckptq: [NULL] fileq: [NULL] objq: [0x6c7ea1b8,0x6c3eb258] objaq: [0x6c7ea1c8,0x6c3eb268]
  st: XCURRENT md: NULL fpin: 'kdswh05: kdsgrp' tch: 1
  flags:
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
/6C09A000

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0006.008.000003bd  0x00c18274.0097.0a  --U-    1  fsc 0x0000.000ebf1c
BH (0x6c3dda58) file#: 6 rdba: 0x01800087 (6/135) class: 1 ba: 0x6c09a000
  set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 79,28
  dbwrid: 0 obj: 76863 objn: 76862 tsn: 7 afn: 6 hint: f
  hash: [0x77bdb4b8,0x77bdb4b8] lru: [0x6c3ddc70,0x6c3dda10]
  ckptq: [NULL] fileq: [NULL] objq: [0x6c3ddc98,0x74257500] objaq: [0x6c3ddca8,0x742574f0]
  st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 2
  flags: only_sequential_access
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
  buffer tsn: 7 rdba: 0x01800087 (6/135)
  scn: 0x0000.00196957 seq: 0x01 flg: 0x06 tail: 0x69570601
  frmt: 0x02 chkval: 0x0620 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000006C09A000 to 0x000000006C09C000--注意这里内存地址开始的位置,卧槽,找到了
06C09A000 0000A206 01800087 00196957 06010000  [........Wi......]
06C09A010 00000620 000E0001 00012C3F 00175702  [ .......?,...W..]
06C09A020 1FE80000 00320002 01800080 001D0001  [......2.........]
06C09A030 0000035F 00C00C9E 002C00AB 00002001  [_.........,.. ..]
06C09A040 00175703 00140003 0000046C 00C00332  [.W......l...2...]
------------部分省略--------------------------
06C09BF90 770702C1 6F736C69 022C316E 0CC10202 [...wilson1,.....]
06C09BFA0 6D6E6508 6365746F 02012C68 080BC102 [.enmotech,......]
06C09BFB0 736C6977 30316E6F 0202002C 770704C1 [wilson10,......w]
06C09BFC0 6F736C69 023C336E 04C10202 6C697707 [ilson3<......wil]
06C09BFD0 336E6F73 0202002C 770603C1 6F736C69 [son3,......wilso]
06C09BFE0 02002C6E 0602C102 736C6977 013C6E6F [n,......wilson<.]
06C09BFF0 02C10202 6C697707 316E6F73 69570601 [.....wilson1..Wi]
Block header dump: 0x01800087
 Object id on Block? Y
 seg/obj: 0x12c3f csc: 0x00.175702 itc: 2 flg: E typ: 1 - DATA
 brn: 0 bdba: 0x1800080 ver: 0x01 opc: 0
 inc: 0 exflg: 0

 Itl Xid Uba Flag Lck Scn/Fsc
06C09BF70 0202013C 770702C1 6F736C69 013C316E [<......wilson1<.]
06C09BF80 02C10202 6C697707 316E6F73 0202013C [.....wilson1<...]
06C09BF90 770702C1 6F736C69 022C316E 0CC10202 [...wilson1,.....]
06C09BFA0 6D6E6508 6365746F 02012C68 080BC102 [.enmotech,......]--注意这里真的出现了enmotech,好玩不?
06C09BFB0 736C6977 30316E6F 0202002C 770704C1 [wilson10,......w]
06C09BFC0 6F736C69 023C336E 04C10202 6C697707 [ilson3<......wil]
06C09BFD0 336E6F73 0202002C 770603C1 6F736C69 [son3,......wilso]
06C09BFE0 02002C6E 0602C102 736C6977 013C6E6F [n,......wilson<.]
06C09BFF0 02C10202 6C697707 316E6F73 69570601 [.....wilson1..Wi]
Block header dump: 0x01800087
 Object id on Block? Y
 seg/obj: 0x12c3f csc: 0x00.175702 itc: 2 flg: E typ: 1 - DATA
 brn: 0 bdba: 0x1800080 ver: 0x01 opc: 0
 inc: 0 exflg: 0

 Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.01d.0000035f 0x00c00c9e.00ab.2c --U- 1 fsc 0x0000.00175703
0x02 0x0003.014.0000046c 0x00c00332.00d4.1b --U- 1 fsc 0x0000.00196957
BH (0x69bdcfa8) file#: 2 rdba: 0x008121db (2/74203) class: 1 ba: 0x69888000
------------部分省略--------------------------



SQL> select to_number('6C09C000','xxxxxxxxxxxxxxxxxxxxx')-to_number('6C09A000','xxxxxxxxxxxxxxxxxxxxx') from dual;

TO_NUMBER('6C09C000','XXXXXXXXXXXXXXXXXXXXX')-TO_NUMBER('6C09A000','XXXXXXXXXXXX
--------------------------------------------------------------------------------
 8192

上面说明内存中0x000000006C09A000 to 0x000000006C09C000刚好是一个block的大小,这个block正是6号文件135号块

set: 3 pool: 3:目前还不是清楚这两个是什么意思!!求高人补充

bsz: 8192:block size为8192

bsi: 0 :

sflg: 1

pwc: 0,25
dbwrid: 0:dbwr进程的id

obj: 76863 :data object id

5

objn: 76862 :object id

6

tsn: 7:tablespace

afn: 6

hint: f
hash: [0x77bdb4b8,0x77bdb4b8]:hash chain的上一个bh和下一个bh

lru: [0x69be90f0,0x6b3dd7b0]:lru列表
lru-flags: hot_buffer:lru列表的标记(关于lru后继再另外介绍)
ckptq: [NULL]:checkpoint列表

fileq: [NULL]:file sequence

objq: [0x6d3e8b28,0x742b54c0]:

objaq: [0x69be9128,0x6abd8e48]
st: XCURRENT:当前读(多版本读的知识)

md: NULL

fpin: ‘kdswh11: kdst_fetch’

tch: 8:tech count
flags: block_written_once redo_since_read:标签
LRBA: [0x0.0.0]:low rba

LSCN: [0x0.0]:low scn

HSCN: [0xffff.ffffffff]:hight scn

HSUB: [1]:

上面的过程展示了一次逻辑读!与此同时写到这里突然感觉要写的东西很多,有点偏离主题了,所以这里就不再展开了!只要能从上面的了解到oracle读取块的过程就算达到目的了!

2.造成abc latch的原因

这里我们思考一个问题,因为buffer area是在SGA中的,SGA是全局共享的资源,共享的东西必然会面临并发的问题,哪么cbc是怎么控制并发的呢?

要回答上面这个问题,我先回答下面这个问题。什么样的共享资源才真正要考虑并发控制?答案是对共享资源做变更或更新操作的时候才要考虑并发控制,如果一个共享资源是只读的,哪么我们可能完全不用考虑并发控制。在oracle在扫描cbc链表的时候,无非两种结果,要么在cbc链表上找到了块对应的bh,要么在cbc链表上没有没找到对应block的bh;找到了就发生逻辑读,没找到就发生物理读!当在cbc链表上找到了块对应的bh,哪么要更新bh中一些信息,例如tch,xcurrent等信息,此时我们可以理解为这个block所在的cbc信息要发生变更;当在cbc链表上没找到块对应的bh,哪么发生物理读,要将这个新的块的bh挂到对应hash bucket上的cbc链表上,我们同样可以认为cbc的信息要发生变更!除此之外,无论是哪种情况,在oracle扫描cbc链表的时候,需要保证cbc链表不再发生变化。哪么oracle在发生cbc链表信息变更的时候和保证在扫描cbc链表cbc链表的信息不发生变化的时候就需要控制并发,怎么实现这个并发控制呢?(又绕到上面的问题了,哈哈),好吧,请看下图:

5

熟话说擒贼先擒王,在对cbc链表并发控制的方法上oracle体现了这一点,哈哈!!当cbc链表信息需要发生变更的时候,oracle从源头上去控制。它会先在cbc对应的hash bucket上加一个独占的latch,这个latch就是大名鼎鼎的cbc latch,在某个session加完这个latch后就可以放心的去扫描cbc链表或者去变更cbc链表的信息了。到这里算是回答了上面提出的问题!!哪么在此时若有其他的session也需要去扫描或者变更上面session加latch的hash bucket上的cbc链表的时候就会发生cbc latch争用或者等待。

接着我们关注一下上面查询中hladdr这个东东;6

这个hladdr就是上面我们说的abc latch在内存中的地址。通常我们说一个cbc latch可能需要和多个hash bucket相关的,也就是cbc latch和hash bucket时1对多的关系,这个我们从下面的两个隐含参数中看出来!

3.pic

从上面的结果我们可以看出来,一个cbc latch要平均负责管理32个hash bucket,当然了这个跟机器的配置时相关的。

到这里我觉着可以总结下造成cbc latch的原因了:

(1)热块:如果某个热块同时多个session访问,哪么这些session必然要请求同一个cbc latch,这必然会引起cbc latch

(2)热链:这里说个热链是指某个cbc链表,如果某个cbc链表同时多个session访问,哪么这些session必然会请求同一个cbc latch,这必然会引起cbc latch

(3)hash bucket太小:如果hash bucket比较小,哪么多个session访问的数据可能就会被散列到同一个hash bucket上,哪么他们请求的cbc latch也会时同一个,这也必然会引起cbc latch

(4)hash latch太少:如果hash latch太少,哪么平均每一个hash latch就要负责管理维护的hash bucket就越多,哪么请求同一个latch的几率就会增大,这就增加了cbc latch的机会

至此,关于造成abc latch的原因也算说完了,这里好像上面的有一句话还没有解释,我说latch都是在share pool中分配的,下面搞出一个share pool的dump就一目了然了:

SQL> select * from v$diag_info where name='Default Trace File';

 INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
 1 Default Trace File
/u01/oracle/diag/rdbms/node1/node1/trace/node1_ora_6764.trc



[root@node2 ~]# vi /u01/oracle/diag/rdbms/node1/node1/trace/node1_ora_6764.trc
Trace file /u01/oracle/diag/rdbms/node1/node1/trace/node1_ora_6764.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/oracle/app
System name: Linux
Node name: node2
Release: 3.10.0-229.el7.x86_64
Version: #1 SMP Fri Mar 6 11:36:42 UTC 2015
Machine: x86_64
Instance name: node1
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 6764, image: oracle@node2 (TNS V1-V3)


*** 2016-05-28 05:41:42.833
*** SESSION ID:(1.19) 2016-05-28 05:41:42.833
*** CLIENT ID:() 2016-05-28 05:41:42.833
*** SERVICE NAME:(SYS$USERS) 2016-05-28 05:41:42.833
*** MODULE NAME:(sqlplus@node2 (TNS V1-V3)) 2016-05-28 05:41:42.833
*** ACTION NAME:() 2016-05-28 05:41:42.833

KGH Latch Directory Information
ldir state: 2 last allocated slot: 97
Slot [ 1] Latch: 0x6000a018 Index: 1 Flags: 3 State: 2 next: (nil)
Slot [ 2] Latch: 0x77a4fbb0 Index: 1 Flags: 3 State: 2 next: (nil)
Slot [ 3] Latch: 0x77a4fc90 Index: 1 Flags: 3 State: 2 next: (nil)
Slot [ 4] Latch: 0x6000e398 Index: 1 Flags: 3 State: 2 next: (nil)
Slot [ 5] Latch: 0x776f0950 Index: 1 Flags: 3 State: 2 next: (nil)
Slot [ 6] Latch: 0x776f2b10 Index: 1 Flags: 3 State: 2 next: (nil)
Slot [ 7] Latch: 0x776f3890 Index: 1 Flags: 3 State: 2 next: (nil)
Slot [ 8] Latch: 0x776f4610 Index: 1 Flags: 3 State: 2 next: (nil)
Slot [ 9] Latch: 0x776f4cd0 Index: 1 Flags: 3 State: 2 next: (nil)
Slot [ 10] Latch: 0x776f5390 Index: 1 Flags: 3 State: 2 next: (nil)
Slot [ 11] Latch: 0x776f5a50 Index: 1 Flags: 3 State: 2 next: (nil)
Slot [ 12] Latch: 0x776f6110 Index: 1 Flags: 3 State: 2 next: 0x1
Slot [ 13] Latch: 0x776f6e90 Index: 1 Flags: 3 State: 2 next: (nil)
Slot [ 14] Latch: 0x776f7550 Index: 1 Flags: 3 State: 2 next: (nil)
Slot [ 15] Latch: 0x776f9dd0 Index: 1 Flags: 3 State: 2 next: (nil)
Slot [ 16] Latch: 0x6000f538 Index: 1 Flags: 3 State: 2 next: 0x60107550
Slot [ 17] Latch: 0x60016098 Index: 1 Flags: 3 State: 2 next: 0x60107418
Slot [ 18] Latch: 0x60015fd0 Index: 1 Flags: 3 State: 2 next: (nil)
Slot [ 19] Latch: 0x6001b428 Index: 1 Flags: 3 State: 2 next: (nil)
Slot [ 20] Latch: 0x6001be30 Index: 1 Flags: 3 State: 2 next: (nil)
Slot [ 21] Latch: 0x6001e0e0 Index: 1 Flags: 3 State: 2 next: 0x60107598
Slot [ 22] Latch: 0x6001fb28 Index: 1 Flags: 3 State: 2 next: 0x60107430
--------------下面省略-------------------------

从dump文件中可以明显的看出latch的信息

 

3.手工的模拟cbc latch

这部分我准备收工的模拟一次cbc latch,模拟一个热块的情况!希望起到一个抛砖引玉的作用:

SQL> select HLADDR from x$bh where file#=6 and dbablk=135;

HLADDR           
---------------- 
0000000077BDAEB8

查询6号文件的135号块的hladdr

SQL> select file#,dbablk,owner,data_object_id,object_name,object_type from x$bh a,dba_objects b where hladdr='0000000077BDAEB8' and a.obj=b.data_object_id AND OWNER='DEMO';

     FILE#     DBABLK OWNER      DATA_OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
---------- ---------- ---------- -------------- ------------------------------ -------------------
         6        135 DEMO                76863 LL_T1                          TABLE

查看6号文件135号块的data_object_id

SQL> select DBMS_ROWID.ROWID_CREATE(1,76863,6,135,13) from dual;

DBMS_ROWID.ROWID_C
------------------
AAASw/AAGAAAACHAAN

SQL> select DBMS_ROWID.ROWID_CREATE(1,76863,6,135,14) from dual;

DBMS_ROWID.ROWID_C
------------------
AAASw/AAGAAAACHAAO

SQL> select DBMS_ROWID.ROWID_CREATE(1,76863,6,135,15) from dual;

DBMS_ROWID.ROWID_C
------------------
AAASw/AAGAAAACHAAP

构造出三个6号文件135号块上的rowid


SQL> select * from demo.ll_t1 where rowid='AAASw/AAGAAAACHAAN';

 ID NAME
---------- --------------------
 20 enmotech20

SQL> select * from demo.ll_t1 where rowid='AAASw/AAGAAAACHAAO';

 ID NAME
---------- --------------------
 21 enmotech21

SQL> select * from demo.ll_t1 where rowid='AAASw/AAGAAAACHAAP';

 ID NAME
---------- --------------------
 22 enmotech22

验证rowid是否正确

session 1

SQL> select userenv('sid') from dual;

USERENV('SID')
--------------
 1

SQL> declare
 2 dbnum1 VARCHAR2(20);
 3 begin
 4 for i in 1 ..10000000 loop
 5 select NAME into dbnum1 from DEMO.LL_T1 where rowid='AAASw/AAGAAAACHAAN';
 6 end loop;
 7 end;
 8 /

在session1中执行上面的匿名块,非常频繁的访问6号文件的135号块

session 2

SQL> select userenv('sid') from dual;

USERENV('SID')
--------------
 31

SQL> declare
 2 dbnum1 VARCHAR2(20);
 3 begin
 4 for i in 1 ..10000000 loop
 5 select NAME into dbnum1 from DEMO.LL_T1 where rowid='AAASw/AAGAAAACHAAO';
 6 end loop;
 7 end;
 8 /


在session2中执行上面的匿名块,非常频繁的访问6号文件的135号块


session 3

SQL> select userenv('sid') from dual;

USERENV('SID')
--------------
 27

SQL> declare
 2 dbnum1 VARCHAR2(20);
 3 begin
 4 for i in 1 ..10000000 loop
 5 select NAME into dbnum1 from DEMO.LL_T1 where rowid='AAASw/AAGAAAACHAAP';
 6 end loop;
 7 end;
 8 /


在session1中执行上面的匿名块,非常频繁的访问6号文件的135号块


session 4


SQL> select userenv('sid') from dual;

USERENV('SID')
--------------
 35

SQL> select sid,event,p1raw,p2raw from v$session where wait_class<>'Idle' order by 2;

 SID EVENT P1RAW P2RAW
---------- ------------------------------ ---------------- ----------------
 35 SQL*Net message to client 0000000062657100 0000000000000001
 31 latch: cache buffers chains 0000000077BDAEB8 000000000000009B
 1 latch: cache buffers chains 0000000077BDAEB8 000000000000009B
 27 latch: cache buffers chains 0000000077BDAEB8 000000000000009B


在session 4中监控等待时间,看见了session 1,session 2和session 3中均产生了cbc latch

我构造了6号文件的135号块为一个热块,在三个session分别非常频繁的访问这个块,这三个session会非常非常频繁的去申请同一个cbc latch,当其中一个session申请cbc latch的时候,另外的一个session还没有释放cbc latch的时候,就会产生cbc latch!!

至此,cbc latch算是说完了,至于怎么处理出cbc latch,我相信知道了cbc latch的原理,解决abc latch应该就不是问题了,我也就不啰嗦了!

记一次OPatch failed with error code 73的处理过程

搞数据库运维的同学们打补丁这活儿想必是少不了的,那么你也肯定在OPath时遇到过各种各样的错误,今天就记录一次我遇到OPatch failed with error code 73的处理过程。

客户有一套4节点RAC,需要打patch 21539301解决Bug 5629423,让oracle在Impdp时可以同时创建多个索引,信息如下:

The creation of indexes in parallel has been addressed in Enhancement Request
Bug 5629423 - DATAPUMP DOES NOT CREATE THE INDEXES IN PARALLEL
This enhancement was implemented as a bug fix. Please download generic patch 21539301 (available for 12.1.0.2 and 11.2.0.4).

按部就班的去下载补丁、上传、解压、更新OPatch版本、冲突检测,一切OK。因为这是个one-off patch,且停机时间自由,所以采用在1节点$ORACLE_HOME/OPatch/opatch applay,给四个节点一起打,rac1很快成功完成,但当给远程节点应用时卡住了,一检查发现rac4被干掉了,整个系统都没了,导致了这个问题。

Rac system comprising of multiple nodes
  Local node = rac1
  Remote node = rac2
  Remote node = rac3
  Remote node = rac4

那么终止掉卡住的OPatch进程,去rac2,rac3上检查,补丁确实没打上。同时OPatch各种命令均报OPatch failed with error code 73

[oracle@rac3 21539301]$ $ORACLE_HOME/OPatch/opatch lsinventory 
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2016, Oracle Corporation.  All rights reserved.


Oracle Home       : /home/app/oracle/product/11.2.0/db_1
Central Inventory : /home/app/oraInventory
   from           : /home/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version    : 11.2.0.3.12
OUI version       : 11.2.0.4.0
Log file location : /home/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2016-05-10_23-58-36PM_1.log



Inventory load failed... OPatch cannot load inventory for the given Oracle Home.

LsInventorySession failed: Unable to create patchObject
Possible causes are:
   ORACLE_HOME/inventory/oneoffs/21539301 is corrupted. 
   No read permission to above directory
Please remove one-off entry [ 21539301 ] from /home/app/oracle/product/11.2.0/db_1/inventory/ContentsXML/comps.xml and retry if above directory corrupted, or Please check the contents of the directory ORACLE_HOME/inventory/oneoffs/21539301 for read permission

OPatch failed with error code 73

仔细看看报错,说的很明确,刚才半拉子场景导致了inventory的不一致,那么我们去修改comps.xml文件:

[oracle@rac3 21539301]$ cp /home/app/oracle/product/11.2.0/db_1/inventory/ContentsXML/comps.xml /home/app/oracle/product/11.2.0/db_1/inventory/ContentsXML/comps.xml.bak20160511 <----先备份再动手,养成好习惯
[oracle@rac3 21539301]$ vi /home/app/oracle/product/11.2.0/db_1/inventory/ContentsXML/comps.xml
<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2013, Oracle and/or its affiliates.
All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<PRD_LIST>
<TL_LIST>
<COMP NAME="oracle.server" VER="11.2.0.4.0" BUILD_NUMBER="0" REP_VER="0.0.0.0.0" RELEASE="Production" INV_LOC="Components/oracle.server/11.2.0.4.0/1/" LANGS="en zh_CN" XML_INV_LOC="Components21/oracle.server/11.2.0.4.0/" ACT_INST_VER="11.2.0.4.0" DEINST_VER="11.2.0.0.0" INSTALL_TIME="2016.May.07 21:50:43 CST" INST_LOC="/home/app/oracle/product/11.2.0/db_1/oracle.server">
   <EXT_NAME>Oracle Database 11g</EXT_NAME>
   <DESC>Installs an optional preconfigured starter database, product options, management tools, networking services, utilities, and basic client software for an Oracle Database server. This option also supports Automatic Storage Management database configuration.</DESC>
   <DESCID>COMPONENT_DESC</DESCID>
   <STG_INFO OSP_VER="10.2.0.0.0"/>
   <CMP_JAR_INFO>
      <INFO NAME="filemapObj" VAL="Components/oracle/server/v11_2_0_4_0/filemap.xml"/>
      <INFO NAME="helpDir" VAL="Components/oracle/server/v11_2_0_4_0/help/"/>
      <INFO NAME="actionsClass" VAL="Components.oracle.server.v11_2_0_4_0.CompActions"/>
      <INFO NAME="resourceClass" VAL="Components.oracle.server.v11_2_0_4_0.resources.CompRes"/>
      <INFO NAME="identifiersXML" VAL="Components/oracle/server/v11_2_0_4_0/identifiers.xml"/>
      <INFO NAME="contextClass" VAL="Components.oracle.server.v11_2_0_4_0.CompContext"/>
      <INFO NAME="fastCopyLogXML" VAL="Components/oracle/server/v11_2_0_4_0/fastCopyLog.xml"/>
   </CMP_JAR_INFO>
   <LOC_INFO INST_DFN_LOC="../Scripts" JAR_NAME="install1.jar"/>
   <BOOK NAME="oracle.server.hs"/>
   <PRE_REQ DEF="F"/>
   <PROD_HOME DEF="F"/>
   <LANG_IDX_MAP>
      <LANG LIST="en fr ar bn pt_BR bg fr_CA ca hr cs da nl ar_EG en_GB et fi de el iw hu is in it ja ko es lv lt ms es_MX no pl pt ro ru zh_CN sk sl es_ES sv th zh_TW tr uk vi"/>
      <LANGSET IDX="1" BITSET="{0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44}"/>
      <LANGSET IDX="2" BITSET="{2}"/>
      <LANGSET IDX="3" BITSET="{9}"/>
      <LANGSET IDX="4" BITSET="{16}"/>
      <LANGSET IDX="5" BITSET="{10}"/>
      <LANGSET IDX="6" BITSET="{25, 29, 38}"/>
      <LANGSET IDX="7" BITSET="{17}"/>
      <LANGSET IDX="8" BITSET="{1}"/>
      <LANGSET IDX="9" BITSET="{19}"/>
      <LANGSET IDX="10" BITSET="{22}"/>
      <LANGSET IDX="11" BITSET="{18}"/>
      <LANGSET IDX="12" BITSET="{23}"/>
      <LANGSET IDX="13" BITSET="{24}"/>
      <LANGSET IDX="14" BITSET="{30}"/>
      <LANGSET IDX="15" BITSET="{11}"/>
      <LANGSET IDX="16" BITSET="{31}"/>
"/home/app/oracle/product/11.2.0/db_1/inventory/ContentsXML/comps.xml" 7338L, 411919C                                                     1,1           Top
<ONEOFF REF_ID="21539301" UNIQ_ID="19476720" ROLLBACK="T" XML_INV_LOC="oneoffs/21539301/" ACT_INST_VER="11.2.0.4.0" INSTALL_TIME="2016.May.10 23:55:17 CST">
   <DESC></DESC>
   <REF_LIST>
      <REF NAME="oracle.rdbms.dbscripts" VER="11.2.0.4.0" HOME_IDX="2"/>
   </REF_LIST>
   <BUG_LIST>
      <BUG>21539301</BUG>
   </BUG_LIST>
   <FILE_LIST/>
</ONEOFF>
            <DEP NAME="oracle.rdbms.numa" VER="10.1.0.1.0" PLAT="ALL_PLATFORMS"/>
            <DEP NAME="oracle.oraolap" VER="11.2.0.4.0" PLAT="Linux"/>
            <DEP NAME="oracle.ntmgmtobjs" VER="11.2.0.4.0" PLAT="ALL_PLATFORMS"/>
            <DEP NAME="oracle.rdbms.dm" VER="11.2.0.4.0" PLAT="Linux"/>
            <DEP NAME="oracle.clrintg.ode_net_2" VER="11.2.0.4.0" PLAT="ALL_PLATFORMS"/>
            <DEP NAME="oracle.rdbms.dv" VER="11.2.0.4.0" PLAT="Linux"/>
            <DEP NAME="oracle.rdbms.rat" VER="11.2.0.4.0" PLAT="Linux"/>
         </DEP_LIST>
      </DEP_GRP>
   </DEP_GRP_LIST>
   <DEP_LIST>
      <DEP NAME="oracle.network.aso" VER="11.2.0.4.0" DEP_GRP_NAME="Optional" HOME_IDX="2"/>
      <DEP NAME="oracle.rdbms.partitioning" VER="11.2.0.4.0" DEP_GRP_NAME="Optional" HOME_IDX="2"/>
      <DEP NAME="oracle.sdo" VER="11.2.0.4.0" DEP_GRP_NAME="Optional" HOME_IDX="2"/>
      <DEP NAME="oracle.rdbms.lbac" VER="11.2.0.4.0" DEP_GRP_NAME="Optional" HOME_IDX="2"/>
      <DEP NAME="oracle.oraolap" VER="11.2.0.4.0" DEP_GRP_NAME="Optional" HOME_IDX="2"/>
      <DEP NAME="oracle.rdbms.dm" VER="11.2.0.4.0" DEP_GRP_NAME="Optional" HOME_IDX="2"/>
      <DEP NAME="oracle.rdbms.dv" VER="11.2.0.4.0" DEP_GRP_NAME="Optional" HOME_IDX="2"/>
      <DEP NAME="oracle.rdbms.rat" VER="11.2.0.4.0" DEP_GRP_NAME="Optional" HOME_IDX="2"/>
   </DEP_LIST>
   <REF_LIST>
      <REF NAME="oracle.server" VER="11.2.0.4.0" HOME_IDX="2"/>
   </REF_LIST>
   <INST_TYPE_LIST>
      <INST_TYPE NAME="Typical" NAME_ID="Typical" DESC_ID="Typical_DESC"/>
   </INST_TYPE_LIST>
   <FILESIZEINFO>
      <DEST VOLUME="%INVENTORY_LOCATION%" SPACE_REQ="2000"/>
      <DEST VOLUME="%ORACLE_HOME%" SPACE_REQ="4107"/>
   </FILESIZEINFO>
</COMP>
</COMP_LIST>
<ONEOFF_LIST>     <-----删除这个结构里的one-off entry [ 21539301 ]相关信息
</ONEOFF_LIST>
<PATCHSET_UPDATE_LIST>
</PATCHSET_UPDATE_LIST>
</PRD_LIST>
~
~
~
~
~
~
~
~
~
~
"/home/app/oracle/product/11.2.0/db_1/inventory/ContentsXML/comps.xml" 7328L, 411562C written

修改完成后,继续以本地模式opatch,顺利完成。另外一个节点也如此操作。

[oracle@rac3 21539301]$ $ORACLE_HOME/OPatch/opatch apply -local
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2016, Oracle Corporation.  All rights reserved.


Oracle Home       : /home/app/oracle/product/11.2.0/db_1
Central Inventory : /home/app/oraInventory
   from           : /home/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version    : 11.2.0.3.12
OUI version       : 11.2.0.4.0
Log file location : /home/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/21539301_May_11_2016_00_12_19/apply2016-05-11_00-12-19AM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   21539301  

Do you want to proceed? [y|n]
y     
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name: 

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y


Backing up files...
Applying interim patch '21539301' to OH '/home/app/oracle/product/11.2.0/db_1'

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...
Patch 21539301 successfully applied.
Log file location: /home/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/21539301_May_11_2016_00_12_19/apply2016-05-11_00-12-19AM_1.log

OPatch succeeded.

 

ora-01578,ora-01110,ora-26040错误

今天检查alert日志发现了大量的报错,数据库为RAC环境,数据库版本为11.2.0.4.0,日志如下:

Errors in file /oracle/orabase/diag/rdbms/nx_dss/NXDSS1/trace/NXDSS1_j003_27143.trc  (incident=589701):
ORA-01578: ORACLE data block corrupted (file # 657, block # 111610)
ORA-01110: data file 657: '+DG_DATA01/nx_dss/datafile/tbs_neweba_657'
ORA-26040: Data block was loaded using the NOLOGGING option
Incident details in: /oracle/orabase/diag/rdbms/nx_dss/NXDSS1/incident/incdir_589701/NXDSS1_j003_27143_i589701.trc
Fri May 13 22:33:38 2016
Dumping diagnostic data in directory=[cdmp_20160513223338], requested by (instance=1, osid=27143 (J003)), summary=[incident=589701
].
Fri May 13 22:33:38 2016
DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.
Errors in file /oracle/orabase/diag/rdbms/nx_dss/NXDSS1/trace/NXDSS1_j003_27143.trc:
ORA-20011: Approximate NDV failed: ORA-01578: ORACLE data block corrupted (file # 657, block # 111610)
ORA-01110: data file 657: '+DG_DATA01/nx_dss/datafile/tbs_neweba_657'
ORA-26040: Data block was loaded using the NOLOGGING option
Fri May 13 22:33:40 2016
Sweep [inc][589701]: completed
Sweep [inc2][589701]: completed
Errors in file /oracle/orabase/diag/rdbms/nx_dss/NXDSS1/trace/NXDSS1_j003_27143.trc  (incident=589702):
ORA-01578: ORACLE data block corrupted (file # 342, block # 13094)
ORA-01110: data file 342: '+DG_DATA01/nx_dss/datafile/tbs_neweba_342'
ORA-26040: Data block was loaded using the NOLOGGING option
Incident details in: /oracle/orabase/diag/rdbms/nx_dss/NXDSS1/incident/incdir_589702/NXDSS1_j003_27143_i589702.trc
Dumping diagnostic data in directory=[cdmp_20160513223345], requested by (instance=1, osid=27143 (J003)), summary=[incident=589702
].
Errors in file /oracle/orabase/diag/rdbms/nx_dss/NXDSS1/trace/NXDSS1_j003_27143.trc  (incident=589703):

随后使用dbv命令检查上述显示的数据库文件如下:

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     16384
SQL>

 

$jyfx1/home/oracle>dbv userid=sys/oracle file=+DG_DATA01/nx_dss/datafile/tbs_neweba_657 blocksize=16384

DBVERIFY: Release 11.2.0.4.0 - Production on Mon May 16 10:43:02 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = +DG_DATA01/nx_dss/datafile/tbs_neweba_657

DBV-00201: Block, DBA -1539197958, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197622, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197621, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197620, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197606, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197605, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197604, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197602, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197601, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197600, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197586, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197585, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197584, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197582, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197581, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197580, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197574, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197573, marked corrupt for invalid redo application

DBV-00201: Block, DBA -1539197572, marked corrupt for invalid redo application


DBVERIFY - Verification complete

Total Pages Examined         : 519940
Total Pages Processed (Data) : 518066
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 1680
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 194
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 0
Total Pages Marked Corrupt   : 31
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)

如上在输出中出现一部分损坏的块被标记为无效的redo 应用,且database address 地址被标记为(-) ,正常输出应该是如下另外一个数据文件校验输出如下:

$jyfx1/home/oracle>  dbv userid=sys/oracle file=+DG_DATA01/nx_dss/datafile/tbs_dm_2_1153  blocksize=16384

DBVERIFY: Release 11.2.0.4.0 - Production on Mon May 16 15:54:31 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = +DG_DATA01/nx_dss/datafile/tbs_dm_2_1153

DBV-00201: Block, DBA 546059486, marked corrupt for invalid redo application

DBV-00201: Block, DBA 546059487, marked corrupt for invalid redo application

DBV-00201: Block, DBA 546059488, marked corrupt for invalid redo application

DBV-00201: Block, DBA 545470281, marked corrupt for invalid redo application

DBV-00201: Block, DBA 545470282, marked corrupt for invalid redo application

DBV-00201: Block, DBA 545470283, marked corrupt for invalid redo application

DBV-00201: Block, DBA 545470284, marked corrupt for invalid redo application


DBVERIFY - Verification complete

Total Pages Examined         : 968992
Total Pages Processed (Data) : 941489
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 6446
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 428
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 20629
Total Pages Marked Corrupt   : 7
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)
$jyfx1/home/oracle>

这个稍后会做说明

通过如下脚本把DBV检测出的corrupt的block 的database address (DBA)转换为文件号和块号,如下:

CREATE OR REPLACE PROCEDURE cdba ( iblock VARCHAR2, imode VARCHAR2 ) AS

x       NUMBER;
digits# NUMBER;
results NUMBER := 0;
file#   NUMBER := 0;
block#  NUMBER := 0;
cur_digit CHAR(1);
cur_digit# NUMBER;

BEGIN
      IF upper(imode) = 'H' THEN
           digits# := length( iblock );
           FOR x  IN 1..digits#  LOOP
                cur_digit := upper(substr( iblock, x, 1 ));
                IF cur_digit IN ('A','B','C','D','E','F')  THEN
                     cur_digit# := ascii( cur_digit ) - ascii('A') +10;
                ELSE
                     cur_digit# := to_number(cur_digit);
                END IF;
                results := (results *16) + cur_digit#;
           END LOOP;
      ELSE
           IF upper(imode) = 'D' THEN
                results := to_number(iblock);
           ELSE
                dbms_output.put_line('H = Hex Input ... D = Decimal Input');
                RETURN;
           END IF;
      END IF;

      file#  := dbms_utility.data_block_address_file(results);
      block# := dbms_utility.data_block_address_block(results);

      dbms_output.put_line('.');
      dbms_output.put_line( 'The file is ' || file# );
      dbms_output.put_line( 'The block is ' || block# );
END;
/

上述脚本可以在测试环境中建立测试环境中建立然后提取转换,如下:

SQL> connect as owner of procedure cdba(使用创建cdba的存储过程的用户进行登录)
SQL> set serveroutput on
SQL> exec cdba(546059486,’D’);

The file is 130
The block is 799966
PL/SQL procedure successfully completed.
SQL>

使用上述的file number,block number 查找对象属性,如下:

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = 130
and 799966 between block_id AND block_id + blocks – 1;

如果是表,查看表的logging 属性。

select owner, table_name, logging
from dba_tables
where table_name in
(‘<list of tables from previous query>’);

如果是索引,如下查看logging属性。

select owner, index_name, logging, table_owner, table_name
from dba_indexes
where index_name in (‘<list of indexes from previous query>’);

在nologging 属性的对象上执行恢复,会导致在dbv校验时输出DBV-201错误,在查询查询对象时,会在alert日志文件中报类似于如下的错误:

ORA-01578: ORACLE data block corrupted (file # 657, block # 111610)
ORA-01110: data file 657: ‘+DG_DATA01/nx_dss/datafile/tbs_neweba_657’
ORA-26040: Data block was loaded using the NOLOGGING option

类似于报上述错误的对象,需要对对象进程重建,并在重建的过程中更改对象logging 属性为LOGGING=YES。(如果想不在出现这种逻辑上的错误再次发生,需要进行更改,可以使用CTAS,新建表默认logging=yes),一般情况下,这种nologging 大多数是临时表,像上述中DBA -1539197604,这种情况,是块的所属对象已经被删除的结果。

Oracle 官方文档:ID 453278.1, ID 113005.1。

 

PGA的简单理解

李 磊   2016年5月16日   PGA的简单理解无评论

这是我之前写的一片博客,今天晚上又读了一遍,有不对的地方希望大家指正!

认识PGA
PGA是特定于进程或者线程的一段内存,换句话说,它是一个操作系统进程或线程专用的内存,不允许系统的其他它进程(线程)访问。PGA一般通过C语言的运行时调用malloc()或memmap()来分配,而且可以在运行时动态扩大和缩小,PGA肯定不会在SGA中分配。PGA是由进程或者线程在本地分配的。PGA中的P代表process或者program,是不共享的。PGA是通过内存heap来管理的,它包含专用和共享服务器进程相关的会话信息。
实际上,对你来说,UGA就是你的会话的状态。会话总能访问这部分内存。UGA的位置完全取决于怎么连接oracle。如果是共享模式连接oracle。UGA肯定存储在每个共享服务器能够访问的内存结构中,也即是SGA中。如果使用专用模式连接oracle,则不再需要大家都能访问你的会话状态,UGA几乎成了PGA的同义词,实际上UGA就包含在PGA中。
1. PGA结构
我们先来看看PGA的结构,如下图(来自Oracle联机文档):

28687558_1431418125o0A3

PGA包含工作区,会话区,和私有sql区,而sql work areas分为sort area、hash area、bitmap merge area。session memory主要包含一些登陆的信息。Private sql area分为persisten area和runtime area。
1.1. Sql work areas
Work areas是PGA中分配的,用于一些基于内存的操作,例如排序,哈希和位图合并,如果基于内存的操作(sort,hash,merge,group by)的数据超过了work area的空间,oracle自动会将数据分为若干个小的数据片,然后在work area中分别处理每个数据片,其他暂时还没有处理的数据片则存放在temp表空间上,以待稍后处理。当开启PGA自动管理时,数据库可以自动的调整work area的大小,当然了你也可以手动的管理PGA,这就需要你手工的设置一些参数了。一般而言,work area越大,需要消耗大量内存的操作性能越好,如果使用了PGA的自动管理,则oracle会根据要处理的数据量来动态的调整work area的大小,来尽量满足数据所需的内存,尽可能地保证所有的操作在内存中完成。相反如果没有使用PGA自动管理,work area的相关参数的大小相对于输入的数据来说比较小的话,oracle不得不把一些数据缓存在磁盘上,这可能极大地增大了响应时间。
1.2. Private sql area
Private sql area保存了一些解析语句的信息和会话相关的信息,当一个服务器进程执行sql或者pl/sql代码,服务器进程使用private sql area存储绑定变量、查询执行状态信息等信息。当使用shared连接模式,private sql area是在UGA中的,而UGA是在SGA(large pool)中分配的。多个不同会话的private sql area可以指向同一个执行计划。例如20号会话执行了select * from employees,10号会话也执行了同样的语句,这两个会话可以共享相同的执行计划。但是每个private sql area可能包含不同的变量和数据。游标是特定的private sql area的名字或者句柄。你可以认为游标就是客户端指向服务端的一个指针,因为游标和private sql area和游标是紧密联系的,它们有时是可以互换的。

2

Private sql area可以划分为下面几个部分:
The run-time area
这个区域包含了查询执行的状态信息,例如 run-time area记录了全表扫描在当前时间返回的行数。分配Run-time area是oracle数据库处理执行请求的第一步。对于DML语句,sql语句关闭后run-time area就释放了。
The Persisten area
这个区域包含了绑定变量。当sql 语句在执行的时候将绑定变量的值赋给语句。只有在游标关闭的时候persisten area才会释放。客户端进程负责管理private sql area。Private sql area的分配和重分配在很大程度上是依赖应用的,但是客户端可以分配的private sql area是受初始化参数open_cursors限制的。

2.PGA 的管理
PGA的管理有两种方式,一种是自动管理,一种是手动管理,下面我们就这两种管理方式展开讨论。workarea_size_policy就是配置PGA是手工管理还是自动管理的参数,如果WORKAREA_SIZE_POLICY=AUTO则PGA就是自动管理,WORKAREA_SIZE_POLICY=MANUL则PGA就是手工管理的。其实我们无论使用自动管理还是手工管理都是针对work area区域的。
2.1.PGA的手工管理
WORKAREA_SIZE_POLICY=MANUL则PGA就是手工管理,这个时候我们就需要手工的配置像SORT_AREA_SIZE,HASH_AREA_SIZE,BITMAP_MERGE_AREA_SIZE,SORT_AREA_RETAINED_SIZE这些参数了,我们每个session能使用的PGA(work area)大小就由这些参数来决定。下面通过实验来验证。
SQL> show parameter workarea_size_policy
NAME                                  TYPE     VALUE
———————————— ———– ——————————
workarea_size_policy          string     AUTO
SQL> alter system set workarea_size_policy=manual scope=spfile;(注意:在设置PGA的手工管理的时候,我们要先把Oracle自动内存管理关闭)
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size 2232840 bytes
Variable Size 1879051768 bytes
Database Buffers 1392508928 bytes
Redo Buffers 16551936 bytes
Database mounted.
Database opened.
SQL> select count(*) from tt;
COUNT(*)
———-
603288
SQL> select count(*) from tt1;
COUNT(*)
———-
603288
SQL> show parameter sort_area_size
NAME                                  TYPE     VALUE
———————————— ———– ——————————
sort_area_size                    integer    65536
SQL> show parameter hash_area_size
NAME                                  TYPE      VALUE
———————————— ———– ——————————
hash_area_size                   integer    131072
SQL> show parameter bitmap_merge_area_size
NAME                                  TYPE     VALUE
———————————— ———– ——————————
bitmap_merge_area_size    integer   1048576
SQL> show parameter sort_area_retained_size
NAME                                  TYPE     VALUE
———————————— ———– ——————————
sort_area_retained_size      integer   0
SQL> select userenv(‘sid’) from dual;
USERENV(‘SID’)
————–
32
SQL> set termout off
SQL> select * from tt1,tt order by 1,2,3,4,5,6,7;
在另外一个session中监测32号session:
SQL> select n.name,s.value from v$sesstat s,v$statname n where s.statistic#=n.statistic# and s.sid=32 and n.name like ‘%sort%’;
NAME                           VALUE
—————————— ———-
sorts (memory)             0
sorts (disk)                   1
sorts (rows)                  603288
SQL> select sid,pga_used_mem/1024/1024 used_mem,pga_alloc_mem/1024/1024 allocate_mem,pga_freeable_mem/1024/1024 free_mem,pga_max_mem/1024/1024 max_mem from v$process p,v$session s where sid=32 and p.addr=s.paddr;
SID        USED_MEM ALLOCATE_MEM    FREE_MEM      MAX_MEM
———- ———-           ————                   ———-             ———-
32         2.99741364    5.54946136              1.1875              5.54946136
在32号会话一直执行排序操作的sql语句时,我多次执行上面的sql语句发现max_mem在5.5M左右后保持不变了,这说明当使用manual方式管理PGA(workarea)时,它可用的大小就是由上面我们提到那些参数决定的。
2.1.1 手工管理PGA的调优
当使用手工管理的PGA时,优化PGA我们主要使用两个动态性能视图,v$pga_target_advice和v$pga_target_advice_histogram,具体的对这两个动态性能试图的字段介绍我们可以查看Oracle的联机文档。
2.2.PGA的自动管理
当workarea_size_policy=auto时,PGA是自动管理的,这个时候我们不需要手动的设置像sort_area_size这些参数,而只是简单的设置一个pga_aggregate_target参数,具体的PGA内部各个组件的大小由Oracle自身来决定,理论上pga_aggregate_target为所有会话使用pga的一个阀值,但是当所有会话需要的PGA总值超过这个阀值时,Oracle并不会停止工作,而是会超过这个阀值。单个会话可以使用的最大的PGA大约为(默认)pga_aggregate_target*0.2,当这个session中有多个并行的process的时候,那么每个并行的进程可以使用的PGA大约为pga_aggregate_target*0.2/n,其中n为并行的进程数。我来解释一下上一句话中括号里“默认”的含义,其实单个会话可以使用的最大的PGA是由Oracle隐含参数_pga_max_size决定的,而这个参数默认的值就是PGA_AGGREGATE_TARGET*0.2。另外一个隐含参数_smm_max_size是决定在使用自动方式管理PGA的时候,最大的workarea的size,下面我们使用实验来验证:
SQL> show parameter workarea_size_policy
NAME                                  TYPE     VALUE
———————————— ———– ——————————
workarea_size_policy          string     AUTO
SQL> show parameter pga_agg
NAME                                  TYPE     VALUE
———————————— ———– ——————————
pga_aggregate_target big   integer   1G
SQL> select n.ksppinm,v.ksppstvl,n.ksppdesc from x$ksppi n,x$ksppcv v where n.indx=v.indx and n.ksppinm like ‘_smm_max_size%’;
KSPPINM               KSPPSTVL     KSPPDESC
——————–       ———-            ————————————————————
_smm_max_size    104850            maximum work area size in auto mode (serial)
SQL> select n.ksppinm,v.ksppstvl,n.ksppdesc from x$ksppi n,x$ksppcv v where n.indx=v.indx and n.ksppinm like ‘_pga_max_size%’;
KSPPINM            KSPPSTVL        KSPPDESC
——————–    ———-              ————————————————————
_pga_max_size   214732800        Maximum size of the PGA memory for one process
SQL> select userenv(‘sid’) from dual;
USERENV(‘SID’)
————–
32
在32号会话中我们执行下面的sql:(其中表tt,tt1是我构造的两个表,表中各有进60万条数据)
SQL> select * from tt,tt1 order by 1,2,3;
我们在另外一个会话中监测32号会话,执行下面的语句来查看pga的使用情况:
SQL> select sid,pga_used_mem/1024/1024 used_mem,pga_alloc_mem/1024/1024 allocate_mem,pga_freeable_mem/1024/1024 free_mem,pga_max_mem/1024/1024 max_mem from v$process p,v$session s where sid=34 and p.addr=s.paddr;

SID       USED_MEM     ALLOCATE_MEM    FREE_MEM   MAX_MEM
———- ———-             ————                  ———-            ———-
34         188.844437      190.299461             1.1875             190.299461
可以看出最大的使用最大的pga为190M,基本上是等于隐含参数_pag_max_size的
接下来我们修改_pga_max_size的值,再来观察pga的使用情况
SQL> alter system set “_pga_max_size”=500M scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 503318728 bytes
Database Buffers 1627389952 bytes
Redo Buffers 4947968 bytes
Database mounted.
Database opened.
SQL> select userenv(‘sid’) from dual;
USERENV(‘SID’)
————–
1
SQL> select * from tt,tt1 order by 1,2,3,4;
我们在另外一个会话中观察1号会话PGA的使用情况
SQL> select sid,pga_used_mem/1024/1024 used_mem,pga_alloc_mem/1024/1024 allocate_mem,pga_freeable_mem/1024/1024 free_mem,pga_max_mem/1024/1024 max_mem from v$process p,v$session s where sid=1 and p.addr=s.paddr;
SID       USED_MEM   ALLOCATE_MEM   FREE_MEM    MAX_MEM
———- ———-           ————                  ———-            ———-
1           289.336327     295.861961            5.5                   295.861961
这个使用单个会话使用的最大PGA已经变成了300M左右。
3.private sql area
接下来我们再关注一下另外两个参数open_cursors和session_cached_cursors,这两个参数都是与private area相关的(非工作区)
SQL> SHOW PARAMETER cursors
NAME                                 TYPE      VALUE
———————————— ———– ——————————
open_cursors                        integer  50
session_cached_cursors      integer  50
我们在1号session中执行下面的匿名块:
declare
v_str varchar2(200);
begin
for i in 1 .. 30000000
loop
for j in 1 .. 5
loop
execute immediate ‘select * from tt where id=’|| i;
–execute immediate v_str;
end loop;
end loop;
end;
/
在另外一个会话中执行下面的语句,查询1号会话缓存的cursor
SQL> select count(*) from v$open_cursor where cursor_type LIKE ‘%CACHED%’ AND SID=41;
COUNT(*)
———-
50
其实查询出来的结果可能不是50,但是基本上是保持在50上下的,这个具体原因还不是很清楚。这个已经可以缓存的cursor数量就是由session_cached_cursors决定的,我们可以修改session_cached_cursors这个参数的值,再来查询已经缓存的cursor数量(注意:单个session,不是全局哦)
SQL> alter system set session_cached_cursors=200 scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2215944 bytes
Variable Size 209719288 bytes
Database Buffers 411041792 bytes
Redo Buffers 3350528 bytes
Database mounted.
Database opened.
SQL> select userenv(‘sid’) from dual;
USERENV(‘SID’)
————–
30
SQL> declare
2 v_str varchar2(200);
3 begin
4 for i in 1 .. 30000000
5 loop
6 for j in 1 .. 5
7 loop
8 execute immediate ‘select * from tt where id=’||i;
9 –execute immediate v_str;
10 end loop;
11 end loop;
12 end;
13 /
在另一个会话中查看此时30号会话的cursor缓存信息:
SQL> select count(*) from v$open_cursor where cursor_type LIKE ‘%CACHED%’ AND SID in (30);
COUNT(*)
———-
200
关于PGA中缓存的cursor的知识是关于软软解析的,这里就不展开讨论了。
在30号会话中执行下面的pl/sql匿名块:
declare
v_sql varchar2(500);
v_cur number;
v_stat number;
begin
for i in 1 .. 300
loop
v_cur :=dbms_sql.open_cursor;
v_sql :=’select id from tt where id=’||i;
dbms_sql.parse(v_cur,v_sql,dbms_sql.native);
v_stat :=dbms_sql.execute(v_cur);
end loop;
end;
/
马上返回下面的错误:
declare
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded
ORA-06512: at “SYS.DBMS_SQL”, line 1017
ORA-06512: at line 8
另一个会话中查询一下30号会话已经打开的cursor数量:
SQL> select count(*) from v$open_cursor where cursor_type LIKE ‘OPEN%’ AND SID in (30);
COUNT(*)
———-
56
这个值基本上就是open_cursors的值
关于报错是因为open_cursers=50,而我们试图打开300个cursor,自然会报错的,我们将open_cursors改为400
SQL> alter system set open_cursors=400 scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2215944 bytes
Variable Size 218107896 bytes
Database Buffers 402653184 bytes
Redo Buffers 3350528 bytes
Database mounted.
Database opened.
SQL> select userenv(‘sid’) from dual;
USERENV(‘SID’)
————–
38
SQL> declare
2 msql varchar2(500);
3 mcur number;
4 mstat number;
5 begin
6 for i in 1 .. 300
7 loop
8 mcur :=dbms_sql.open_cursor;
9 msql :=’select id from tt where id=’||i;
10 dbms_sql.parse(mcur,msql,dbms_sql.native);
11 mstat :=dbms_sql.execute(mcur);
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.
此时上面的匿名pl/sql块可以顺利执行了,我们再查看一下38号会话打开的cursor数:
SQL> select count(*) from v$open_cursor where cursor_type LIKE ‘OPEN%’ AND SID in (38);
COUNT(*)
———-
305
打开的cursor数量基本上等于open_cursors的值。
关于为什么查出的值具体为什么不是准确的等于open_cursors的值,我偶然发现在执行匿名块之前先查询的当前会话已经打开的cursor数,在匿名块执行结束后再查询会话打开的cursor数,然后用后面的查询结果减去前面的查询结果,这样似乎直接等于open_cursors的值。

ogg无法正常停止extract进程 & 重启进程报错处理

在给ogg源端、目标端新增表时,经常会出现由于大事务的存在而导致抽取进程无法正常停止。系统会提示有大事务存在。

Sending STOP request to EXTRACT EXTODS3 ...

There are open, long-running transactions. Before you stop Extract, make the archives containing data for those transactions available for when Extract restarts. To force Extract to stop, use the SEND EXTRACT EXTODS3, FORCESTOP command.
Oldest redo log files necessary to restart Extract are:

Redo Thread 1, Redo Log Sequence Number 81638, SCN 3461.1538900652 (14866420712108), RBA 212382736
Redo Thread 2, Redo Log Sequence Number 106187, SCN 3461.1577312642 (14866459124098), RBA 824682512

由于本套环境的源端库比较繁忙,等到这个事务结束再去停止抽取进程,在参数表中配置,是无法忍受的,所以根据提示使用了强制停止抽取进程。

GGSCI (crm2db2) 2> SEND EXTRACT EXTODS3, FORCESTOP

在参数文件中添加好新表信息后,尝试重启进程,发现无法正常启动,进程状态为abended。
报错信息为无法找到归档日志(没有记录当时的日志,今后碰到会补充)

--解决办法:记录下问题进程的信息
GGSCI (crm2db2) 2> info EXTODS1

EXTRACT    EXTODS1   Last Started 2016-04-18 11:07   Status ABENDED
Checkpoint Lag       00:35:35 (updated 00:00:11 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2016-04-18 10:41:35  Thread 1, Seqno 81166, RBA 26388520
                     SCN 3461.1066937545 (14865948749001)
Log Read Checkpoint  Oracle Redo Logs
                     2016-04-18 10:41:35  Thread 2, Seqno 105728, RBA 25264
                     SCN 3461.1066938115 (14865948749571)


---通过os层面kill extract进程
ogg_eda@crm2db2:/ogg_eda$ ps -ef|grep EXTODS1
 ogg_eda  9335 16252  0 11:07:34 ?         8:37 /ogg_eda/extract PARAMFILE /ogg_eda/dirprm/extods1.prm REPORTFILE /ogg_eda/dirrpt/EXTODS1.rpt PROCESSID EXTODS1 USESUBDIRS
 ogg_eda 12019 11708  0 11:16:53 pts/8     0:00 grep EXTODS1

kill -9 9335

--alter ogg  时间比 info EXTODS1的时间提前2s
ALTER EXTRACT EXTODS1, THREAD 1, BEGIN 2016-04-18 10:41:33       
ALTER EXTRACT EXTODS1, THREAD 2, BEGIN 2016-04-18 10:41:33 


--start extract
start  EXTODS1

之后检查目标端,可能会出现数据重复导入的问题,需要加入参数HANDLECOLLISIONS
问题现象: 对比表两边字段,未发生变化。只是出现重复数据

 view report REPQD3
 
MAP resolved (entry OM.L_OFFER_PROD_INST_REL):
  MAP "OM"."L_OFFER_PROD_INST_REL" , TARGET CRMPDATA.L_OFFER_PROD_INST_REL;
Using following columns in default map by name:
  PROD_COMP_ID, PRO_PROD_COMP_ID, PROD_COMP_ROLE_CD, PROD_INST_ID, 
  STATE_CD, CREATE_DATE, LAN_ID, INSTANCE_TYPE, INSTANCE_ID, 
  PROD_TYPE_CD, COMP_INST_ID, CHG_ID, CUST_ORDER_ID, ORDER_ITEM_ID, 
  ACTION_TYPE, STATE_DATE, END_TIME, BEG_TIME, STAFF_ID, CHANNEL_ID, 
  DETAIL_ID, MCOMP_INST_ID, OFFER_PROD_INST_REL_ID, PROD_OFFER_INST_ID, 
  ROLE_CD, OFFER_PROD_INST_REL_ROLE_ID, BILL_PROD_OFFER_INST_ID, 
  EFF_DATE, EXP_DATE, D_ORDER_ITEM_ID
Using the following key columns for target table CRMPDATA.L_OFFER_PROD_INST_REL: PROD_COMP_ID, ORDER_ITE
M_ID, ACTION_TYPE, CHG_ID.


2016-05-06 16:22:10  WARNING OGG-00869  OCI Error ORA-00001: unique constraint (CRMPDATA.PK_OFFER_PROD_I
NST_REL) violated (status = 1). INSERT INTO "CRMPDATA"."L_OFFER_PROD_INST_REL" ("PROD_COMP_ID","PRO_PROD
_COMP_ID","PROD_COMP_ROLE_CD","PROD_INST_ID","STATE_CD","CREATE_DATE","LAN_ID","INSTANCE_TYPE","INSTANCE
_ID","PROD_TYPE_CD","COMP_INST_ID","CHG_ID","CUST_ORDER_ID","ORDER_ITEM_ID","ACTION_TYPE","STATE_DATE","
END_TIME","BEG_TIME","STAFF_ID","CHANNEL_ID","DETAIL_ID","MCOMP_INST_ID","OFFER_PROD_INST_REL_ID","PROD_
OFFER_INST_ID","ROLE_CD","OFFER_PROD_INST_REL_ROLE_ID","BILL_PROD_OFFER_INST_ID","EFF_DATE","EXP_DATE","
D_ORDER_ITEM_ID") VALUES (:a0,:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9,:a10,:a11,:a12,:a13,:a14,:a15,:a16,:a1
7,:a18,:a19,:a20,:a21,:a22,:a23,:a24,:a25,:a26,:a27,:a28,:a29).

--之后在该进程参数文件中加入HANDLECOLLISIONS,之后重启。待数据追平后注释掉该参数,再次重启该进程,使得注释参数生效。
GGSCI (qdata-rac2) 31> edit param REPQD3
REPLICAT repqd3
setenv (ORACLE_SID=gsods2)
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
USERID ogg,Password oEgDgA163CRM
REPORTCOUNT EVERY 30 MINUTES,RATE
REPERROR Default,ABEND
numfiles 5000
--HANDLECOLLISIONS
assumetargetdefs
DISCARDFILE ./dirrpt/repqdata3.dsc, APPEND, MEGABYTES 1024
ALLOWNOOPUPDATES
MAP OM.ACCESS_PROD_INST_XM , TARGET CRMPDATA.ACCESS_PROD_INST_XM;

之后进程恢复正常。

ORACLE实用技巧之不知道密码情况下dblink的迁移

ORACLE实用技巧之不知道密码情况下dblink的迁移

Morinson 原创,欢迎转载,但请保留作者及出处。

在oracle数据库的运维过程中,常常会有一些特殊场景,需要我们灵活运用知识去解决问题,今天,就和大家一起探讨分析一种情况。我们需要将一个数据库从一个环境迁移到另外一个,但因为种种限制因素,只能采用数据导入导出的方式来进行。在梳理环境编写迁移方案的时候,遇到了一个难题,数据库里有大量的dblink,大家知道这些dblink是在使用的,但是没有人知道密码。下面,我们使用模拟环境来表达分析解决过程。

数据库版本:
01

数据库链接:
02

在模拟环境中共有3数据库链接,我们以source这个为例。首先,我们确定这个dblink是可用的:
03

这个时候,很多同学可能就想到了,既然数据库链接source可以用,那就说明本地库(9i,服务名firefox)是知道访问远程库(11g,服务名source)时用到的用户名和密码的,那么oracle会把它存在哪里呢?难道是dba_db_links的其它列?我们一起来看看:
04

owner列存储dblink的所有者,值public代表是公共的;
db_link列存储dblink的名字;
username列存储dblink访问远程库的用户名;
host列存储dblink远程库的连接字符串,或者本地服务名;
create列存储列dblink的创建时间;

 

dba_db_links中对于dblink以哪个用户访问哪个库都做了详细的保存,但就是没有密码信息。到这里我们不要失望,因为我们知道所有的数据字典其实都不是真正的表格,而是基于内部表的一系列视图。我们去看看dba_db_links的定义:
05

知识点:其实数据字典严格的名字应该叫数据字典视图,它提供了数据库的一些系统信息,数据字典是基于数据字典基表(也叫内部表,以$结尾)所建立的一系列视图,数据字典视图主要包括三种类型:user_xxx,all_xxx和dba_xxx。

 

原来dba_db_links的数据来自于link$和user$两个内部表,那么密码会不会在这两个表里保存?我们去看看它们的结构:
06

07

知识点:内部表,sys用户下以$结尾的一系列表,是数据库内核的组成部分,用户只能在上面执行查询操作,其维护和修改是系统自动完成的,对其的不当操作可能会造成数据库宕机、甚至永久性损坏。

 

在link$和user$中我们发现都有一个password字段,那么哪一个是我们要找的dblink的密码呢?我们先来看看其中的内容:
08

09

内容太多,只选取相关的部分
10

我们发现link$中的密码以明文保存,user$中的密码以加密后的字符串保存。但到底哪个才是我们要找的密码呢?其实oracle在设计内部表时每个都有明确的作用和定义,其中link$是用来保存dblink相关信息的(包括密码),user$是用来数据库本地用户相关信息的。

 

那么现在我们就找到了数据库链接source的密码:tiger,再根据其它信息,可以很快写出其迁移重建语句:

 

create public database link SOURCE
  connect to SCOTT identified by "tiger"
  using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.127.136)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = source)
    )
  )';

知识点:在10gR1及之前的版本里,所有dblink的密码都是以明文方式在sys.link$中存储,但是在10gR2及之后版本,oracle进行了改良,以加密方式存储。

 

针对10gR2及之后版本,我们虽然不能直接取得密码,但也可以通过更新内部表的方式来实现迁移。Oracle对于直接修改内部表是严格禁止的,因为不当操作可能会造成数据库宕机、甚至永久性损坏。因为dblink是数据库里比较独立关联性小的一个功能,因此我们才有可能采取这种方式。但即便如此,也要求在做好备份后,由经验丰富掌握相关知识的工程师进行操作。

同样,先来看看数据库版本:
11

数据库链接
12

还是以数据库链接source为例,我们先测试其可用性:
13

接下来我们看看sys.link$在10gR2及之后的表结构变化,并直接从其中查询密码:
14

与上面的9i对比,多了两个列,其中PASSWORDX列就是用来存储加密之后的密码。
15

通过查询我们发现,password列虽然还在,但是内容为空,passwordx列则保存了加密之后的密码。

我们可以通过pl/sql dev之类的工具,将sys.link$的内容导出为sql语句,每个insert语句对应一个dblink信息。对导出文件进行编辑,保留我们需要迁移的dblink语句,删掉其它。
16

在目标环境上以sys身份执行脚本,然后对迁移后的dblink进行测试。
17

gongfei