联系:QQ(5163721)
标题:global_name为空导致的数据库不能open—–使用gdb修复(中断oracle启动的部分监测功能)
作者:Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
GLOBAL_NAME和props$对象介绍
global_name为空导致的数据库不能open—–使用dd修复(使用dd拷贝块的方式)
global_name为空导致的数据库不能open—–使用DUL修复
global_name为空导致的数据库不能open—使用BBED修复(bbed恢复update的数据)
这篇为第1种方法,参考MOS文档,使用gdb中断后open数据库,再手工执行update语句保证 global_name 不为空。
首先了解一下,不同操作系统平台上的诊断工具有所不同,Linux和AIX上都可以使用gdb来诊断。
AIX(Big Endian)使用gdb诊断的例子请参考:一次体验N种报错的Oracle数据库恢复(ORA-704 ORA-604 ORA-600[25016] ORA-376)
我这里是Linux,即Little Endian 。
测试时,首先把global_name 置空:
[oracle@lunar ~]$ ss SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 4 06:47:18 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Welcome Lunar's oracle world! Love you , baby ! Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning and Real Application Testing options Session altered. Elapsed: 00:00:00.01 06:47:20 SYS@lunarp>select * from global_name; GLOBAL_NAME ------------------------------------------------------------------------------------------------------------------------------------------------------ LUNAR Elapsed: 00:00:00.06 07:21:29 SYS@lunarp>oradebug setmypid alter session set tracefile_identifier='lunar'; Statement processed. 07:36:18 SYS@lunarp> Session altered. Elapsed: 00:00:00.06 07:36:18 SYS@lunarp>oradebug event 10046 trace name context forever,level 12; Statement processed. 07:36:22 SYS@lunarp>update global_name set global_name=''; 1 row updated. Elapsed: 00:00:00.48 07:36:27 SYS@lunarp>commit; Commit complete. Elapsed: 00:00:00.02 07:36:31 SYS@lunarp>oradebug event 10046 trace name context off Statement processed. 07:36:35 SYS@lunarp>oradebug close_trace oradebug tracefile_name Statement processed. 07:36:39 SYS@lunarp>/u01/app/oracle/diag/rdbms/lunarp/lunarp/trace/lunarp_ora_11544_lunar.trc SYS@lunarp>shutdown abort ORACLE instance shut down. SYS@lunarp>startup Welcome Lunar's oracle world! Love you , baby ! ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2230952 bytes Variable Size 243271000 bytes Database Buffers 373293056 bytes Redo Buffers 7532544 bytes Database mounted. Welcome Lunar's oracle world! Love you , baby ! ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], [] Process ID: 11995 Session ID: 125 Serial number: 5 SYS@lunarp>
此时,trace中显示 执行下SQL命令时,数据库报错:
PARSING IN CURSOR #140086475835376 len=55 dep=1 uid=0 oct=3 lid=0 tim=1407167741390301 hv=1950821498 ad='7f2eaec8' sqlid='459f3z9u4fb3u' select value$ from props$ where name = 'GLOBAL_DB_NAME' END OF STMT PARSE #140086475835376:c=1999,e=870,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=415205717,tim=1407167741390297 EXEC #140086475835376:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=415205717,tim=1407167741390424 FETCH #140086475835376:c=0,e=44,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=415205717,tim=1407167741390486 Incident 4937 created, dump file: /u01/app/oracle/diag/rdbms/lunarp/lunarp/incident/incdir_4937/lunarp_ora_12099_i4937.trc ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], [] [oracle@lunar ~]$ ps -ef|grep LOCAL=YES oracle 12183 12142 0 08:03 ? 00:00:00 oraclelunarp (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 12211 12186 0 08:08 pts/5 00:00:00 grep LOCAL=YES [oracle@lunar ~]$
下面使用gdb进行恢复:
[oracle@lunar ~]$ gdb $ORACLE_HOME/bin/oracle 12183 GNU gdb (GDB) Red Hat Enterprise Linux (7.0.1-42.el5) Copyright (C) 2009 Free Software Foundation, Inc. License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html> This is free software: you are free to change and redistribute it. There is NO WARRANTY, to the extent permitted by law. Type "show copying" and "show warranty" for details. This GDB was configured as "x86_64-redhat-linux-gnu". For bug reporting instructions, please see: <http://www.gnu.org/software/gdb/bugs/>... Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/bin/oracle...(no debugging symbols found)...done. Attaching to program: /u01/app/oracle/product/11.2.0.3/dbhome_1/bin/oracle, process 12183 Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libodm11.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libodm11.so Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libcell11.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libcell11.so Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libskgxp11.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libskgxp11.so Reading symbols from /lib64/librt.so.1...(no debugging symbols found)...done. Loaded symbols for /lib64/librt.so.1 Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libnnz11.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libnnz11.so Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libclsra11.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libclsra11.so Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libdbcfg11.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libdbcfg11.so Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libhasgen11.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libhasgen11.so Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libskgxn2.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libskgxn2.so Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libocr11.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libocr11.so Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libocrb11.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libocrb11.so Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libocrutl11.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libocrutl11.so Reading symbols from /usr/lib64/libaio.so.1...(no debugging symbols found)...done. Loaded symbols for /usr/lib64/libaio.so.1 Reading symbols from /lib64/libdl.so.2...(no debugging symbols found)...done. Loaded symbols for /lib64/libdl.so.2 Reading symbols from /lib64/libm.so.6...(no debugging symbols found)...done. Loaded symbols for /lib64/libm.so.6 Reading symbols from /lib64/libpthread.so.0...(no debugging symbols found)...done. [Thread debugging using libthread_db enabled] Loaded symbols for /lib64/libpthread.so.0 Reading symbols from /lib64/libnsl.so.1...(no debugging symbols found)...done. Loaded symbols for /lib64/libnsl.so.1 Reading symbols from /lib64/libc.so.6...(no debugging symbols found)...done. Loaded symbols for /lib64/libc.so.6 Reading symbols from /lib64/ld-linux-x86-64.so.2...(no debugging symbols found)...done. Loaded symbols for /lib64/ld-linux-x86-64.so.2 Reading symbols from /usr/lib64/libnuma.so.1...(no debugging symbols found)...done. Loaded symbols for /usr/lib64/libnuma.so.1 Reading symbols from /lib64/libnss_files.so.2...(no debugging symbols found)...done. Loaded symbols for /lib64/libnss_files.so.2 Reading symbols from /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libnque11.so...(no debugging symbols found)...done. Loaded symbols for /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libnque11.so 0x0000003e9880d9b0 in __read_nocancel () from /lib64/libpthread.so.0 (gdb) break kokiasg Breakpoint 1 at 0x13c162c (gdb)
然后,回到sqlplus的会话,直接执行alter database open:
SYS@lunarp>alter database open; .....
回到gdb窗口:
(gdb) continue Breakpoint 1, 0x00000000013c162c in kokiasg () (gdb)
回到sqlplus会话,看到数据库已经open:
SYS@lunarp>conn / as sysdba Welcome Lunar's oracle world! Love you , baby ! Connected. ERROR: ORA-22303: type "SYS"."DBMSOUTPUT_LINESARRAY" not found ORA-00942: table or view does not exist ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_OUTPUT" ORA-06512: at line 1 Session altered. Elapsed: 00:00:00.00 08:14:06 SYS@lunarp>select status from v$instance; STATUS ------------ OPEN Elapsed: 00:00:00.01 SYS@lunarp>
此时alert.log显示:
Mon Aug 04 08:11:18 2014 alter database open Beginning crash recovery of 1 threads parallel recovery started with 2 processes Started redo scan Completed redo scan read 40 KB redo, 30 data blocks need recovery Started redo application at Thread 1: logseq 84, block 2 Recovery of Online Redo Log: Thread 1 Group 3 Seq 84 Reading mem 0 Mem# 0: /stage/lunar/redo03.log Completed redo application of 0.03MB Completed crash recovery at Thread 1: logseq 84, block 83, scn 1683842 30 data blocks read, 30 data blocks written, 40 redo k-bytes read Mon Aug 04 08:11:20 2014 LGWR: STARTING ARCH PROCESSES Mon Aug 04 08:11:20 2014 ARC0 started with pid=20, OS id=12288 ARC0: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE ARC0: STARTING ARCH PROCESSES Mon Aug 04 08:11:21 2014 ARC1 started with pid=21, OS id=12290 Mon Aug 04 08:11:21 2014 ARC2 started with pid=22, OS id=12292 Thread 1 advanced to log sequence 85 (thread open) Mon Aug 04 08:11:21 2014 ARC3 started with pid=23, OS id=12294 ARC1: Archival started ARC2: Archival started ARC1: Becoming the 'no FAL' ARCH ARC1: Becoming the 'no SRL' ARCH ARC2: Becoming the heartbeat ARCH Thread 1 opened at log sequence 85 Current log# 1 seq# 85 mem# 0: /stage/lunar/redo01.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Mon Aug 04 08:11:21 2014 SMON: enabling cache recovery Archived Log entry 160 added for thread 1 sequence 84 ID 0x92fd63eb dest 1: Mon Aug 04 08:11:21 2014 NSA2 started with pid=24, OS id=12300 ARC2: Standby redo logfile selected for thread 1 sequence 84 for destination LOG_ARCHIVE_DEST_2 ARC3: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE Thread 1 advanced to log sequence 86 (LGWR switch) Current log# 2 seq# 86 mem# 0: /stage/lunar/redo02.log ****************************************************************** LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2 ****************************************************************** [12183] Successfully onlined Undo Tablespace 2. Undo initialization finished serial:0 start:221099734 end:221101944 diff:2210 (22 seconds) Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is AL32UTF8 Archived Log entry 162 added for thread 1 sequence 85 ID 0x92fd63eb dest 1: LNS: Standby redo logfile selected for thread 1 sequence 86 for destination LOG_ARCHIVE_DEST_2 ARC0: Standby redo logfile selected for thread 1 sequence 85 for destination LOG_ARCHIVE_DEST_2
现在修改global_name = ‘lunarbb’:
08:14:32 SYS@lunarp>update global_name set global_name = 'lunarbb'; 1 row updated. Elapsed: 00:00:00.12 08:15:05 SYS@lunarp>commit; Commit complete. Elapsed: 00:00:00.00 08:15:07 SYS@lunarp>select * from global_name; GLOBAL_NAME ------------------------------------------------------------------------------------------------------------------------------------------------------ lunarbb Elapsed: 00:00:00.00 08:15:27 SYS@lunarp> (gdb) kill Kill the program being debugged? (y or n) y (gdb) quit [oracle@lunar ~]$
再次正常启动数据库,数据库已经open了,且GLOBAL_NAME显示为我们刚才修改的:lunarbb
SYS@lunarp>startup Welcome Lunar's oracle world! Love you , baby ! ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2230952 bytes Variable Size 243271000 bytes Database Buffers 373293056 bytes Redo Buffers 7532544 bytes Database mounted. Welcome Lunar's oracle world! Love you , baby ! Database opened. SYS@lunarp>select * from global_name; GLOBAL_NAME ------------------------------------------------------------------------------------------------------------------------------------------------------ lunarbb Elapsed: 00:00:00.05 SYS@lunarp>