分类目录归档：Oracle

dul 12.2完美支持Oracle 19c恢复

发表于 2020 年 11 月 11 日 由 惜分飞

Oracle dul 12.2完美支持12c+版本(实测19c恢复正常)

[root@ora11g dul]# ./dul

Data UnLoader: 12.2.0.0.0 Beta - Internal Only - on Wed Nov 11 00:00:39 2020
with 64-bit io functions and the decompression option

Copyright (c) 1994 2019 Bernard van Duijnen All rights reserved.

 Strictly Oracle Internal Use Only


Found db_id = 2193655303
Found db_name = UORA19C
DUL> bootstrap;
Probing file = 1, block = 520
. unloading table                BOOTSTRAP$
DUL: Warning: block number is non zero but marked deferred trying to process it anyhow
      60 rows unloaded
Reading BOOTSTRAP.dat 60 entries loaded
Parsing Bootstrap$ contents
DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 12
 OBJ$: segobjno 18, file 1 block 240
 TAB$: segobjno 2, tabno 1, file 1  block 144
 COL$: segobjno 2, tabno 5, file 1  block 144
 USER$: segobjno 10, tabno 1, file 1  block 208
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$   72485 rows unloaded
. unloading table                      TAB$    2243 rows unloaded
. unloading table                      COL$  123798 rows unloaded
. unloading table                     USER$     127 rows unloaded
Reading USER.dat 127 entries loaded
Reading OBJ.dat 72485 entries loaded and sorted 72485 entries
Reading TAB.dat 2243 entries loaded
Reading COL.dat 123798 entries loaded and sorted 123798 entries
Reading BOOTSTRAP.dat 60 entries loaded

DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 12
 OBJ$: segobjno 18, file 1 block 240
 TAB$: segobjno 2, tabno 1, file 1  block 144
 COL$: segobjno 2, tabno 5, file 1  block 144
 USER$: segobjno 10, tabno 1, file 1  block 208
 TABPART$: segobjno 814, file 1 block 5424
 INDPART$: segobjno 819, file 1 block 5464
 TABCOMPART$: segobjno 836, file 1 block 5600
 INDCOMPART$: segobjno 841, file 1 block 5640
 TABSUBPART$: segobjno 826, file 1 block 5520
 INDSUBPART$: segobjno 831, file 1 block 5560
 IND$: segobjno 2, tabno 3, file 1  block 144
 ICOL$: segobjno 2, tabno 4, file 1  block 144
 LOB$: segobjno 2, tabno 6, file 1  block 144
 COLTYPE$: segobjno 2, tabno 7, file 1  block 144
 TYPE$: segobjno 740, tabno 1, file 1  block 4880
 COLLECTION$: segobjno 740, tabno 2, file 1  block 4880
 ATTRIBUTE$: segobjno 740, tabno 3, file 1  block 4880
 LOBFRAG$: segobjno 847, file 1 block 5688
 LOBCOMPPART$: segobjno 850, file 1 block 5720
 UNDO$: segobjno 15, file 1 block 224
 TS$: segobjno 6, tabno 2, file 1  block 176
 PROPS$: segobjno 127, file 1 block 1320
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$
DUL: Warning: Recreating file "OBJ.ctl"
   72485 rows unloaded
. unloading table                      TAB$
DUL: Warning: Recreating file "TAB.ctl"
    2243 rows unloaded
. unloading table                      COL$
DUL: Warning: Recreating file "COL.ctl"
  123798 rows unloaded
. unloading table                     USER$
DUL: Warning: Recreating file "USER.ctl"
     127 rows unloaded
. unloading table                  TABPART$     304 rows unloaded
. unloading table                  INDPART$     204 rows unloaded
. unloading table               TABCOMPART$       1 row  unloaded
. unloading table               INDCOMPART$       0 rows unloaded
. unloading table               TABSUBPART$      32 rows unloaded
. unloading table               INDSUBPART$       0 rows unloaded
. unloading table                      IND$    2922 rows unloaded
. unloading table                     ICOL$    4996 rows unloaded
. unloading table                      LOB$     702 rows unloaded
. unloading table                  COLTYPE$    3035 rows unloaded
. unloading table                     TYPE$    5889 rows unloaded
. unloading table               COLLECTION$    1385 rows unloaded
. unloading table                ATTRIBUTE$   15376 rows unloaded
. unloading table                  LOBFRAG$      15 rows unloaded
. unloading table              LOBCOMPPART$       0 rows unloaded
. unloading table                     UNDO$      21 rows unloaded
. unloading table                       TS$      21 rows unloaded
. unloading table                    PROPS$      42 rows unloaded
Reading USER.dat 127 entries loaded
Reading OBJ.dat 72485 entries loaded and sorted 72485 entries
Reading TAB.dat 2243 entries loaded
Reading COL.dat 123798 entries loaded and sorted 123798 entries
Reading TABPART.dat 304 entries loaded and sorted 304 entries
Reading TABCOMPART.dat 1 entries loaded and sorted 1 entries
Reading TABSUBPART.dat 32 entries loaded and sorted 32 entries
Reading INDPART.dat 204 entries loaded and sorted 204 entries
Reading INDCOMPART.dat 0 entries loaded and sorted 0 entries
Reading INDSUBPART.dat 0 entries loaded and sorted 0 entries
Reading IND.dat 2922 entries loaded
Reading LOB.dat 702 entries loaded
Reading ICOL.dat 4996 entries loaded
Reading COLTYPE.dat 3035 entries loaded
Reading TYPE.dat
DUL: Notice: Increased the size of DC_TYPES from 4096 to 32768 entries
 5889 entries loaded
