分类目录归档：Oracle

数据库不能open 报ORA-7445 lmebucp错

发表于 2018 年 4 月 8 日 由 惜分飞

有一个朋友数据库启动报错ORA-03113

SQL> select * from v$version;

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

SQL> startup
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             385876488 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14680064 bytes
Database mounted.
ORA-03113: end-of-file on communication channel

alert日志报ORA-7445 lmebucp()+24错误

Sun Apr 08 08:05:07 CST 2018
ALTER DATABASE   MOUNT
Sun Apr 08 08:05:11 CST 2018
Setting recovery target incarnation to 2
Sun Apr 08 08:05:11 CST 2018
Successful mount of redo thread 1, with mount id 2650526067
Sun Apr 08 08:05:11 CST 2018
Database mounted in Exclusive Mode
Completed: ALTER DATABASE   MOUNT
Sun Apr 08 08:05:11 CST 2018
ALTER DATABASE OPEN
Sun Apr 08 08:05:11 CST 2018
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/t10g/redo01.log
Successful open of redo thread 1
Sun Apr 08 08:05:11 CST 2018
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Sun Apr 08 08:05:11 CST 2018
SMON: enabling cache recovery
Sun Apr 08 08:05:11 CST 2018
Errors in file /u01/app/oracle/admin/t10g/udump/t10g_ora_32810.trc:
ORA-07445: exception encountered: core dump [lmebucp()+24] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []

对应trace文件

Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x0, PC: [0x37e6418, lmebucp()+24]
*** 2014-04-08 08:05:11.793
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [lmebucp()+24] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
Current SQL statement for this session:
ALTER DATABASE OPEN
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+31          call     ksedst1()            000000000 ? 000000001 ?
                                                   7F4238A22BD0 ? 7F4238A22C30 ?
                                                   7F4238A22B70 ? 000000000 ?
ksedmp()+610         call     ksedst()             000000000 ? 000000001 ?
                                                   7F4238A22BD0 ? 7F4238A22C30 ?
                                                   7F4238A22B70 ? 000000000 ?
ssexhd()+629         call     ksedmp()             000000003 ? 000000001 ?
                                                   7F4238A22BD0 ? 7F4238A22C30 ?
                                                   7F4238A22B70 ? 000000000 ?
__restore_rt()+0     call     ssexhd()             00000000B ? 7F4238A23BF0 ?
                                                   7F4238A23AC0 ? 7F4238A22C30 ?
                                                   7F4238A22B70 ? 000000000 ?
lmebucp()+24         signal   __restore_rt()       000000000 ? 000008C00 ?
                                                   7FFF0F778790 ? 000000009 ?
                                                   000000000 ? 00000000D ?
kqlbebs()+1468       call     lmebucp()            000000000 ? 000008C00 ?
                                                   7FFF0F778790 ? 000000009 ?
                                                   000000000 ? 00000000D ?
kqlblfc()+172        call     kqlbebs()            000000000 ? 0BC119DE0 ?
                                                   7FFF0F778790 ? 000000009 ?
                                                   000000000 ? 00000000D ?
adbdrv()+58009       call     kqlblfc()            000000000 ? 7FFF0F77F610 ?
                                                   7FFF0F778790 ? 000000009 ?
                                                   000000000 ? 00000000D ?
opiexe()+13745       call     adbdrv()             000000000 ? 7FFF0F77F610 ?
                                                   0B9FFF9A8 ? 000000009 ?
                                                   000000000 ? 00000000D ?
opiosq0()+3398       call     opiexe()             000000004 ? 000000000 ?
                                                   7FFF0F7807CC ? 000000001 ?
                                                   000000000 ? 00000000D ?
kpooprx()+318        call     opiosq0()            000000003 ? 00000000E ?
                                                   7FFF0F780AF8 ? 0000000A4 ?
                                                   000000000 ? 600000013 ?
