月归档：十月 2012

commit后lob字段使用临时表空间未释放

发表于 2012 年 10 月 12 日 由 惜分飞

临时表空间被使用现状
接到客户反馈,他们的数据库使用了长连接,临时表空间使用率一直不下降,一个会话占用了几百M甚至几个G的临时表空间不释放,随着时间的积累,会话占用的临时表空间还在继续增加,最终的现象是100G的数据文件,160G的临时表空间还在继续报临时表空间不足.查询v$sort_usage发现其SEGTYPE全部为LOB_DATA而CONTENTS为TEMPORARY,而且BLOCKS都很大,通过上面的信息大概分析,怀疑是因为数据库查询或者操作LOB类型时候使用了TEMPORARY,但是没有释放导致

相关版本信息

OS:AIX 6.1(64)
DB:10.2.0.5

测试案例证明

--执行查询脚本
$ more check.sql
connect / as sysdba
select * from v$tempseg_usage where username not in ('HDDS_CLPS_DTA','FOGLIGHT');

--测试脚本1
$ more test1.sh
sqlplus /nolog <<EOF
connect / as sysdba
drop user xifenfei cascade;
create user xifenfei identified by tc
default tablespace users temporary tablespace temp quota unlimited on users;
grant connect,resource,alter session to xifenfei;
revoke unlimited tablespace from xifenfei;


connect xifenfei/tc
select to_nclob('a') from dual;

!sqlplus /nolog @check

commit;

!sqlplus /nolog @check

EOF

--测试脚本2
$ more test2.sh
sqlplus /nolog << EOF2
connect xifenfei/tc
alter session set events '60025 trace name context forever';
select to_nclob('a') from dual;

!sqlplus /nolog @check

commit;

!sqlplus /nolog @check

EOF2

测试结果

$ ./test1.sh

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:04:39 2012

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

SQL> Connected.
SQL> drop user xifenfei cascade
          *
ERROR at line 1:
ORA-01918: user 'XIFENFEI' does not exist

Grant succeeded.

SQL> 
Revoke succeeded.

SQL> SQL> SQL> Connected.
SQL> 
TO_NCLOB('A')
--------------------------------------------------------------------------------
a

SQL> SQL> 
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:04:39 2012

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

Connected.

USERNAME                       USER                           SESSION_ADDR
------------------------------ ------------------------------ ----------------
SESSION_NUM SQLADDR             SQLHASH SQL_ID
----------- ---------------- ---------- -------------
TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#
------------------------------- --------- --------- ---------- ----------
   EXTENTS     BLOCKS   SEGRFNO#
---------- ---------- ----------
xifenfei                           xifenfei                           07000002F96ECB30
      10152 07000002AE1C36E0 1362191183 9z69tsx8m2sug
TEMP                            TEMPORARY LOB_DATA         201       3465
         1        128          1


SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SQL> 
Commit complete.

SQL> SQL> 
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:04:39 2012

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

Connected.

USERNAME                       USER                           SESSION_ADDR
------------------------------ ------------------------------ ----------------
SESSION_NUM SQLADDR             SQLHASH SQL_ID
----------- ---------------- ---------- -------------
TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#
------------------------------- --------- --------- ---------- ----------
   EXTENTS     BLOCKS   SEGRFNO#
---------- ---------- ----------
xifenfei                           xifenfei                           07000002F96ECB30
      10152 07000002AE1C36E0 1362191183 9z69tsx8m2sug
TEMP                            TEMPORARY LOB_DATA         201       3465
         1        128          1


SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--测试脚本2
$ ./test2.sh

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:03:56 2012

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

SQL> Connected.
SQL> 
Session altered.

SQL> 
TO_NCLOB('A')
--------------------------------------------------------------------------------
a

SQL> SQL> 
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:03:56 2012

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

Connected.

no rows selected

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SQL> 
Commit complete.

SQL> SQL> 
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:03:56 2012

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

Connected.

no rows selected

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

1.Without event 60025 set (before and after commit):都出现v$tempseg_usage中存在对应记录,而且提交后不能释放Temp LOB space
2.With event 60025 set (before and after commit):都未现v$tempseg_usage中存在对应记录,证明提交后释放Temp LOB space

