|
|
|
发表于：
 2015-11-19 15:37:14 |
[全部帖]
[楼主帖]
|
楼主
|
|
| |
|
|
1.Oracle启动报错 -bash-2.05b$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.8.0 - Production on 星期三 2月 15 10:31:53 2012
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1527846824 bytes
Fixed Size 452520 bytes
Variable Size 385875968 bytes
Database Buffers 1140850688 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel |
2.alert文件 Wed Feb 15 10:32:06 2012
ALTER DATABASE MOUNT
Wed Feb 15 10:32:10 2012
Successful mount of redo thread 1, with mount id 412256678
Wed Feb 15 10:32:10 2012
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Wed Feb 15 10:33:44 2012
alter database open
Wed Feb 15 10:33:44 2012
Beginning crash recovery of 1 threads
Wed Feb 15 10:33:44 2012
Started redo scan
Wed Feb 15 10:33:44 2012
Completed redo scan
0 redo blocks read, 0 data blocks need recovery
Wed Feb 15 10:33:44 2012
Started recovery at
Thread 1: logseq 2, block 3, scn 2862.4075508322
Wed Feb 15 10:33:44 2012
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
Mem# 0 errs 0: /data1z/oracle/oradata/ahcx216/redo01.log
Wed Feb 15 10:33:44 2012
Completed redo application
Wed Feb 15 10:33:44 2012
Ended recovery at
Thread 1: logseq 2, block 3, scn 2862.4075528323
0 data blocks read, 0 data blocks written, 0 redo blocks read
Crash recovery completed successfully
Wed Feb 15 10:33:44 2012
Thread 1 advanced to log sequence 3
Thread 1 opened at log sequence 3
Current log# 3 seq# 3 mem# 0: /data1z/oracle/oradata/ahcx216/redo03.log
Successful open of redo thread 1
Wed Feb 15 10:33:45 2012
SMON: enabling cache recovery
Wed Feb 15 10:33:45 2012
Errors in file /data1z/oracle/admin/ahcx216/udump/ahcx216_ora_21325.trc:
ORA-07445: exception encountered: core dump [kkdliac()+346] [SIGSEGV] [Address not mapped to object] [0x43] [] []
|
3.trace文件 /data1z/oracle/admin/ahcx216/udump/ahcx216_ora_21325.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /data1z/oracle/product/9.2
System name: Linux
Node name: aiserch1
Release: 2.4.21-27.ELsmp
Version: #1 SMP Wed Dec 1 21:59:02 EST 2004
Machine: i686
Instance name: ahcx216
Redo thread mounted by this instance: 1
Oracle process number: 12
Unix process pid: 21325, image: oracle@aiserch1 (TNS V1-V3)
*** SESSION ID:(11.13) 2012-02-15 10:33:44.739
Thread checkpoint rba:0x000002.00000002.0010 scn:0x0b2e.f2eb5261
Cache low rba is 0xffffffff.ffffffff.ffff
Use incremental checkpoint on-disk rba
Thread 1 recovery from rba:0x000002.00000003.0000 scn:0x0b2e.f2eb5262
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 0
Average hash chain = 0/0 = 0.0
Max compares per lookup = 0
Avg compares per lookup = 0/0 = 0.0
----------------------------------------------
*** 2012-02-15 10:33:44.766
KCRA: start recovery claims for 0 data blocks
*** 2012-02-15 10:33:44.766
KCRA: buffers claimed = 0/0, eliminated = 0
*** 2012-02-15 10:33:44.766
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 0
Average hash chain = 0/0 = 0.0
Max compares per lookup = 0
Avg compares per lookup = 0/0 = 0.0
----------------------------------------------
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x43, PC: [0x8880c00, kkdliac()+346]
Registers:
%eax: 0xa0f6b170 %ebx: 0x00000003 %ecx: 0xa0f6b170
%edx: 0x00000001 %edi: 0x00000000 %esi: 0x00000000
%esp: 0xbfff1cb8 %ebp: 0xbfff1cd8 %eip: 0x08880c00
%efl: 0x00010286
kkdliac()+329 (0x8880bef) jnz 0x8880f61
kkdliac()+335 (0x8880bf5) mov %edx,%eax
kkdliac()+337 (0x8880bf7) mov 0xc0(%eax),%edx
kkdliac()+343 (0x8880bfd) mov %edx,0xffffffe0(%ebp)
> kkdliac()+346 (0x8880c00) movzw 0x40(%ebx),%esi
kkdliac()+350 (0x8880c04) mov %si,0xffffffd4(%ebp)
kkdliac()+354 (0x8880c08) movb 0x42(%ebx),%al
kkdliac()+357 (0x8880c0b) movb %al,0xffffffd6(%ebp)
kkdliac()+360 (0x8880c0e) movzw 0x8(%ebx),%edx
*** 2012-02-15 10:33:45.029
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [kkdliac()+346] [SIGSEGV] [Address not mapped to object] [0x43] [] []
Current SQL statement for this session:
create table bootstrap$ ( line# number not null, obj# number not null,
sql_text varchar2(4000) not null) storage (initial 50K objno 56 extents (file 1 block 377))
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+274 call ksedst() 1 ? 0 ? 0 ? 1 ? 65252838 ?
2C297862 ?
ssexhd()+1113 call ksedmp() 3 ? 0 ? 0 ? 0 ? 0 ? 0 ?
__pthread_sighandle call 00000000 B ? B75CEC90 ? B75CED10 ? 0 ?
r_rt()+122 0 ? 0 ?
kkdliac()+346 signal 00000000 B ? B75CEC90 ? B75CED10 ?
ctcdrv()+1688 call kkdlcob() BFFF1DC8 ? 2 ? 0 ? 38 ? 0 ?
0 ? 0 ?
opiexe()+9647 call ctcdrv() B749CD78 ? BFFF3258 ?
BFFF33B0 ?
opiosq0()+1170 call opiexe() 4 ? 0 ? BFFF379C ?
opiosq()+19 call opiosq0() 3 ? F ? BFFF4418 ? 0 ?
opiodr()+1133 call 00000000 4A ? F ? BFFF4418 ?
__PGOSF163_rpidrus( call opiodr() 4A ? F ? BFFF4418 ? 2 ?
)+145
skgmstack()+137 call 00000000 BFFF3ED0 ? 2 ? BFFF428C ?
BFFF3EE8 ? BFFF3ED0 ?
BFFF3EE8 ?
rpidru()+98 call skgmstack() BFFF3EE8 ? AF61BC0 ? F618 ?
821F376 ? BFFF3ED0 ?
rpiswu2()+315 call 00000000 BFFF428C ? 0 ? 0 ? 0 ?
BFFF3F64 ? F618 ?
rpidrv()+1087 call rpiswu2() 9D7416FC ? 0 ? BFFF4394 ? 2 ?
BFFF43B4 ? 0 ?
rpisplu()+298 call rpidrv() 2 ? 4A ? BFFF4418 ? 8 ?
9EA3F16C ? B749DE88 ?
rpispl()+28 call rpisplu() 2 ? 0 ? 0 ? B749DE88 ? BC ?
0 ? 0 ?
kqlbebs()+781 call rpispl() 2 ? 0 ? B749DE88 ? BC ? 0 ?
0 ?
kqlblfc()+106 call kqlbebs() 0 ? BFFF5590 ?
adbdrv()+2220 call kqlblfc() 0 ? BFFF5590 ? BFFF5590 ?
opiexe()+10319 call adbdrv() B74A022C ? AF5A904 ?
B74AD040 ? 1 ? 0 ? 1 ?
opiosq0()+1170 call opiexe() 4 ? 0 ? BFFF6928 ?
kpooprx()+206 call opiosq0() 3 ? E ? BFFF6A68 ? 24 ?
kpoal8()+599 call kpooprx() BFFF7604 ? BFFF7510 ? 13 ?
1 ? 0 ? 24 ?
opiodr()+1133 call 00000000 5E ? 14 ? BFFF7600 ?
ttcpip()+4250 call 00000000 5E ? 14 ? BFFF7600 ? 0 ?
opitsk()+1077 call ttcpip() AF5A900 ? 5E ? BFFF7600 ? 0 ?
BFFF87D8 ? BFFF87D4 ?
opiino()+1287 call opitsk() 0 ? 0 ? AF5A900 ? CC349D8 ?
F1 ? 0 ?
opiodr()+1133 call 00000000 3C ? 4 ? BFFF9B9C ?
opidrv()+418 call opiodr() 3C ? 4 ? BFFF9B9C ? 0 ?
sou2o()+30 call opidrv() 3C ? 4 ? BFFF9B9C ?
main()+187 call sou2o() BFFF9B80 ? 3C ? 4 ?
BFFF9B9C ? 890710 ? 0 ?
__libc_start_main() call 00000000 2 ? BFFF9C04 ? BFFF9C10 ?
+188 890518 ? 2 ? 82174E0 ?
--------------------- Binary Stack Dump ---------------------
从这里看出数据库是在open过程中，创建bootstrap$错误，这个错误对于现在的我来说，暂时没有办法去解决。
为了能够抢救出客户需要的其中一个用户下面的数据，我不得不采用odu来解决问题。
|
4.填写ontrol.txt文件 SQL> set pagesize 1000
SQL> set linesize 200
SQL> col name for a50
SQL> select file#,ts#,rfile#,name from v$datafile;
FILE# TS# RFILE# NAME
---------- ---------- ---------- ----------------------------------------------
1 0 1 /data1z/oracle/oradata/ahcx216/system01
2 1 2 /data1z/oracle/oradata/ahcx216/undotbs01
3 3 3 /data1z/oracle/oradata/ahcx216/CITY
4 4 4 /data1z/oracle/oradata/ahcx216/DATATS
5 5 5 /data1z/oracle/oradata/ahcx216/indx01
6 6 6 /data1z/oracle/oradata/ahcx216/tools01
7 7 7 /data1z/oracle/oradata/ahcx216/users
8 4 8 /data1z/oracle/oradata/ahcx216/datats02
9 4 9 /data1z/oracle/oradata/ahcx216/datats03
………………
|
5.登录odu bash-2.05b$ ./odu
Oracle Data Unloader:Release 3.0.8
Copyright (c) 2008,2009 XiongJun. All rights reserved.
Web: http://www.laoxiong.net
Email: magic007cn@gmail.com
loading default config.......
byte_order little
block_size 8192
db_timezone -7
client_timezone 8
data_path data
charset_name ZHS16GBK
ncharset_name AL16UTF16
output_format text
lob_storage infile
clob_byte_order little
load control file 'config.txt' successful
loading default control file ......
ts# fn rfn bsize blocks bf offset filename
---- ---- ---- ----- -------- -- ------ --------------------------------------------
0 1 1 8192 32000 N 0 /data1z/oracle/oradata/ahcx216/system01
1 2 2 8192 524288 N 0 /data1z/oracle/oradata/ahcx216/undotbs01
3 3 3 8192 524288 N 0 /data1z/oracle/oradata/ahcx216/CITY
4 4 4 8192 524288 N 0 /data1z/oracle/oradata/ahcx216/DATATS
5 5 5 8192 524288 N 0 /data1z/oracle/oradata/ahcx216/indx01
………………
load control file 'control.txt' successful
loading dictionary data......
|
6.加载数据字典 ODU> unload dict
CLUSTER C_USER# file_no: 1 block_no: 89
TABLE OBJ$ file_no: 1 block_no: 121
CLUSTER C_OBJ# file_no: 1 block_no: 25
CLUSTER C_OBJ# file_no: 1 block_no: 25
found IND$'s obj# 19
found IND$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:3
found TABPART$'s obj# 230
found TABPART$'s dataobj#:230,ts#:0,file#:1,block#:1657,tab#:0
found INDPART$'s obj# 234
found INDPART$'s dataobj#:234,ts#:0,file#:1,block#:1689,tab#:0
found TABSUBPART$'s obj# 240
found TABSUBPART$'s dataobj#:240,ts#:0,file#:1,block#:1737,tab#:0
found INDSUBPART$'s obj# 245
found INDSUBPART$'s dataobj#:245,ts#:0,file#:1,block#:1777,tab#:0
found IND$'s obj# 19
found IND$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:3
found LOB$'s obj# 156
found LOB$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:6
found LOBFRAG$'s obj# 258
found LOBFRAG$'s dataobj#:258,ts#:0,file#:1,block#:1881,tab#:0
|
7.列出用户 ODU> list user
USER# USERNAME
---------- ------------------------------
…………
20 HS_ADMIN_ROLE
25 MAS_ADMIN
24 HF_CX
21 WMSYS
22 WM_ADMIN_ROLE
23 CITY
26 BB_ADMIN
27 AQ_ADMIN
28 WH_ADMIN
29 LA_ADMIN
…………
|
8.列出用户表 ODU> list table TL_ADMIN
OBJ# OBJECT_NAME
---------- ------------------------------
2286779 WT_MX_201005
32309 BD_DEALER_308
126172 CDMA_FAVOUR_BAOYUE
79675 DAISHENG_TMP
52634 RYSJ_T_308
25577 FEE_ID_T
931167 A136_20080304
1514084 WANGTONG_MOKUAI_2009
………………
|
10.导出用户下表 ODU> unload user TL_ADMIN
Unloading user TL_ADMIN's tables.
Unloading table: WT_MX_201005,object ID: 2286779
Unloading segment,storage(Obj#=2286779 DataObj#=2286779 TS#=3 File#=31 Block#=8571 Cluster=0)
25205 rows unloaded
Unloading table: BD_DEALER_308,object ID: 32309
Unloading segment,storage(Obj#=32309 DataObj#=32309 TS#=3 File#=31 Block#=17587 Cluster=0)
60 rows unloaded
Unloading table: CDMA_FAVOUR_BAOYUE,object ID: 126172
Unloading segment,storage(Obj#=126172 DataObj#=126172 TS#=3 File#=3 Block#=30899 Cluster=0)
31 rows unloaded
Unloading table: DAISHENG_TMP,object ID: 79675
Unloading segment,storage(Obj#=79675 DataObj#=342004 TS#=3 File#=31 Block#=19451 Cluster=0)
7504 rows unloaded
…………………………
|
11.查看导出来数据 -bash-2.05b$ ls -l
-rw-r--r-- 1 oracle dba 658 2月 15 08:43 TL_ADMIN_AMORTIZE.ctl
-rw-r--r-- 1 oracle dba 763 2月 15 08:43 TL_ADMIN_AMORTIZE_HM_COPY.ctl
-rw-r--r-- 1 oracle dba 683 2月 15 08:43 TL_ADMIN_AMORTIZE_HM_COPY.sql
-rw-r--r-- 1 oracle dba 45813 2月 15 08:43 TL_ADMIN_AMORTIZE_HM_COPY.txt
-rw-r--r-- 1 oracle dba 748 2月 15 08:43 TL_ADMIN_AMORTIZE_HM.ctl
-rw-r--r-- 1 oracle dba 686 2月 15 08:43 TL_ADMIN_AMORTIZE_HM.sql
-rw-r--r-- 1 oracle dba 263046 2月 15 08:43 TL_ADMIN_AMORTIZE_HM.txt
-rw-r--r-- 1 oracle dba 777 2月 15 08:43 TL_ADMIN_AMORTIZE_ID.ctl
-rw-r--r-- 1 oracle dba 696 2月 15 08:43 TL_ADMIN_AMORTIZE_ID.sql
……………………………………
|
12.创建表脚本 -bash-2.05b$ ls -l *.sql
-rw-r--r-- 1 oracle dba 312 2月 15 08:43 TL_ADMIN_A136_20080304.sql
-rw-r--r-- 1 oracle dba 683 2月 15 08:43 TL_ADMIN_AMORTIZE_HM_COPY.sql
-rw-r--r-- 1 oracle dba 686 2月 15 08:43 TL_ADMIN_AMORTIZE_HM.sql
-rw-r--r-- 1 oracle dba 696 2月 15 08:43 TL_ADMIN_AMORTIZE_ID.sql
-rw-r--r-- 1 oracle dba 147 2月 15 08:44 TL_ADMIN_AMORTIZE_OWE.sql
………………
复制这些文件列表，使用UltraEdit编辑文件（recover_create_tab.sql），生成如下脚本
@@TL_ADMIN_A136_20080304.sql
@@TL_ADMIN_AMORTIZE_HM_COPY.sql
@@TL_ADMIN_AMORTIZE_HM.sql
@@TL_ADMIN_AMORTIZE_ID.sql
@@TL_ADMIN_AMORTIZE_OWE.sql
@@TL_ADMIN_AMORTIZE_PAY.sql
然后使用sqlplus登录数据库，直接执行recover_create_tab.sql，创建了所有需要的表
|
13.加载数据脚本 -bash-2.05b$ ls -l *.ctl
-rw-r--r-- 1 oracle dba 507 2月 15 08:43 TL_ADMIN_A136_20080304.ctl
-rw-r--r-- 1 oracle dba 658 2月 15 08:43 TL_ADMIN_AMORTIZE.ctl
-rw-r--r-- 1 oracle dba 763 2月 15 08:43 TL_ADMIN_AMORTIZE_HM_COPY.ctl
-rw-r--r-- 1 oracle dba 748 2月 15 08:43 TL_ADMIN_AMORTIZE_HM.ctl
-rw-r--r-- 1 oracle dba 777 2月 15 08:43 TL_ADMIN_AMORTIZE_ID.ctl
-rw-r--r-- 1 oracle dba 344 2月 15 08:44 TL_ADMIN_AMORTIZE_OWE.ctl
-rw-r--r-- 1 oracle dba 342 2月 15 08:43 TL_ADMIN_AMORTIZE_PAY.ctl
也同样使用UltraEdit出来，生成文件（recover_load_data.sh）
sqlldr TL_ADMIN/password control= TL_ADMIN_A136_20080304.ctl
sqlldr TL_ADMIN/password control= TL_ADMIN_AMORTIZE.ctl
sqlldr TL_ADMIN/password control= TL_ADMIN_AMORTIZE_HM_COPY.ctl
sqlldr TL_ADMIN/password control= TL_ADMIN_AMORTIZE_HM.ctl
sqlldr TL_ADMIN/password control= TL_ADMIN_AMORTIZE_ID.ctl
sqlldr TL_ADMIN/password control= TL_ADMIN_AMORTIZE_OWE.ctl
sqlldr TL_ADMIN/password control= TL_ADMIN_AMORTIZE_PAY.ctl
sqlldr TL_ADMIN/password control= TL_ADMIN_BD_DEALER_308.ctl
sqlldr TL_ADMIN/password control= TL_ADMIN_BRXZ.ctl
然后在shell中执行recover_load_data.sh脚本导入数据，至此数据恢复完成
|
注意：recover_create_tab.sql和recover_load_data.sh需要在当前抽取出来数据的目录中。
--转自 
|
|
|
发起投票
技术讨论
加好友
发消息
赞(
操作