kpoal8()+783         call     kpooprx()            7FFF0F783CDC ? 7FFF0F781D30 ?
                                                   000000013 ? 000000001 ?
                                                   000000000 ? 600000013 ?
opiodr()+1184        call     kpoal8()             00000005E ? 000000017 ?
                                                   7FFF0F783CD8 ? 000000001 ?
                                                   000000001 ? 600000013 ?
ttcpip()+1226        call     opiodr()             00000005E ? 000000017 ?
                                                   7FFF0F783CD8 ? 000000000 ?
                                                   005BEBD70 ? 600000013 ?
opitsk()+1310        call     ttcpip()             006AF1FB0 ? 0054A6760 ?
                                                   7FFF0F783CD8 ? 000000000 ?
                                                   7FFF0F7837D8 ? 7FFF0F783E40 ?
opiino()+1024        call     opitsk()             000000003 ? 000000000 ?
                                                   7FFF0F783CD8 ? 000000001 ?
                                                   000000000 ? 721000200000001 ?
opiodr()+1184        call     opiino()             00000003C ? 000000004 ?
                                                   7FFF0F784ED8 ? 000000001 ?
                                                   000000000 ? 721000200000001 ?
opidrv()+548         call     opiodr()             00000003C ? 000000004 ?
                                                   7FFF0F784ED8 ? 000000000 ?
                                                   005BEB820 ? 721000200000001 ?
sou2o()+114          call     opidrv()             00000003C ? 000000004 ?
                                                   7FFF0F784ED8 ? 000000000 ?
                                                   005BEB820 ? 721000200000001 ?
opimai_real()+163    call     sou2o()              7FFF0F784EB0 ? 00000003C ?
                                                   000000004 ? 7FFF0F784ED8 ?
                                                   005BEB820 ? 721000200000001 ?
main()+116           call     opimai_real()        000000002 ? 7FFF0F784F40 ?
                                                   000000004 ? 7FFF0F784ED8 ?
                                                   005BEB820 ? 721000200000001 ?
__libc_start_main()  call     main()               000000002 ? 7FFF0F784F40 ?
+244                                               000000004 ? 7FFF0F784ED8 ?
                                                   005BEB820 ? 721000200000001 ?
_start()+41          call     __libc_start_main()  00072D108 ? 000000002 ?
                                                   7FFF0F785098 ? 000000000 ?
                                                   005BEB820 ? 000000002 ?

--------------------- Binary Stack Dump ---------------------

10046定位语句

SQL> staRTUP MOUNT
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             385876488 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14680064 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> oradebug tracefile_name
/u01/app/oracle/admin/t10g/udump/t10g_ora_32908.trc
SQL> alter database open;
ERROR:
ORA-03113: end-of-file on communication channel


SQL> 


PARSING IN CURSOR #2 len=55 dep=1 uid=0 oct=3 lid=0 tim=1463569438249596 hv=2111436465 ad='beb13e10'
select line#, sql_text from bootstrap$ where obj# != :1
END OF STMT
PARSE #2:c=0,e=471,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1463569438249594
BINDS #2:
kkscoacd
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f8f940fadc0  bln=22  avl=02  flg=05
  value=56
EXEC #2:c=2000,e=61246,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1463569438310929
WAIT #2: nam='db file sequential read' ela= 41 file#=1 block#=377 blocks=1 obj#=56 tim=1463569438311099
WAIT #2: nam='db file scattered read' ela= 73 file#=1 block#=378 blocks=3 obj#=56 tim=1463569438311528
FETCH #2:c=1000,e=775,p=4,cr=5,cu=0,mis=0,r=0,dep=1,og=4,tim=1463569438311772
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x0, PC: [0x37e6418, lmebucp()+24]
*** 2018-04-08 08:11:44.840
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [lmebucp()+24] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
Current SQL statement for this session:
alter database open

通过这里基本上可以定位，报错是由于bootstrap$中对象异常导致。由于该对象比较特殊,使用一些特殊方法进行处理,数据库正常启动成功。如果需要相关技术支持请联系我们，提供专业ORACLE数据库恢复技术支持
Phone:17813235971    Q Q:107644445QQ咨询惜分飞    E-Mail:dba@xifenfei.com