解决方案
通过上面的试验证明我们可以通过设置event 60025来解决该版本的会话提交后Temp LOB space不能被回收的问题.
我们可以通过在session级别使用”alter session set events ’60025 trace name context forever’;”来实现。如果想实现全库级别的，但是因为event 60025不能通过system设置生效,所以我们可以通过logon触发器来实现该功能

create or replace trigger sys.login_db after logon on database
begin
execute immediate 'alter session set events ''60025 trace name context forever''';
end;
/

注意这个是ORCLE bug(Bug 5723140 – Temp LOB space not released after commit [ID 5723140.8]),从10.2.0.4开始虽然已经修复了该bug,但是默认情况下:为了更加高效的利用temp,在session未断开前,不自动释放temp 空间,可以通过设置event 60025来强制会话在commit之后就立即释放temp space

发表在 Oracle | 6 条评论

recover遇到坏块处理本质探讨

发表于 2012 年 10 月 8 日 由 惜分飞

如果在还原出来的数据文件中有坏块,而归档日志和联机日志是正常的,那么在应用日志恢复过程中,会出现什么情况,这里通过一个简单的测试给予其中一种情况的说明
创建测试表

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> create table t_xifenfei(object_id,object_name) tablespace xifenfei
  2  as
  3  select object_id,object_name from dba_objects      
  4  where rownum<11;

Table created.

SQL> col object_name for a30
SQL> select   object_id,object_name,
  2   dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3  dbms_rowid.rowid_block_number(rowid)blockno
  4   from chf.t_xifenfei;

 OBJECT_ID OBJECT_NAME                       REL_FNO    BLOCKNO
---------- ------------------------------ ---------- ----------
        20 ICOL$                                   5         12
        44 I_USER1                                 5         12
        28 CON$                                    5         12
        15 UNDO$                                   5         12
        29 C_COBJ#                                 5         12
         3 I_OBJ#                                  5         12
        25 PROXY_ROLE_DATA$                        5         12
        39 I_IND1                                  5         12
        51 I_CDEF2                                 5         12
        26 I_PROXY_ROLE_DATA$_1                    5         12

10 rows selected.

SQL> select name from v$datafile where file#=5;

NAME
--------------------------------------------------------------
/u01/oracle/oradata/XFF/xifenfei01.dbf

SQL> update t_xifenfei set object_name='WWW.XIFENFEI.COM';

10 rows updated.

SQL> commit;

Commit complete.

SQL> create table t_xifenfei_new(object_id,object_name) tablespace xifenfei
  2  as
  3  select object_id,object_name from dba_objects      
  4  where rownum<11;

Table created.

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

备份数据文件

[oracle@xifenfei XFF]$ cp xifenfei01.dbf  ../tmp/
[oracle@xifenfei XFF]$ ll ../tmp/xifenfei01.dbf 
-rw-r----- 1 oracle oinstall 10493952 Sep 28 19:05 ../tmp/xifenfei01.dbf
[oracle@xifenfei XFF]$ date
Fri Sep 28 19:05:42 CST 2012

bbed破坏备份文件

[oracle@xifenfei XFF]$ bbed password=blockedit

BBED: Release 2.0.0.0.0 - Limited Production on Fri Sep 28 19:05:59 2012

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

************* !!! For Oracle Internal Use only !!! ***************

BBED> set filename '/u01/oracle/oradata/tmp/xifenfei01.dbf'
        FILENAME        /u01/oracle/oradata/tmp/xifenfei01.dbf

BBED> set block 12
        BLOCK#          12

BBED> set mode edit
        MODE            Edit

BBED> map
 File: /u01/oracle/oradata/tmp/xifenfei01.dbf (0)
 Block: 12                                    Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 96 bytes                     @20      

 struct kdbh, 14 bytes                      @124     

 struct kdbt[1], 4 bytes                    @138     

 sb2 kdbr[10]                               @142     

 ub1 freespace[7666]                        @162     

 ub1 rowdata[360]                           @7828    

 ub4 tailchk                                @8188    


