如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!
诗檀软件专业数据库修复团队
服务热线 ： 13764045638 QQ号:47079569 邮箱：[email protected]
数据库后台日志文件alert.log报以下异常：
Database Characterset is AL32UTF8
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 16
replication_dependency_tracking turned off (no async multimaster replication found)
Thu Sep 20 09:46:09 2012
Errors in file /user/oracle/admin/maclean/bdump/maclean_p003_856120.trc:
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
Starting background process QMNC
QMNC started with pid=53, OS id=1229222
Thu Sep 20 09:46:11 2012
Errors in file /user/oracle/admin/maclean/bdump/maclean_p003_856120.trc:
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
Thu Sep 20 09:46:12 2012
Errors in file /user/oracle/admin/maclean/bdump/maclean_p003_856120.trc:
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
Thu Sep 20 09:46:13 2012
Errors in file /user/oracle/admin/maclean/bdump/maclean_p003_856120.trc:
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
Thu Sep 20 09:46:15 2012
Doing block recovery for file 370 block 140818
Doing block recovery for file 370 block 140818
Doing block recovery for file 370 block 140818
Doing block recovery for file 370 block 140818
Doing block recovery for file 370 block 140818
Thu Sep 20 09:46:16 2012
Completed: ALTER DATABASE OPEN
Thu Sep 20 09:46:18 2012
Doing block recovery for file 370 block 140818
Block recovery from logseq 336560, block 264 to scn 12971246479133
Thu Sep 20 09:46:18 2012
Recovery of Online Redo Log: Thread 1 Group 4 Seq 336560 Reading mem 0
Mem# 0: /oradata4/macleanDB/redolog/redolog4a.log
Mem# 1: /oradata4/macleanDB/redolog/redolog4b.log
Block recovery completed at rba 336560.27935.16, scn 3020.445245214
Thu Sep 20 09:46:18 2012
SMON: slave died unexpectedly, downgrading to serial recovery
Thu Sep 20 09:46:18 2012
Errors in file /user/oracle/admin/maclean/bdump/maclean_smon_729208.trc:
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
Thu Sep 20 09:46:20 2012
Errors in file /user/oracle/admin/maclean/bdump/maclean_smon_729208.trc:
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
Thu Sep 20 09:46:21 2012
Errors in file /user/oracle/admin/maclean/bdump/maclean_smon_729208.trc:
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
Thu Sep 20 09:46:22 2012
Errors in file /user/oracle/admin/maclean/bdump/maclean_smon_729208.trc:
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
Thu Sep 20 09:46:24 2012
Errors in file /user/oracle/admin/maclean/bdump/maclean_pmon_786638.trc:
ORA-00474: SMON process terminated with error
Thu Sep 20 09:46:24 2012
PMON: terminating instance due to error 474
Instance terminated by PMON, pid = 786638
Thu Sep 20 10:38:31 2012
Starting ORACLE instance (normal)
sskgpgetexecname failed to get name
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =61
LICENSE_MAX_USERS = 0
SYS auditing is enabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.4.0.
System parameters with non-default values:
processes = 500
sessions = 555
__shared_pool_size = 637534208
__large_pool_size = 16777216
__java_pool_size = 16777216
__streams_pool_size = 0
sga_target = 21474836480
control_files = /oradata1/macleanDB/control1/control01.ctl, /oradata1/macleanDB/control2/control02.ctl, /oradata1/macleanDB/control3/control03.ctl
db_block_size = 8192
__db_cache_size = 20786970624
compatible = 10.2.0.3.0
log_archive_dest_1 = LOCATION=/oraarch/maclean
log_archive_format = %t_%s_%r.dbf
db_files = 4000
db_file_multiblock_read_count= 16
undo_management = AUTO
undo_tablespace = UNDOTBS2
undo_retention = 7200
remote_login_passwordfile= EXCLUSIVE
audit_sys_operations = TRUE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=macleanXDB)
job_queue_processes = 10
background_dump_dest = /user/oracle/admin/maclean/bdump
user_dump_dest = /user/oracle/admin/maclean/udump
core_dump_dest = /user/oracle/admin/maclean/cdump
audit_file_dest = /user/oracle/admin/maclean/adump
audit_trail = DB, EXTENDED
db_name = maclean
open_cursors = 300
pga_aggregate_target = 10737418240
MMAN started with pid=4, OS id=368872
PSP0 started with pid=3, OS id=184496
PMON started with pid=2, OS id=94512
DBW0 started with pid=5, OS id=331874
DBW1 started with pid=6, OS id=418128
LGWR started with pid=7, OS id=397498
CKPT started with pid=8, OS id=508388
SMON started with pid=9, OS id=459050
RECO started with pid=10, OS id=471536
CJQ0 started with pid=11, OS id=442822
MMON started with pid=12, OS id=180548
Thu Sep 20 10:38:38 2012
starting up 1 dispatcher(s) for network address ‘(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))’…
MMNL started with pid=13, OS id=352574
Thu Sep 20 10:38:38 2012
starting up 1 shared server(s) …
Thu Sep 20 10:38:42 2012
ALTER DATABASE MOUNT
Thu Sep 20 10:38:47 2012
Setting recovery target incarnation to 2
Thu Sep 20 10:38:47 2012
Successful mount of redo thread 1, with mount id 2376692082
Thu Sep 20 10:38:47 2012
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Thu Sep 20 10:38:47 2012
ALTER DATABASE OPEN
Thu Sep 20 10:38:49 2012
Beginning crash recovery of 1 threads
parallel recovery started with 15 processes
Thu Sep 20 10:38:50 2012
Started redo scan
Thu Sep 20 10:38:51 2012
Completed redo scan
27994 redo blocks read, 8334 data blocks need recovery
Thu Sep 20 10:38:52 2012
Started redo application at
Thread 1: logseq 336560, block 3
Thu Sep 20 10:38:52 2012
Recovery of Online Redo Log: Thread 1 Group 4 Seq 336560 Reading mem 0
Mem# 0: /oradata4/macleanDB/redolog/redolog4a.log
Mem# 1: /oradata4/macleanDB/redolog/redolog4b.log
Thu Sep 20 10:38:53 2012
Completed redo application
Thu Sep 20 10:38:56 2012
Completed crash recovery at
Thread 1: logseq 336560, block 27997, scn 12971246499154
8334 data blocks read, 8334 data blocks written, 27994 redo blocks read
Thu Sep 20 10:38:57 2012
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=32, OS id=770284
ARC1 started with pid=33, OS id=762350
Thu Sep 20 10:38:57 2012
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
Thu Sep 20 10:38:58 2012
Thread 1 advanced to log sequence 336561 (thread open)
Thread 1 opened at log sequence 336561
Current log# 2 seq# 336561 mem# 0: /oradata1/macleanDB/redolog/redolog2a.log
Current log# 2 seq# 336561 mem# 1: /oradata1/macleanDB/redolog/redolog2b.log
Successful open of redo thread 1
Thu Sep 20 10:38:58 2012
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Sep 20 10:38:58 2012
ARC1: Becoming the ‘no FAL’ ARCH
ARC1: Becoming the ‘no SRL’ ARCH
Thu Sep 20 10:38:58 2012
ARC0: Becoming the heartbeat ARCH
Thu Sep 20 10:38:58 2012
SMON: enabling cache recovery
Thu Sep 20 10:39:03 2012
Successfully onlined Undo Tablespace 5.
Thu Sep 20 10:39:03 2012
SMON: enabling tx recovery
Thu Sep 20 10:39:03 2012
Database Characterset is AL32UTF8
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 16
Thu Sep 20 10:39:04 2012
Errors in file /user/oracle/admin/maclean/bdump/maclean_p003_491552.trc:
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=52, OS id=770310
Thu Sep 20 10:39:06 2012
Errors in file /user/oracle/admin/maclean/bdump/maclean_p003_491552.trc:
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
Thu Sep 20 10:39:08 2012
Errors in file /user/oracle/admin/maclean/bdump/maclean_p003_491552.trc:
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
Thu Sep 20 10:39:08 2012
Errors in file /user/oracle/admin/maclean/bdump/maclean_p003_491552.trc:
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kghstack_err+0068] [SIGSEGV] [Address not mapped to object] [0x778670101010109] [] []
ORA-00600: internal error code, arguments: [kghstack_free2], [], [], [], [], [], [], []
Thu Sep 20 10:39:11 2012
Doing block recovery for file 370 block 140818
Doing block recovery for file 370 block 140818
Doing block recovery for file 370 block 140818
Doing block recovery for file 370 block 140818
Doing block recovery for file 370 block 140818
设置10513 level 2内部事件后，SMON不再recover dead transaction ，打开数据库后实例不再意外终止。
SQL> show parameter event
NAME TYPE VALUE
———————————— ———– ——————————
event string 10513 trace name context forev
er, level 2
通过RMAN validate命令和dbv检测数据文件，发现表MAC_maclean_TAB上存在一个逻辑讹误块和一个物理讹误块。
SQL> select * from V$DATABASE_BLOCK_CORRUPTION ;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
370 140818 1 1.2971E+13 LOGICAL
84 23875 1 0 CORRUPT
鉴于用户可以接受少量数据的损失，且blockrecover以上物理讹误块在RMAN中实际运行时寻找该FILE 84 BLOCK 23875耗费大量时间，所以考虑利用ROWID的方式绕过讹误块，拷贝出绝大多数的表数据。
SQL> select dbms_rowid.rowid_create(1,353223,370,140818,0) from dual;
DBMS_ROWID.ROWID_C
——————
AAAh1CAFyAAAiYSAAA
SQL> select dbms_rowid.rowid_create(1,353223,370,140819,0) from dual;
DBMS_ROWID.ROWID_C
——————
AABWPHAFyAAAiYTAAA
alter session set events ‘10231 trace name context forever, level 10’;
SQL> select count(*) from MAC_maclean_TAB;
COUNT(*)
———-
8450840
insert /*+ APPEND */ into MAC_maclean_TAB_BAK select /*+ ROWID(A) */ * from MAC_maclean_TAB A where rowid <‘AABWPHAFyAAAiYSAAA’;
insert /*+ APPEND */ into MAC_maclean_TAB_BAK select /*+ ROWID(A) */ * from MAC_maclean_TAB A where rowid >=’AABWPHAFyAAAiYTAAA’;
SQL> select count(*) from MAC_maclean_TAB_BAK;
COUNT(*)
———-
8450796
alter table MAC_maclean_TAB rename to MAC_maclean_TAB_20120920_BAK;
alter table MAC_maclean_TAB_BAK rename to MAC_maclean_TAB;
SQL>
SQL> select count(*) from MAC_maclean_TAB;
COUNT(*)
———-
8450796
SQL> analyze table MAC_maclean_TAB validate structure;
Table analyzed.
drop table MAC_maclean_TAB_20120920_BAK;
原MAC_maclean_TAB共计8450840条数据，实际修复出8450796条记录，丢失44条。
后续使用CREATE TABLE命令将原存在逻辑坏块的数据块覆盖，避免上述ORA-600问题再次发生。
create table LARGE_TABLE (t1 int) tablespace MAC_REPORT_TBS ;
alter table LARGE_TABLE allocate extent (datafile ‘/maclean/maclean_32.dbf’ size 10M);
对全库做数据文件的验证操作,找出数据库中潜在的坏块，避免此类问题再次发生，若使用RMAN可以参考如下脚本：
RMAN> run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
backup validate check logical database;
}
以上会将找到的坏块信息汇总到V$DATABASE_BLOCK_CORRUPTION视图中：
select * from V$DATABASE_BLOCK_CORRUPTION ;
Leave a Reply