发表在 Oracle | 标签为 , , | 评论关闭

数据文件自扩展引起—enq: HW – contention

发表于 2018 年 4 月 2 日 由 惜分飞

客户反馈数据库比较慢,严重影响业务运行,让我们进行分析
enq: HW – contention等待明显
登录上去查看发现大量的enq: HW – contention等相关等待.
1
2

确认相关对象

SQL> select p3 from v$session_wait where event = 'enq: HW - contention';

   P3 
----------
34083635

SQL> select dbms_utility.data_block_address_block(34083635) RDBA_FILE,
  2  dbms_utility.data_block_address_file(34083635) RDBA_BLOCK from dual;  

 RDBA_FILE RDBA_BLOCK
---------- ----------
    529203          8


SQL>  select owner, segment_type, segment_name,tablespace_name   
  2      from dba_extents   
  3      where file_id = 8   
  4     and 529203 between block_id and block_id + blocks - 1;  

   OWNER   SEGMENT_TYPE        SEGMENT_NAME 	    TABLESPACE_NAME
---------- ------------------- -------------------  -------------------
XXXX       INDEX               T_TRADEITEM_85        USERS

确认对应sql
3

分析表空间使用情况
4
增加数据文件
5
再次查询表空间使用情况
6
查询等待事件
7
通过这里的分析,由于USERS表空间的空闲表空间已经被完全使用,后续使用需要扩展,因而引起了enq: HW – contention等待,以及由于该等待引起了其他相关等待,当users表空间进行扩展之后,立马恢复正常.实际的例子证明,在实际生产中特别是高并发生产库中,数据文件自扩展会严重影响数据库性能.

发表在 Oracle性能优化 | 标签为 | 2 条评论

恢复没有控制文件备份的rman数据文件备份

发表于 2018 年 3 月 30 日 由 惜分飞

最近有朋友咨询,只有rman备份数据文件,无控制文件备份的问题,这里进行分析验证
只有数据文件的rman备份
这里只有数据文件的备份，没有任何控制文件的备份

C:\Documents and Settings\Administrator>e:

E:\>dir
 驱动器 E 中的卷是 新加卷
 卷的序列号是 68FB-8527

 E:\ 的目录

2018-03-29  10:53     6,163,873,792 FULL_20180329_01SV08N2_1_1.RMAN
2018-03-29  10:54     1,050,558,464 FULL_20180329_02SV0918_1_1.RMAN
2018-03-29  10:56     2,167,414,784 FULL_20180329_03SV092B_1_1.RMAN
2018-03-29  11:07     1,034,625,024 FULL_20180329_05SV09QQ_1_1.RMAN
2018-03-29  11:07         1,376,256 FULL_20180329_06SV09RJ_1_1.RMAN
2018-03-29  11:08         3,104,768 FULL_20180329_07SV09TP_1_1.RMAN

还原system01.dbf

SQL> startup nomount
ORACLE 例程已经启动。

Total System Global Area 2137886720 bytes
Fixed Size                  2177456 bytes
Variable Size            1224738384 bytes
Database Buffers          905969664 bytes
Redo Buffers                5001216 bytes


SQL> @restore_datafile.sql system01.dbf

Trying : E:/FULL_20180329_02SV0918_1_1.RMAN From : DISK
Data File : Found
------------------------
RESTORED Data File To: D:\oradata\orcl\system01.dbf

PL/SQL 过程已成功完成。

重建控制文件

SQL> startup nomount pfile=e:/pfile.txt
ORACLE 例程已经启动。