BBED> p kcbh
struct kcbh, 20 bytes                       @0       
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x0140000c
   ub4 bas_kcbh                             @8        0x0004d7b0
   ub2 wrp_kcbh                             @12       0x000a
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x06 (KCBHFDLC, KCBHFCKV)
   ub2 chkval_kcbh                          @16       0xe573
   ub2 spare3_kcbh                          @18       0x0000

BBED> d offset 8188
 File: /u01/oracle/oradata/tmp/xifenfei01.dbf (0)
 Block: 12               Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 0106b0d7 

 <32 bytes per line>

BBED> m /x 11
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/tmp/xifenfei01.dbf (0)
 Block: 12               Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 1106b0d7 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 12:
current = 0xe563, required = 0xe563

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/tmp/xifenfei01.dbf
BLOCK = 12

Block 12 is corrupt
Corrupt block relative dba: 0x0140000c (file 0, block 12)
Fractured block found during verification
Data in bad block:
 type: 6 format: 2 rdba: 0x0140000c
 last change scn: 0x000a.0004d7b0 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xd7b00611
 check value in block header: 0xe563
 computed block checksum: 0x0


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 1
Total Blocks Influx           : 2

修改数据库记录

SQL> startup
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size             109054428 bytes
Database Buffers          201326592 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
SQL> conn chf/xifenfei
Connected.
SQL> update t_xifenfei set object_name='惜分飞';

10 rows updated.

SQL> update t_xifenfei_new set object_name='惜分飞';

10 rows updated.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

利用备份数据文件恢复数据库

[oracle@xifenfei XFF]$ cp xifenfei01.dbf xifenfei01.dbf_bak
[oracle@xifenfei XFF]$ cp ../tmp/xifenfei01.dbf  xifenfei01.dbf
[oracle@xifenfei XFF]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Sep 28 19:13:59 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size             109054428 bytes
Database Buffers          201326592 bytes
Redo Buffers                7118848 bytes
Database mounted.
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u01/oracle/oradata/XFF/xifenfei01.dbf'
--提示数据需要恢复

SQL> recover datafile 5;
ORA-00279: change 42949990720 generated at 09/28/2012 19:04:10 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/XFF/archivelog/1_24_792679299.dbf
ORA-00280: change 42949990720 for thread 1 is in sequence #24


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> alter database open;

Database altered.
--利用被破坏的数据文件+归档日志恢复数据库正常

SQL> col object_name for a30
SQL> select   object_id,object_name from t_xifenfei;
select   object_id,object_name from t_xifenfei
                                    *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 12)
ORA-01110: data file 5: '/u01/oracle/oradata/XFF/xifenfei01.dbf'
--提示被破坏的数据块,查询不能完成

--证明坏块之外的数据块还是被正常应用日志
SQL>  select   object_id,object_name from t_xifenfei_new;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
        20 惜分飞
        44 惜分飞
        28 惜分飞
        15 惜分飞
        29 惜分飞
         3 惜分飞
        25 惜分飞
        39 惜分飞
        51 惜分飞
        26 惜分飞

10 rows selected.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

dbv检查坏块

[oracle@xifenfei XFF]$ dbv file=xifenfei01.dbf

DBVERIFY: Release 10.2.0.4.0 - Production on Fri Sep 28 19:14:52 2012

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

DBVERIFY - Verification starting : FILE = xifenfei01.dbf

DBV-00200: Block, DBA 20971532, already marked corrupt
--这里可以看出来，该数据块已经被标志为坏块

DBVERIFY - Verification complete

Total Pages Examined         : 1280
Total Pages Processed (Data) : 2
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 14
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1264
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 318700 (10.318700)

查看恢复过程alert日志

Fri Sep 28 19:14:06 2012
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Fri Sep 28 19:14:06 2012
ALTER DATABASE OPEN
ORA-1113 signalled during: ALTER DATABASE OPEN...
Fri Sep 28 19:14:11 2012
ALTER DATABASE RECOVER  datafile 5  
Media Recovery Start
 parallel recovery started with 2 processes
ORA-279 signalled during: ALTER DATABASE RECOVER  datafile 5  ...
Fri Sep 28 19:14:16 2012
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Fri Sep 28 19:14:16 2012