Reading ATTRIBUTE.dat 15376 entries loaded
Reading COLLECTION.dat
DUL: Notice: Increased the size of DC_COLLECTIONS from 1024 to 8192 entries
 1385 entries loaded
Reading BOOTSTRAP.dat 60 entries loaded
Reading LOBFRAG.dat 15 entries loaded and sorted 15 entries
Reading LOBCOMPPART.dat 0 entries loaded and sorted 0 entries
Reading UNDO.dat 21 entries loaded
Reading TS.dat 21 entries loaded
Reading PROPS.dat 42 entries loaded
Database character set is AL32UTF8
Database national character set is AL16UTF16

实际测试一张表恢复

DUL> unload table sys.props$;
. unloading table                    PROPS$
DUL: Warning: Recreating file "SYS_PROPS.ctl"
      42 rows unloaded

20201111224237

发表在 非常规恢复 | 标签为 , , , | 评论关闭

因尝试恢复未破坏数据库实现数据0丢失恢复

发表于 2020 年 11 月 10 日 由 惜分飞

早上接手一个被人折腾了一个晚上的库,通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)收集信息发现有8个数据文件WRONG FILE TYPE
20201110220859

分析最初日志发现客户做了多次offline数据文件和resetlogs操作,运气不错resetlogs操作都没有成功,现场没有被进一步破坏
20201110221430
20201110221400
进一步检查发现客户有11月9日的备份以及全部的归档,直接执行

run{
set newname for datafile  31 to '+ARCH';
set newname for datafile  19 to '+ARCH';
set newname for datafile  20 to '+ARCH';
set newname for datafile  35 to '+ARCH';
set newname for datafile  52 to '+ARCH';
set newname for datafile  16 to '+ARCH';
set newname for datafile  30 to '+ARCH';
set newname for datafile  28 to '+ARCH';
restore datafile 31,19,20,35,52,16,30,28;
switch datafile all;
}

然后catalog注册日志,继续恢复,实现数据0丢失,数据库打开
20201110222050

庆幸夜间的所有误操作没有产生任何实质性破坏,不然就算有备份,后果也是比较麻烦的(客户的设备io特别慢,还原8个数据文件使用了近7个小时,全库还原只能呵呵……)

发表在 Oracle备份恢复 | 标签为 | 评论关闭

因为人工误操作导致resetlogs scn不一致恢复

发表于 2020 年 11 月 8 日 由 惜分飞

有个朋友,数据库损坏有一段时间,前期找过我们,对其进行分析,判断强制打开库应该就可以了;后来不知道什么原因有几批人进行了恢复,把现场完全破坏,检查数据库发现,数据库文件有部分resetlogs scn不一致
1

通过工具(Oracle恢复小工具—Oracle Recovery Tools)对文件相关值进行修改,并尝试打开库
20201108133342
SQL> recover database until cancel;
ORA-00279: change 15764982275935 generated at 11/06/2020 00:27:23 needed for thread 1
ORA-00289: suggestion : /jiaoh/oradata/orcl/archlog/arch_1_5_995131008.log
ORA-00280: change 15764982275935 for thread 1 is in sequence #5

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 3 needs more recovery to be consistent
ORA-01110: data file 3: '/u01/oracle/oradata/orcl/undotbs01.dbf'


ORA-01112: media recovery not started


SQL> alter database open resetlogs;

Database altered.

数据库alert日志报ORA-600 4097错误

un Nov 08 12:17:06 2020
Errors in file /u01/oracle/diag/rdbms/orcl/orcl/trace/orcl_j006_135739.trc  (incident=2604819):
ORA-00600: internal error code, arguments: [4097], [31], [2], [338312], [], [], [], [], [], [], [], []
Incident details in: /u01/oracle/diag/rdbms/orcl/orcl/incident/incdir_2604819/orcl_j006_135739_i2604819.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

对undo进行处理,数据库后台无明显报错,建议客户逻辑导出数据导入新库,恢复完成.

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