-
加我公众号(orasos)
加我微信(17813235971)
加我QQ(107644445)
标签云
asm恢复 bbed bootstrap$ dul kcbzib_kcrsds_1 kccpb_sanity_check_2 kcratr_nab_less_than_odr MySQL恢复 obet ORA-00312 ORA-00704 ORA-00742 ORA-01110 ORA-01200 ORA-01555 ORA-01578 ORA-01595 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-600 kcratr_nab_less_than_odr ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle碎片 OSD-04016 YOUR FILES ARE ENCRYPTED 比特币加密文章分类
- MongoDB (1)
- MongoDB恢复 (1)
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (112)
- 数据库 (1,881)
- DB2 (22)
- MySQL (82)
- Oracle (1,706)
- Data Guard (53)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (168)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (72)
- Oracle Bug (8)
- Oracle RAC (56)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (29)
- Oracle备份恢复 (660)
- Oracle安装升级 (108)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (91)
- PostgreSQL (37)
- pdu工具 (7)
- PostgreSQL恢复 (13)
- SQL Server (34)
- SQL Server恢复 (14)
- TimesTen (7)
- 达梦数据库 (5)
- 达梦恢复 (3)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (51)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (34)
- MongoDB (1)
-
最近发表
- 记录一次云存储资源到期之后引起的数据库恢复
- Firebird 损坏数据库数据抢救工具—firebirdext
- Oracle 19c 202610补丁（RUs+OJVM）-19.33
- 由于con$.con#值达到极限，触发数据库增加约束报ORA-600 12807错误
- MongoDB 离线提取工具—mongoext
- .bres勒索病毒加密数据库dmp文件可恢复
- ORA-600 krsm_mrp_rebuild_lf_list.lno_mismatch
- 记录一次0丢失的ORA-00354: 损坏重做日志块标头故障恢复
- 不当数据库恢复操作导致一个月数据丢失
- obet快速修复oracle 位图损坏块
- 不太常见的10.2.0.1的oracle redo损坏恢复
- obet forcecopy功能抢救硬件故障中的数据文件
- kcratr_nab_less_than_odr和system坏块故障处理
- 通过obet 恢复system坏块,打开数据库
- obet dbv功能完整说明
- 分享一例运行在aix上的sap系统数据库恢复过程
- OBET-Oracle Block Editor Tool使用说明
- 几乎动用了所有手段的Oracle故障恢复
- Oracle Block Edit Tool (obet) 功能增强–2026.07
- Oracle 19c 202607补丁（RUs+OJVM）-19.32
分类目录归档：Oracle备份恢复
使用 dul 挖数据文件初试
最近测试了下dul,整体感觉和odu差不多
1.配置init.dul
[oracle@xifenfei dul]$ more init.dul osd_big_endian_flag=false osd_dba_file_bits=10 osd_c_struct_alignment=32 osd_file_leader_size=1 osd_word_size = 32 dc_columns=2000000 dc_tables=10000 dc_objects=1000000 dc_users=400 dc_segments=100000 Buffer=10485760 control_file = control.txt db_block_size=8192 export_mode=true --false表示是sqlloader,true表示imp compatible=10
2.配置控制文件
[oracle@xifenfei dul]$ more control.txt
0 1 /u01/oracle/oradata/XFF/system01.dbf
1 2 /u01/oracle/oradata/XFF/undotbs01.dbf
2 3 /u01/oracle/oradata/XFF/sysaux01.dbf
4 4 /u01/oracle/oradata/XFF/users01.dbf
6 5 /u01/oracle/oradata/XFF/datfttuser.dbf
--sql语句
select ts#,rfile#,name from v$datafile;
3.启动dul
[oracle@xifenfei dul]$ ./dul Data UnLoader: 10.2.0.5.13 - Internal Only - on Sun Jun 10 06:39:47 2012 with 64-bit io functions Copyright (c) 1994 2012 Bernard van Duijnen All rights reserved. Strictly Oracle Internal Use Only Found db_id = 3426707456 Found db_name = XFF
4.加载初始化数据字典
DUL> BOOTSTRAP;
Probing file = 1, block = 377
. unloading table BOOTSTRAP$
DUL: Warning: block number is non zero but marked deferred trying to process it anyhow
57 rows unloaded
DUL: Warning: Dictionary cache DC_BOOTSTRAP is empty
Reading BOOTSTRAP.dat 57 entries loaded
Parsing Bootstrap$ contents
Generating dict.ddl for version 10
OBJ$: segobjno 18, file 1 block 121
TAB$: segobjno 2, tabno 1, file 1 block 25
COL$: segobjno 2, tabno 5, file 1 block 25
USER$: segobjno 10, tabno 1, file 1 block 89
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table OBJ$ 50930 rows unloaded
. unloading table TAB$ 1593 rows unloaded
. unloading table COL$ 55163 rows unloaded
. unloading table USER$ 61 rows unloaded
Reading USER.dat 61 entries loaded
Reading OBJ.dat 50930 entries loaded and sorted 50930 entries
Reading TAB.dat 1593 entries loaded
Reading COL.dat 55163 entries loaded and sorted 55163 entries
Reading BOOTSTRAP.dat 57 entries loaded
DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 10
OBJ$: segobjno 18, file 1 block 121
TAB$: segobjno 2, tabno 1, file 1 block 25
COL$: segobjno 2, tabno 5, file 1 block 25
USER$: segobjno 10, tabno 1, file 1 block 89
TABPART$: segobjno 266, file 1 block 2121
INDPART$: segobjno 271, file 1 block 2161
TABCOMPART$: segobjno 288, file 1 block 2297
INDCOMPART$: segobjno 293, file 1 block 2345
TABSUBPART$: segobjno 278, file 1 block 2217
INDSUBPART$: segobjno 283, file 1 block 2257
IND$: segobjno 2, tabno 3, file 1 block 25
ICOL$: segobjno 2, tabno 4, file 1 block 25
LOB$: segobjno 2, tabno 6, file 1 block 25
COLTYPE$: segobjno 2, tabno 7, file 1 block 25
TYPE$: segobjno 181, tabno 1, file 1 block 1297
COLLECTION$: segobjno 181, tabno 2, file 1 block 1297
ATTRIBUTE$: segobjno 181, tabno 3, file 1 block 1297
LOBFRAG$: segobjno 299, file 1 block 2393
LOBCOMPPART$: segobjno 302, file 1 block 2425
UNDO$: segobjno 15, file 1 block 105
TS$: segobjno 6, tabno 2, file 1 block 57
PROPS$: segobjno 96, file 1 block 721
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table OBJ$
DUL: Warning: Recreating file "OBJ.ctl"
50930 rows unloaded
. unloading table TAB$
DUL: Warning: Recreating file "TAB.ctl"
1593 rows unloaded
. unloading table COL$
DUL: Warning: Recreating file "COL.ctl"
55163 rows unloaded
. unloading table USER$
DUL: Warning: Recreating file "USER.ctl"
61 rows unloaded
. unloading table TABPART$ 90 rows unloaded
. unloading table INDPART$ 99 rows unloaded
. unloading table TABCOMPART$ 0 rows unloaded
. unloading table INDCOMPART$ 0 rows unloaded
. unloading table TABSUBPART$ 0 rows unloaded
. unloading table INDSUBPART$ 0 rows unloaded
. unloading table IND$ 2251 rows unloaded
. unloading table ICOL$ 3669 rows unloaded
. unloading table LOB$ 537 rows unloaded
. unloading table COLTYPE$ 1702 rows unloaded
. unloading table TYPE$ 1886 rows unloaded
. unloading table COLLECTION$ 552 rows unloaded
. unloading table ATTRIBUTE$ 7051 rows unloaded
. unloading table LOBFRAG$ 1 row unloaded
. unloading table LOBCOMPPART$ 0 rows unloaded
. unloading table UNDO$ 21 rows unloaded
. unloading table TS$ 7 rows unloaded
. unloading table PROPS$ 27 rows unloaded
Reading USER.dat 61 entries loaded
Reading OBJ.dat 50930 entries loaded and sorted 50930 entries
Reading TAB.dat 1593 entries loaded
Reading COL.dat 55163 entries loaded and sorted 55163 entries
Reading TABPART.dat 90 entries loaded and sorted 90 entries
Reading TABCOMPART.dat 0 entries loaded and sorted 0 entries
Reading TABSUBPART.dat 0 entries loaded and sorted 0 entries
Reading INDPART.dat 99 entries loaded and sorted 99 entries
Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries
Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries
Reading IND.dat 2251 entries loaded
Reading LOB.dat 537 entries loaded
Reading ICOL.dat 3669 entries loaded
Reading COLTYPE.dat 1702 entries loaded
Reading TYPE.dat 1886 entries loaded
Reading ATTRIBUTE.dat 7051 entries loaded
Reading COLLECTION.dat 552 entries loaded
Reading BOOTSTRAP.dat 57 entries loaded
Reading LOBFRAG.dat 1 entries loaded and sorted 1 entries
Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries
Reading UNDO.dat 21 entries loaded
Reading TS.dat 7 entries loaded
Reading PROPS.dat 27 entries loaded
Database character set is ZHS16GBK
Database national character set is AL16UTF16
5.导出某种表
DUL> desc chf.t_xifenfei;
Table CHF.T_XIFENFEI
obj#= 52189, dataobj#= 52189, ts#= 4, file#= 4, block#=123
tab#= 0, segcols= 2, clucols= 0
Column information:
icol# 01 segcol# 01 ID len 22 type 2 NUMBER(0,-127)
icol# 02 segcol# 02 NAME len 100 type 1 VARCHAR2 cs 852(ZHS16GBK)
DUL> UNLOAD TABLE chf.t_xifenfei;
. unloading table T_XIFENFEI 2 rows unloaded
6.验证导出dmp文件
[oracle@xifenfei dul]$ strings CHF_T_XIFENFEI.dmp
EXPORT:V07.00.07
UBernard's DUL
RTABLES
1024
Direct UnLoader(C) in EXPort mode
TABLE "T_XIFENFEI"
CREATE TABLE "T_XIFENFEI"("ID" NUMBER,"NAME" VARCHAR2(100))
INSERT INTO "T_XIFENFEI" ("ID", "NAME") VALUES (:1, :2)
www.xifenfei.com
WWW.XIFENEI.COM
EXIT
使用bbed让rac中的sysaux数据文件online
一个朋友的11g rac库的sysaux表空间因某种原因缺少历史归档,导致无法正常online,是的数据库的很多功能受限.通过实现展示恢复过程.
模拟环境
SQL> select name,file#,status from v$datafile; NAME FILE# STATUS ---------------------------------------------------- ---------- ------- +XIFENFEI/xff/datafile/system.256.776961315 1 SYSTEM +XIFENFEI/xff/datafile/sysaux.257.776961315 2 ONLINE +XIFENFEI/xff/datafile/undotbs1.258.776961317 3 ONLINE +XIFENFEI/xff/datafile/user_dd.dbf 4 ONLINE +XIFENFEI/xff/datafile/undotbs2.264.776961693 5 ONLINE +XIFENFEI/asm/datafile/xifenfei01.dbf.268.781967893 6 ONLINE 6 rows selected. SQL> alter database datafile 2 offline; Database altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 14 Next log sequence to archive 15 Current log sequence 15 SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> / System altered. SQL> / System altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 19 Next log sequence to archive 19 Current log sequence 20 --删除部分归档日志 [grid@rac1 ~]$ asmcmd ASMCMD> ls DATA/ XIFENFEI/ ASMCMD> cd data ASMCMD> ls XFF/ rac-cluster/ ASMCMD> cd xff ASMCMD> ls ARCHIVELOG/ CONTROLFILE/ ONLINELOG/ ASMCMD> cd archivelog ASMCMD> ls 2012_03_03/ 2012_04_13/ 2012_04_30/ 2012_05_01/ 2012_05_24/ 2012_06_12/ ASMCMD> cd 2012_06_12 ASMCMD> ls thread_1_seq_15.280.785752747 thread_1_seq_16.281.785752845 thread_1_seq_17.282.785752929 thread_1_seq_18.283.785753043 thread_1_seq_19.284.785753115 ASMCMD> rm thread_1_seq_16.281.785752845 ASMCMD> rm thread_1_seq_15.280.785752747
尝试online 数据文件
SQL> alter database datafile 2 online;
alter database datafile 2 online
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '+XIFENFEI/xff/datafile/sysaux.257.776961315'
SQL> recover datafile 2;
ORA-00279: change 1155352 generated at 06/12/2012 08:20:10 needed for thread 1
ORA-00289: suggestion :
+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747
ORA-00280: change 1155352 for thread 1 is in sequence #15
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747'
ORA-17503: ksfdopn:2 Failed to open file
+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747
ORA-15012: ASM file
'+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747' does not exist
ORA-00308: cannot open archived log
'+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747'
ORA-17503: ksfdopn:2 Failed to open file
+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747
ORA-15012: ASM file
'+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747' does not exist
准备bbed修改数据文件
现在datafile 2不能恢复,我们需要修改的就是该datafile header 相关的scn等信息,另外拷贝一个数据文件出来做修改时候参考
RMAN> copy datafile 2 to '/tmp/auxsys.dbf_rman'; Starting backup at 2012-06-12 08:59:07 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 instance=XFF1 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=+XIFENFEI/xff/datafile/sysaux.257.776961315 output file name=/tmp/auxsys.dbf_rman tag=TAG20120612T090029 RECID=1 STAMP=785754322 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:50 Finished backup at 2012-06-12 09:05:36 RMAN> copy datafile 4 to '/tmp/user.dbf_rman'; Starting backup at 2012-06-12 09:09:28 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=+XIFENFEI/xff/datafile/user_dd.dbf output file name=/tmp/user.dbf_rman tag=TAG20120612T090932 RECID=2 STAMP=785754582 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 Finished backup at 2012-06-12 09:09:48
bbed修改datafile header
[oracle@rac1 tmp]$ bbed password=blockedit listfile=/tmp/o_bbed mode=edit
BBED: Release 2.0.0.0.0 - Limited Production on Tue Jun 12 09:37:30 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /tmp/auxsys.dbf_rman 0
2 /tmp/user.dbf_rman 0
BBED> set file 2 block 1
FILE# 2
BLOCK# 1
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x0011a787
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x2ed5a9cd
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000014
ub4 kcrbabno @504 0x000000c5
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000086
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000085
BBED> set file 1 block 1
FILE# 1
BLOCK# 1
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x0011a118
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x2ed59e3a
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x0000000f
ub4 kcrbabno @504 0x0000c4ed
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000079
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000078
/*
确定需要修改项kscnbas/kcvcptim/kcvfhcpc/kcvfhccc的相关信息
*/
BBED> set count 16
COUNT 16
BBED> d file 2 block 1 offset 484
File: /tmp/user.dbf_rman (2)
Block: 1 Offsets: 484 to 499 Dba:0x00800001
------------------------------------------------------------------------
87a71100 00001000 cda9d52e 01000000
<32 bytes per line>
BBED> m /x 87a71100 file 1 block 1 offset 484
BBED-00209: invalid number (87a71100)
BBED> m /x 87a7 file 1 block 1 offset 484
File: /tmp/auxsys.dbf_rman (1)
Block: 1 Offsets: 484 to 499 Dba:0x00400001
------------------------------------------------------------------------
87a71100 00000000 3a9ed52e 01000000
<32 bytes per line>
BBED> d file 2 block 1 offset 492
File: /tmp/user.dbf_rman (2)
Block: 1 Offsets: 492 to 507 Dba:0x00800001
------------------------------------------------------------------------
cda9d52e 01000000 14000000 c5000000
<32 bytes per line>
BBED> m /x cda9d52e file 1 block 1 offset 492
BBED-00209: invalid number (cda9d52e)
BBED> d file 1 block 1 offset 492
File: /tmp/auxsys.dbf_rman (1)
Block: 1 Offsets: 492 to 507 Dba:0x00400001
------------------------------------------------------------------------
3a9ed52e 01000000 0f000000 edc40000
<32 bytes per line>
BBED> m /x cda9 file 1 block 1 offset 492
File: /tmp/auxsys.dbf_rman (1)
Block: 1 Offsets: 492 to 507 Dba:0x00400001
------------------------------------------------------------------------
cda9d52e 01000000 0f000000 edc40000
<32 bytes per line>
BBED> d file 1 block 1 offset 140
File: /tmp/auxsys.dbf_rman (1)
Block: 1 Offsets: 140 to 155 Dba:0x00400001
------------------------------------------------------------------------
79000000 2970bc2e 78000000 00000000
<32 bytes per line>
BBED> d file 2 block 1 offset 140
File: /tmp/user.dbf_rman (2)
Block: 1 Offsets: 140 to 155 Dba:0x00800001
------------------------------------------------------------------------
86000000 2970bc2e 85000000 00000000
<32 bytes per line>
BBED> m /x 86000000 file 1 block 1 offset 140
BBED-00209: invalid number (86000000)
BBED> m /x 8600 file 1 block 1 offset 140
File: /tmp/auxsys.dbf_rman (1)
Block: 1 Offsets: 140 to 155 Dba:0x00400001
------------------------------------------------------------------------
86000000 2970bc2e 78000000 00000000
<32 bytes per line>
BBED> d file 2 block 1 offset 148
File: /tmp/user.dbf_rman (2)
Block: 1 Offsets: 148 to 163 Dba:0x00800001
------------------------------------------------------------------------
85000000 00000000 00000000 00000000
<32 bytes per line>
BBED> m /x 8500 file 1 block 1 offset 148
File: /tmp/auxsys.dbf_rman (1)
Block: 1 Offsets: 148 to 163 Dba:0x00400001
------------------------------------------------------------------------
85000000 00000000 00000000 00000000
<32 bytes per line>
BBED> set file 1 block 1
FILE# 1
BLOCK# 1
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x0011a787
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x2ed5a9cd
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x0000000f
ub4 kcrbabno @504 0x0000c4ed
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x00000086
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000085
BBED> sum apply
Check value for File 1, Block 1:
current = 0x48c4, required = 0x48c4
使用修改后数据文件尝试online
SQL> alter database rename file '+XIFENFEI/xff/datafile/sysaux.257.776961315' to '/tmp/auxsys.dbf_rman'; Database altered. SQL> recover database datafile 2 ; ORA-00274: illegal recovery option DATAFILE SQL> recover database datafile 2; ORA-00274: illegal recovery option DATAFILE SQL> recover datafile 2; ORA-00283: recovery session canceled due to errors ORA-01122: database file 2 failed verification check ORA-01110: data file 2: '/tmp/auxsys.dbf_rman' ORA-01207: file is more recent than control file - old control file
尝试重建控制文件
SQL> alter database backup controlfile to trace as '/tmp/xifenfei.ctl'; Database altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP NOMOUNT ORACLE instance started. Total System Global Area 535662592 bytes Fixed Size 1346140 bytes Variable Size 411043236 bytes Database Buffers 117440512 bytes Redo Buffers 5832704 bytes SQL> @xifenfei_ctl CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS ARCHIVELOG * ERROR at line 1: ORA-01503: CREATE CONTROLFILE failed ORA-12720: operation requires database is in EXCLUSIVE mode --在rac中重建控制文件需要设置cluster_database=FALSE SQL> alter system set cluster_database=FALSE scope=spfile; System altered. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> STARTUP NOMOUNT ORACLE instance started. Total System Global Area 535662592 bytes Fixed Size 1346140 bytes Variable Size 411043236 bytes Database Buffers 117440512 bytes Redo Buffers 5832704 bytes SQL> @xifenfei_ctl Control file created.
online数据文件
重建控制文件恢复数据库之后 datafile 2自动online成功,省去了手工处理麻烦,如果没有自动online,请手工处理
SQL> recover database; Media recovery complete. SQL> alter database open; Database altered. SQL> col name for a52 SQL> select name,file#,status from v$datafile; NAME FILE# STATUS ---------------------------------------------------- ---------- ------- +XIFENFEI/xff/datafile/system.256.776961315 1 SYSTEM /tmp/auxsys.dbf_rman 2 ONLINE +XIFENFEI/xff/datafile/undotbs1.258.776961317 3 ONLINE +XIFENFEI/xff/datafile/user_dd.dbf 4 ONLINE +XIFENFEI/xff/datafile/undotbs2.264.776961693 5 ONLINE +XIFENFEI/asm/datafile/xifenfei01.dbf.268.781967893 6 ONLINE 6 rows selected.
文件系统中的datafile 2 恢复到asm中
SQL> alter database datafile 2 offline; Database altered. RMAN> copy datafile 2 to '+XIFENFEI'; Starting backup at 2012-06-12 10:55:42 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=21 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/tmp/auxsys.dbf_rman output file name=+XIFENFEI/xff/datafile/sysaux.257.785761227 tag=TAG20120612T105800 RECID=1 STAMP=785762097 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:16:24 Finished backup at 2012-06-12 11:15:05 RMAN> switch datafile 2 to copy; datafile 2 switched to datafile copy "+XIFENFEI/xff/datafile/sysaux.257.785761227" RMAN> recover datafile 2; Starting recover at 2012-06-12 11:30:32 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:01:30 Finished recover at 2012-06-12 11:34:11 RMAN> sql 'alter database datafile 2 online'; sql statement: alter database datafile 2 online
验证和收尾工作
SQL> select name,file#,status from v$datafile; NAME FILE# STATUS ---------------------------------------------------- ---------- ------- +XIFENFEI/xff/datafile/system.256.776961315 1 SYSTEM +XIFENFEI/xff/datafile/sysaux.257.785761227 2 ONLINE +XIFENFEI/xff/datafile/undotbs1.258.776961317 3 ONLINE +XIFENFEI/xff/datafile/user_dd.dbf 4 ONLINE +XIFENFEI/xff/datafile/undotbs2.264.776961693 5 ONLINE +XIFENFEI/asm/datafile/xifenfei01.dbf.268.781967893 6 ONLINE SQL> alter system set cluster_database=true scope=spfile; System altered. --然后重启节点
使用Oracle Recovery Tools一键式解决给问题
忘记执行end backup命令数据库恢复
遇到两次begin backup忘记end backup导致的悲剧.虽然不是自己亲身经历,但是感触很深,这里做了一个小实验,说明在begin backup后忘记end backup,而又丢失了备份归档日志,且数据库异常重启的事故恢复(这里为了加大实验难道,并且使用begin backup命令后的热备文件恢复)
模拟begin end
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oracle/oradata/xifenfei/archive Oldest online log sequence 37 Next log sequence to archive 39 Current log sequence 39 SQL> alter tablespace bbed begin backup; Tablespace altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oracle/oradata/xifenfei/archive Oldest online log sequence 37 Next log sequence to archive 39 Current log sequence 39 SQL> drop table chf.t_xff; Table dropped. SQL> create table chf.t_xff 2 as 3 select * from dba_objects; Table created. SQL> alter system switch logfile; System altered. SQL> delete from chf.t_XFF; 30811 rows deleted. SQL> commit; Commit complete. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oracle/oradata/xifenfei/archive Oldest online log sequence 40 Next log sequence to archive 42 Current log sequence 42
cp备份文件
[oracle@xifenfei xifenfei]$ cp bbed01.dbf bbed01.dbf_05 [oracle@xifenfei xifenfei]$ cp bbed02.dbf bbed02.dbf_05
继续操作数据库
SQL> alter system switch logfile; System altered. SQL> insert into chf.t_xff 2 select * from dba_objects; 30811 rows created. SQL> commit; Commit complete. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oracle/oradata/xifenfei/archive Oldest online log sequence 41 Next log sequence to archive 43 Current log sequence 43 SQL> alter system switch logfile; System altered.
模拟异常关闭数据库
SQL> shutdown immediate; ORA-01149: cannot shutdown - file 11 has online backup set ORA-01110: data file 11: '/u01/oracle/oradata/xifenfei/bbed01.dbf' SQL> shutdown abort; ORACLE instance shut down.
删除部分归档日志(模拟归档日志丢失)
[oracle@xifenfei archive]$ mv 1_39.dbf 1_39.dbf_bak [oracle@xifenfei archive]$ mv 1_40.dbf 1_40.dbf_bak
启动数据库
[oracle@xifenfei xifenfei]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jun 5 03:02:56 2012 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 353441008 bytes Fixed Size 451824 bytes Variable Size 184549376 bytes Database Buffers 167772160 bytes Redo Buffers 667648 bytes Database mounted. ORA-01113: file 11 needs media recovery ORA-01110: data file 11: '/u01/oracle/oradata/xifenfei/bbed01.dbf'
分析相关SCN
SQL> select file#,online_status "STATUS",to_char(change#,'9999999999999999') "SCN",
2 To_char(time,'yyyy-mm-dd hh24:mi:ss')"TIME" from v$recover_file;
FILE# STATUS SCN TIME
---------- ------- ----------------- -------------------
11 ONLINE 12286828683164 2012-06-05 02:55:43
12 ONLINE 12286828683164 2012-06-05 02:55:43
SQL> select file#,to_char(checkpoint_change#,'999999999999999') "SCN",
2 to_char(last_change#,'999999999999999')"STOP_SCN" from v$datafile;
FILE# SCN STOP_SCN
---------- ---------------- ----------------
1 12286828684636
2 12286828684636
3 12286828684636
4 12286828684636
5 12286828684636
6 12286828684636
7 12286828684636
8 12286828684636
9 12286828684636
10 12286828684636
11 12286828683164
12 12286828683164
12 rows selected.
SQL> select file#,to_char(checkpoint_change#,'9999999999999999') "SCN",
2 to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN"
3 from v$datafile_header;
FILE# SCN RESETLOGS SCN
---------- ----------------- -----------------
1 12286828684636 174968
2 12286828684636 174968
3 12286828684636 174968
4 12286828684636 174968
5 12286828684636 174968
6 12286828684636 174968
7 12286828684636 174968
8 12286828684636 174968
9 12286828684636 174968
10 12286828684636 174968
11 12286828683164 174968
12 12286828683164 174968
12 rows selected.
SQL> select file#,to_char(CHANGE#,'9999999999999999') "SCN",
2 to_char(TIME,'yyyy-mm-dd hh24:mi:ss') "TIME" from v$backup;
FILE# SCN TIME
---------- ----------------- -------------------
1 0
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 12286828683164 2012-06-05 02:55:43
12 12286828683164 2012-06-05 02:55:43
12 rows selected.
发现数据库未end backup
Tue Jun 5 02:55:43 2012 alter tablespace bbed begin backup Tue Jun 5 02:55:43 2012 Completed: alter tablespace bbed begin backup
尝试end backup
出现这个错误是正常的,因为我替换回来的bbed表空间数据文件的版本信息可能和控制文件的不一致,解决方法是重建控制文件
SQL> alter tablespace bbed end backup; alter tablespace bbed end backup * ERROR at line 1: ORA-01235: END BACKUP failed for 2 file(s) and succeeded for 0 ORA-01122: database file 12 failed verification check ORA-01110: data file 12: '/u01/oracle/oradata/xifenfei/bbed02.dbf' ORA-01208: data file is an old version - not accessing current version ORA-01122: database file 11 failed verification check ORA-01110: data file 11: '/u01/oracle/oradata/xifenfei/bbed01.dbf' ORA-01208: data file is an old version - not accessing current version
重建控制文件
SQL> shutdown abort; ORACLE instance shut down. SQL> STARTUP NOMOUNT Total System Global Area 353441008 bytes Fixed Size 451824 bytes Variable Size 184549376 bytes Database Buffers 167772160 bytes Redo Buffers 667648 bytes SQL>@ctl.sql Control file created.
尝试恢复数据库
SQL> recover database;
ORA-00279: change 12286828683164 generated at 06/05/2012 02:55:43 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/xifenfei/archive/1_39.dbf
ORA-00280: change 12286828683164 for thread 1 is in sequence #39
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u01/oracle/oradata/xifenfei/archive/1_39.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log
'/u01/oracle/oradata/xifenfei/archive/1_39.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
执行end backup
SQL> alter tablespace bbed end backup; Tablespace altered.
再次查看相关SCN
可以发现end backup之后,datafile header 的scn发生了改变,说明begin backup主要是冻住了datafile header scn
SQL> select file#,online_status "STATUS",to_char(change#,'9999999999999999') "SCN",
2 To_char(time,'yyyy-mm-dd hh24:mi:ss')"TIME" from v$recover_file;
FILE# STATUS SCN TIME
---------- ------- ----------------- -------------------
1 ONLINE 12286828684636 2012-06-05 03:00:46
2 ONLINE 12286828684636 2012-06-05 03:00:46
3 ONLINE 12286828684636 2012-06-05 03:00:46
4 ONLINE 12286828684636 2012-06-05 03:00:46
5 ONLINE 12286828684636 2012-06-05 03:00:46
6 ONLINE 12286828684636 2012-06-05 03:00:46
7 ONLINE 12286828684636 2012-06-05 03:00:46
8 ONLINE 12286828684636 2012-06-05 03:00:46
9 ONLINE 12286828684636 2012-06-05 03:00:46
10 ONLINE 12286828684636 2012-06-05 03:00:46
11 ONLINE 12286828683821 2012-06-05 02:56:26
12 ONLINE 12286828683821 2012-06-05 02:56:26
12 rows selected.
SQL> select file#,to_char(checkpoint_change#,'999999999999999') "SCN",
2 to_char(last_change#,'999999999999999')"STOP_SCN" from v$datafile;
FILE# SCN STOP_SCN
---------- ---------------- ----------------
1 12286828684636
2 12286828684636
3 12286828684636
4 12286828684636
5 12286828684636
6 12286828684636
7 12286828684636
8 12286828684636
9 12286828684636
10 12286828684636
11 12286828684636
12 12286828684636
12 rows selected.
SQL> select file#,to_char(checkpoint_change#,'9999999999999999') "SCN",
2 to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN"
3 from v$datafile_header;
FILE# SCN RESETLOGS SCN
---------- ----------------- -----------------
1 12286828684636 174968
2 12286828684636 174968
3 12286828684636 174968
4 12286828684636 174968
5 12286828684636 174968
6 12286828684636 174968
7 12286828684636 174968
8 12286828684636 174968
9 12286828684636 174968
10 12286828684636 174968
11 12286828683821 174968
12 12286828683821 174968
12 rows selected.
再次尝试恢复数据库
SQL> recover database;
ORA-00279: change 12286828683821 generated at 06/05/2012 02:56:26 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/xifenfei/archive/1_41.dbf
ORA-00280: change 12286828683821 for thread 1 is in sequence #41
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> alter database open;
Database altered.
总结说明
在数据库忘记end backup,而又被异常重启数据库时候,会提示你需要恢复.这个时候如果你有所有的归档日志,那没有任何问题,直接recover就可以了.如果因为begin backup命令执行比较久,部分归档日志丢失,这个时候不能直接recover,可以先尝试end backup,然后在recover.如果在这个时候还发现有部分日志不存在,那只能考虑bbed修改datafile header的scn.
温馨提醒:各位dba在执行begin backup之后一定要记得end backup
发表在 Oracle备份恢复
评论关闭