--恢复数据库的时候,发现坏块
Media Recovery Log /u01/oracle/oradata/XFF/archivelog/1_24_792679299.dbf
Fri Sep 28 19:14:16 2012
Hex dump of (file 5, block 12) in trace file /u01/oracle/admin/XFF/bdump/xff_p001_23011.trc
Corrupt block relative dba: 0x0140000c (file 5, block 12)
Fractured block found during media recovery
Data in bad block:
 type: 6 format: 2 rdba: 0x0140000c
 last change scn: 0x000a.0004d7b0 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xd7b00611
 check value in block header: 0xe563
 computed block checksum: 0x0
Reread of rdba: 0x0140000c (file 5, block 12) found same corrupted data

--继续恢复
Fri Sep 28 19:14:16 2012
Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0
  Mem# 0: /u01/oracle/oradata/XFF/redo01.log
Fri Sep 28 19:14:16 2012
Recovery of Online Redo Log: Thread 1 Group 2 Seq 26 Reading mem 0
  Mem# 0: /u01/oracle/oradata/XFF/redo02.log
Fri Sep 28 19:14:16 2012
Recovery of Online Redo Log: Thread 1 Group 3 Seq 27 Reading mem 0
  Mem# 0: /u01/oracle/oradata/XFF/redo03.log
Fri Sep 28 19:14:16 2012
Media Recovery Complete (XFF)
Completed: ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Fri Sep 28 19:14:31 2012
alter database open

bbed查看修改相关信息

BBED> set filename '/u01/oracle/oradata/XFF/xifenfei01.dbf'
        FILENAME        /u01/oracle/oradata/XFF/xifenfei01.dbf

BBED> set block 12
        BLOCK#          12

BBED> map
 File: /u01/oracle/oradata/XFF/xifenfei01.dbf (0)
 Block: 12                                    Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 96 bytes                     @20      

 struct kdbh, 14 bytes                      @124     

 struct kdbt[1], 4 bytes                    @138     

 sb2 kdbr[10]                               @142     

 ub1 freespace[7666]                        @162     

 ub1 rowdata[360]                           @7828    

 ub4 tailchk                                @8188    


BBED> p kcbh
struct kcbh, 20 bytes                       @0       
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x0140000c
   ub4 bas_kcbh                             @8        0x00000000
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0xff   <--因为被标记为坏块,所以为ff
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0xe77d
   ub2 spare3_kcbh                          @18       0x0000

--查看数据块中记录
BBED> p *kdbr[5]
rowdata[69]
-----------
ub1 rowdata[69]                             @7897     0x2c

BBED> x /rnc
rowdata[69]                                 @7897    
-----------
flag@7897: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7898: 0x02
cols@7899:    2

col    0[2] @7900: 3 
col   1[16] @7903: WWW.XIFENFEI.COM  <--确实没有被恢复,而是直接被跳过


BBED> set mode edit
        MODE            Edit

BBED> m /x 01 offset 14
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/XFF/xifenfei01.dbf (0)
 Block: 12               Offsets:   14 to  525           Dba:0x00000000
------------------------------------------------------------------------
 01047de7 00000100 00000dcc 000098d7 ………… 

 <32 bytes per line>

BBED> d offset 8188
 File: /u01/oracle/oradata/XFF/xifenfei01.dbf (0)
 Block: 12               Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 ff060000    <--这么说明:数据块被标志为坏块的时候，同时会修改tailchk值

 <32 bytes per line>

BBED> m /x 01 offset 8188
 File: /u01/oracle/oradata/XFF/xifenfei01.dbf (0)
 Block: 12               Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 01060000 

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 12:
current = 0xe77d, required = 0xe77d

--验证块已经标记为正常块
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/XFF/xifenfei01.dbf
BLOCK = 12


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

启动数据库测试

SQL> startup
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size             109054428 bytes
Database Buffers          201326592 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
SQL> conn chf/xifenfei
Connected.
SQL> col object_name for a30
SQL> select   object_id,object_name from t_xifenfei;

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
        20 WWW.XIFENFEI.COM
        44 WWW.XIFENFEI.COM
        28 WWW.XIFENFEI.COM
        15 WWW.XIFENFEI.COM
        29 WWW.XIFENFEI.COM
         3 WWW.XIFENFEI.COM
        25 WWW.XIFENFEI.COM
        39 WWW.XIFENFEI.COM
        51 WWW.XIFENFEI.COM
        26 WWW.XIFENFEI.COM