Total System Global Area 2137886720 bytes
Fixed Size                  2177456 bytes
Variable Size            1224738384 bytes
Database Buffers          905969664 bytes
Redo Buffers                5001216 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 50
  3      MAXLOGMEMBERS 5
  4      MAXDATAFILES 100
  5      MAXINSTANCES 1
  6      MAXLOGHISTORY 226
  7  LOGFILE
  8    GROUP 1 'D:\oradata\orcl\redo01.log'  SIZE 100M,
  9    GROUP 2 'D:\oradata\orcl\redo02.log'  SIZE 100M,
 10    GROUP 3 'D:\oradata\orcl\redo03.log'  SIZE 100M
 11  DATAFILE
 12    'D:\oradata\orcl\system01.dbf'
 13  CHARACTER SET ZHS16GBK
 14  ;

控制文件已创建。

注册备份集

E:\>rman target /

恢复管理器: Release 11.2.0.1.0 - Production on 星期四 3月 29 15:46:17 2018

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

已连接到目标数据库: ORCL (DBID=1449113534, 未打开)

RMAN> catalog start with 'e:/*.rman';

使用目标数据库控制文件替代恢复目录
搜索与样式 e:/*.rman 匹配的所有文件

数据库未知文件的列表
=====================================
文件名: E:\FULL_20180329_01SV08N2_1_1.RMAN
文件名: E:\FULL_20180329_02SV0918_1_1.RMAN
文件名: E:\FULL_20180329_03SV092B_1_1.RMAN
文件名: E:\FULL_20180329_05SV09QQ_1_1.RMAN
文件名: E:\FULL_20180329_06SV09RJ_1_1.RMAN
文件名: E:\FULL_20180329_07SV09TP_1_1.RMAN

是否确实要将上述文件列入目录 (输入 YES 或 NO)? yes
正在编制文件目录...
目录编制完毕

已列入目录的文件的列表
=======================
文件名: E:\FULL_20180329_01SV08N2_1_1.RMAN
文件名: E:\FULL_20180329_02SV0918_1_1.RMAN
文件名: E:\FULL_20180329_03SV092B_1_1.RMAN
文件名: E:\FULL_20180329_05SV09QQ_1_1.RMAN
文件名: E:\FULL_20180329_06SV09RJ_1_1.RMAN
文件名: E:\FULL_20180329_07SV09TP_1_1.RMAN

RMAN> list backup;


备份集列表
===================


BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
1       Full    5.74G      DISK        00:00:00     29-3月 -18
        BP 关键字: 1   状态: AVAILABLE  已压缩: NO  标记: TAG20180329T104802
段名:E:\FULL_20180329_01SV08N2_1_1.RMAN
  备份集 1 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  5       Full 96384627   29-3月 -18

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
2       Full    1001.88M   DISK        00:00:00     29-3月 -18
        BP 关键字: 2   状态: AVAILABLE  已压缩: NO  标记: TAG20180329T104802
段名:E:\FULL_20180329_02SV0918_1_1.RMAN
  备份集 2 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  1       Full 96384627   29-3月 -18 D:\ORADATA\ORCL\SYSTEM01.DBF

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
3       Full    2.02G      DISK        00:00:00     29-3月 -18
        BP 关键字: 3   状态: AVAILABLE  已压缩: NO  标记: TAG20180329T104802
段名:E:\FULL_20180329_03SV092B_1_1.RMAN
  备份集 3 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  6       Full 96384627   29-3月 -18

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
4       Full    986.69M    DISK        00:00:00     29-3月 -18
        BP 关键字: 4   状态: AVAILABLE  已压缩: NO  标记: TAG20180329T110706
段名:E:\FULL_20180329_05SV09QQ_1_1.RMAN
  备份集 4 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  2       Full 96384627   29-3月 -18

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
5       Full    1.30M      DISK        00:00:00     29-3月 -18
        BP 关键字: 5   状态: AVAILABLE  已压缩: NO  标记: TAG20180329T110706
段名:E:\FULL_20180329_06SV09RJ_1_1.RMAN
  备份集 5 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  4       Full 96384627   29-3月 -18

BS 关键字  类型 LV 大小       设备类型 经过时间 完成时间
------- ---- -- ---------- ----------- ------------ ----------
6       Full    2.95M      DISK        00:00:00     29-3月 -18
        BP 关键字: 6   状态: AVAILABLE  已压缩: NO  标记: TAG20180329T110841
段名:E:\FULL_20180329_07SV09TP_1_1.RMAN
  备份集 6 中的数据文件列表
  文件 LV 类型 Ckp SCN    Ckp 时间   名称
  ---- -- ---- ---------- ---------- ----
  3       Full 96384627   29-3月 -18

还原所有数据文件

SQL> @restore_datafile all_file

Restoring All Data Files :
--------------------------
Attempting To Restore :D:\oradata\orcl\1.dbf
------------------------
Trying : E:/FULL_20180329_01SV08N2_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_02SV0918_1_1.RMAN From : DISK
Data File : Found
------------------------
RESTORED Data File To: D:\oradata\orcl\1.dbf
Attempting To Restore :D:\oradata\orcl\2.dbf
------------------------
Trying : E:/FULL_20180329_01SV08N2_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_02SV0918_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_03SV092B_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_05SV09QQ_1_1.RMAN From : DISK
Data File : Found
------------------------
RESTORED Data File To: D:\oradata\orcl\2.dbf
Attempting To Restore :D:\oradata\orcl\3.dbf
------------------------
Trying : E:/FULL_20180329_01SV08N2_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_02SV0918_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_03SV092B_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_05SV09QQ_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_06SV09RJ_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_07SV09TP_1_1.RMAN From : DISK
Data File : Found
------------------------
RESTORED Data File To: D:\oradata\orcl\3.dbf
Attempting To Restore :D:\oradata\orcl\4.dbf
------------------------
Trying : E:/FULL_20180329_01SV08N2_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_02SV0918_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_03SV092B_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_05SV09QQ_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_06SV09RJ_1_1.RMAN From : DISK
Data File : Found
------------------------
RESTORED Data File To: D:\oradata\orcl\4.dbf
Attempting To Restore :D:\oradata\orcl\5.dbf
------------------------
Trying : E:/FULL_20180329_01SV08N2_1_1.RMAN From : DISK
Data File : Found
------------------------
RESTORED Data File To: D:\oradata\orcl\5.dbf
Attempting To Restore :D:\oradata\orcl\6.dbf
------------------------
Trying : E:/FULL_20180329_01SV08N2_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_02SV0918_1_1.RMAN From : DISK
Data File: Not Found
------------------------
Trying : E:/FULL_20180329_03SV092B_1_1.RMAN From : DISK
Data File : Found
------------------------
RESTORED Data File To: D:\oradata\orcl\6.dbf

PL/SQL 过程已成功完成。

重建控制文件并open数据库

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 50
  3      MAXLOGMEMBERS 5
  4      MAXDATAFILES 100
  5      MAXINSTANCES 1
  6      MAXLOGHISTORY 226
  7  LOGFILE
  8    GROUP 1 'D:\oradata\orcl\redo01.log'  SIZE 100M,
  9    GROUP 2 'D:\oradata\orcl\redo02.log'  SIZE 100M,
 10    GROUP 3 'D:\oradata\orcl\redo03.log'  SIZE 100M
 11  DATAFILE
 12    'D:\oradata\orcl\1.dbf',
 13    'D:\oradata\orcl\2.dbf',
 14    'D:\oradata\orcl\3.dbf',
 15    'D:\oradata\orcl\4.dbf',
 16    'D:\oradata\orcl\5.dbf',
 17    'D:\oradata\orcl\6.dbf'
 18  CHARACTER SET ZHS16GBK
 19  ;

控制文件已创建。

SQL> alter database open resetlogs;

数据库已更改。

这里本质就是通过oracle内部包,直接还原数据文件,这个模拟的是基础的情况,在实际的恢复中,由于只有数据文件的备份,可能文件不一致,还需要通过一些非常规方法对数据库进行强制打开

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