10 rows selected.

--通过修改数据块的seq_kcbh和tailchk,让这个块恢复正常,但是记录依然丢失，
--因为应用日志恢复之时标记为坏块跳过该块的日志应用

通过实验证明:
1.如果只有数据块异常,应用日志恢复,不一定会出现ORA-600[3020],而是直接把该块标记为坏块,继续应用日志
2.标记坏块其实就是修改seq_kcbh为ff,同时也修改tailchk值
3.经验值:如果在数据库应用日志恢复的时候,如果出现ORA-600[3020]错误,可以使用allow 2 corruption来跳过坏块处理,其实也是修改seq_kcbh为ff,然后让数据库跳过该块的恢复.

发表在 Oracle备份恢复 | 评论关闭

large pool太小导致shared server异常

发表于 2012 年 10 月 6 日 由 惜分飞

数据库出现如下错误

Fri Oct  5 09:33:54 2012
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20
Process S001 started up but failed with error = 20
failed to start shared server 'S001', oer=20

重启后错误提示变为

Successfully onlined Undo Tablespace 1.
Fri Oct  5 09:34:41 2012
SMON: enabling tx recovery
Fri Oct  5 09:34:41 2012
Database Characterset is AL32UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN
Fri Oct  5 13:53:50 2012
Errors in file /oracle/admin/ora/bdump/ora_s000_25948.trc:
ORA-04031: unable to allocate 72 bytes of shared memory 
("large pool","unknown object","session heap","trigger condition node")
Fri Oct  5 13:53:50 2012
Errors in file /oracle/admin/ora/bdump/ora_s000_25948.trc:
ORA-00600: internal error code, arguments: [ksudel1], [], [], [], [], [], [], []
ORA-04031: unable to allocate 72 bytes of shared memory 
("large pool","unknown object","session heap","trigger condition node")
Fri Oct  5 13:54:52 2012
found dead shared server 'S000', pid = (11, 1)
Fri Oct  5 17:25:59 2012
Errors in file /oracle/admin/ora/bdump/ora_s000_31081.trc:
ORA-04031: unable to allocate 72 bytes of shared memory 
("large pool","unknown object","session heap","trigger condition node")
Fri Oct  5 17:25:59 2012
Errors in file /oracle/admin/ora/bdump/ora_s000_31081.trc:
ORA-00600: internal error code, arguments: [ksudel1], [], [], [], [], [], [], []
ORA-04031: unable to allocate 72 bytes of shared memory 
("large pool","unknown object","session heap","trigger condition node")

通过这里的错误,我们可以看到是large pool不能分配72 bytes的连续内存空间而使得S000进程报错.那这两者有什么联系:我们知道S000是shared server的进程,那shared server为什么导致large pool不足呢？查询官方文档得出,如下三种情况会使用large pool

Session memory for the shared server and the Oracle XA interface 
(used where transactions interact with more than one database)

I/O server processes

Oracle backup and restore operations

主要也就是shared server/parallel query buffers/backup restore这几个操作会使用到large pool.在该案例中很明显的可以看到是因为shared server进程需要分配large pool中一部分空间,而没有连续空间从而出现该错误.数据库相关参数配置

SQL> show parameter mts;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
mts_circuits                         integer     555
mts_dispatchers                      string      (PROTOCOL=TCP) (SERVICE=oraXDB)
mts_listener_address                 string
mts_max_dispatchers                  integer     5
mts_max_servers                      integer     20
mts_multiple_listeners               boolean     FALSE
mts_servers                          integer     1
mts_service                          string      ora
mts_sessions                         integer     550

SQL> show parameter large;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
large_pool_size                      big integer 16777216

这里可以看出来,数据库明显配置了MTS,因为数据库在启动时候,最少会建立一个shared server进程,而这个时候因为large pool太小(16M),导致该进程无法正常建立,从而出现上述alert中相关错误,临时处理方法增加large pool.后续需要关注业务特点,考虑是否可以采用Oracle Dedicated server模式来处理.

发表在 ORA-xxxxx | 评论关闭