Category Archives: Oracle备份恢复

删除分区 oracle asm disk 恢复

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：删除分区 oracle asm disk 恢复

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

接到一个朋友数据库故障请求case.大概操作是这样的:有一个39T的lun,通过parted分了15个分区,给oracle asm使用创建磁盘组data4,然后分了4个分区做成data5(由于ausize写错误了),删除掉磁盘组和这四个分区.然后重新分配了6个分区,并且使用最后5个分区创建了data5磁盘组.使用了一段时间之后,由于oracle空间不足,检查的时候误以为这个lun就前面15个分区使用,人工把后面的6个分区给删除了,并且创建了4个新分区,然后发现数据库crash了,发现误删除了在使用的分区.然后又把新创建的4个分区给删除了.接手该故障的时候,这个39T lun的分区信息如下

[root@node1 linux64]# parted /dev/mapper/36000d31003d39e000000000000000004
GNU Parted 2.1
Using /dev/mapper/36000d31003d39e000000000000000004
Welcome to GNU Parted! Type 'help' to view a list of commands.
(parted) print                                                            
Model: Linux device-mapper (multipath) (dm)
Disk /dev/mapper/36000d31003d39e000000000000000004: 39.6TB
Sector size (logical/physical): 512B/4096B
Partition Table: gpt

Number  Start   End     Size    File system  Name         Flags
 1      2097kB  2000GB  2000GB               asmdata4-1
 2      2000GB  4000GB  2000GB               asmdata4-2
 3      4000GB  6000GB  2000GB               asmdata4-3
 4      6000GB  8000GB  2000GB               asmdata4-4
 5      8000GB  10.0TB  2000GB               asmdata4-5
 6      10.0TB  12.0TB  2000GB               asmdata4-6
 7      12.0TB  14.0TB  2000GB               asmdata4-7
 8      14.0TB  16.0TB  2000GB               asmdata4-8
 9      16.0TB  18.0TB  2000GB               asmdata4-9
10      18.0TB  20.0TB  2000GB               asmdata4-10
11      20.0TB  22.0TB  2000GB               asmdata4-11
12      22.0TB  24.0TB  2000GB               asmdata4-12
13      24.0TB  26.0TB  2000GB               asmdata4-13
14      26.0TB  28.0TB  2000GB               asmdata4-14
15      28.0TB  30.0TB  2000GB               asmdata4-15

客户正常使用情况下,这个lun上面相关分区的asm disk信息

 SQL> CREATE DISKGROUP DATA4 EXTERNAL REDUNDANCY  DISK 
'/dev/mapper/36000d31003d39e000000000000000004p1' SIZE 1907346M ,
 '/dev/mapper/36000d31003d39e000000000000000004p2' SIZE 1907350M ,
 '/dev/mapper/36000d31003d39e000000000000000004p3' SIZE 1907348M ,
 '/dev/mapper/36000d31003d39e000000000000000004p4' SIZE 1907348M ,
 '/dev/mapper/36000d31003d39e000000000000000004p5' SIZE 1907350M  
ATTRIBUTE 'compatible.asm'='11.2.0.0.0','au_size'='8M' /* ASMCA */ 

SQL> ALTER DISKGROUP DATA4 ADD  DISK 
'/dev/mapper/36000d31003d39e000000000000000004p10' SIZE 1907348M ,
'/dev/mapper/36000d31003d39e000000000000000004p6' SIZE 1907348M ,
'/dev/mapper/36000d31003d39e000000000000000004p7' SIZE 1907348M ,
'/dev/mapper/36000d31003d39e000000000000000004p8' SIZE 1907350M ,
'/dev/mapper/36000d31003d39e000000000000000004p9' SIZE 1907348M /* ASMCA */ 

SQL> ALTER DISKGROUP DATA4 ADD  DISK 
'/dev/mapper/36000d31003d39e000000000000000004p11' SIZE 1907348M ,
'/dev/mapper/36000d31003d39e000000000000000004p12' SIZE 1907350M ,
'/dev/mapper/36000d31003d39e000000000000000004p13' SIZE 1907348M ,
'/dev/mapper/36000d31003d39e000000000000000004p14' SIZE 1907348M ,
'/dev/mapper/36000d31003d39e000000000000000004p15' SIZE 1907350M /* ASMCA */ 

SQL> CREATE DISKGROUP DATA5 EXTERNAL REDUNDANCY  DISK 
'/dev/mapper/36000d31003d39e000000000000000004p17' SIZE 1716614M ,
'/dev/mapper/36000d31003d39e000000000000000004p18' SIZE 1716614M ,
'/dev/mapper/36000d31003d39e000000000000000004p19' SIZE 1716614M ,
'/dev/mapper/36000d31003d39e000000000000000004p20' SIZE 1716614M ,
'/dev/mapper/36000d31003d39e000000000000000004p21' SIZE 1621246M  
ATTRIBUTE 'compatible.asm'='11.2.0.0.0','au_size'='4M' /* ASMCA */

基于客户现在的情况,data4中的所有分区都正常,主要是要找出来data5中的5个分区的数据.因为客户不确定p16分区大小,导致后续的5个分区起始位置不好定位.从而使得恢复无法进行.通过shell脚本结合kfed尝试定位asm disk header信息

#!/bin/bash
j=xxxxxxxxxxx
for ((i=xxxxxx; i<=j; i++))
do
 echo "-----$i--------" >> /home/get_au1.txt
 kfed read /dev/mapper/36000d31003d39e000000000000000004 aun=$i |
 > grep  "kfdhdb.dskname:              DATA" >> /home/get_au.txt
done

结果发现无法获取到结果,通过分析发现这里由于lun过大，导致aun值过大,从而使得kfed溢出无法读取到正常值.根据parted的特性,人工dd部分block进行分析

[root@node1 bak]# kfed read xifenfei.dd aun=134|more
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:              2147483648 ; 0x008: disk=0
kfbh.check:                  3357988283 ; 0x00c: 0xc826d5bb
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:         ORCLDISK ; 0x000: length=8
kfdhdb.driver.reserved[0]:            0 ; 0x008: 0x00000000
kfdhdb.driver.reserved[1]:            0 ; 0x00c: 0x00000000
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                186646528 ; 0x020: 0x0b200000
kfdhdb.dsknum:                        0 ; 0x024: 0x0000
kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:              DATA5_0000 ; 0x028: length=10
kfdhdb.grpname:                   DATA5 ; 0x048: length=5
kfdhdb.fgname:               DATA5_0000 ; 0x068: length=10
kfdhdb.capname:                         ; 0x088: length=0
kfdhdb.crestmp.hi:             33116450 ; 0x0a8: HOUR=0x2 DAYS=0x9 MNTH=0x4 YEAR=0x7e5
kfdhdb.crestmp.lo:            477378560 ; 0x0ac: USEC=0x0 MSEC=0x10e SECS=0x7 MINS=0x7
kfdhdb.mntstmp.hi:             33116450 ; 0x0b0: HOUR=0x2 DAYS=0x9 MNTH=0x4 YEAR=0x7e5
kfdhdb.mntstmp.lo:            486256640 ; 0x0b4: USEC=0x0 MSEC=0x2ec SECS=0xf MINS=0x7
kfdhdb.secsize:                     512 ; 0x0b8: 0x0200
kfdhdb.blksize:                    4096 ; 0x0ba: 0x1000
kfdhdb.ausize:                  4194304 ; 0x0bc: 0x00400000
kfdhdb.mfact:                    454272 ; 0x0c0: 0x0006ee80
kfdhdb.dsksize:                  429153 ; 0x0c4: 0x00068c61
kfdhdb.pmcnt:                         2 ; 0x0c8: 0x00000002

顺利找到了data5中的第一块磁盘,而且确定了起始位置,然后构造相关的dd语句把分区的数据dd到一个新磁盘中

dd if=/dev/mapper/36000d31003d39e000000000000000004 bs=4M skip=xxxxx count=429153 of=/dev/sdu

然后通过kfed查看数据
20210704160347

通过类似方法依次处理,最终把5块asm disk全部找到,并且顺利dd到新的磁盘中.尝试启动crs,并mount data5
20210704153634
20210704153548
data5 磁盘组mount成功之后,数据库顺利启动,实现lun中删除分区之后,asm磁盘组数据完美恢复
20210704153728
这次运气还不错,仅仅是对lun的分区使用了parted进行了删除和创建等操作,没有格式化文件系统和做成新的asm disk,不然数据会有一部分丢失.对于有部分破坏的分区,需要通过底层碎片的方法进行最大限度抢救数据.参考类似文档:
asm disk被加入vg恢复
又一例asm格式化文件系统恢复
文件系统损坏导致数据文件异常恢复
一次完美的asm disk被格式化ntfs恢复
Oracle 数据文件大小为0kb或者文件丢失恢复
再一起asm disk被格式化成ext3文件系统故障恢复
oracle asm disk格式化恢复—格式化为ext4文件系统
oracle asm disk格式化恢复—格式化为ntfs文件系统
分享oracleasm createdisk重新创建asm disk后数据0丢失恢复案例

expdp 并行导出单表数据

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：expdp 并行导出单表数据

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在某些情况下,需要使用并行的方法使用 datapump 对单个对象并行导出,导入加快数据迁移的数据
expdp导出操作

#!/bin/bash
chunk=10
for ((i=0;i<chunk;i++));
do
  expdp USERNAME/Password@DB_NAME TABLES=LOB_TEST QUERY=LOB_TEST:\"where mod\(dbms_rowid.rowid_block_number\(rowid\)\, 
>${chunk}\) = ${i}\" directory=DMP dumpfile=lob_test_${i}.dmp logfile= log_test_${i}.log &
  echo $i
done

impdp导入操作

#!/bin/bash
chunk=10
for ((i=0;i<chunk;i++));
do
 impdp USERNAME/Password@DB_NAME  directory=DMP REMAP_TABLE=LOB_TEST:LOB_TEST  remap_schema=source:target 
>dumpfile= lob_test_${i}.dmp logfile=TABLE_imp_log_test_${i}.log  DATA_OPTIONS=DISABLE_APPEND_HINT  CONTENT=DATA_ONLY &
 echo $i
done

在12c版本开始impdp可能会启用ENABLE_PARALLEL_DML特性,需要注意
参考:Optimising LOB Export and Import Performance via Oracle DataPump

datapump network_link遭遇ORA-12899错误

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：datapump network_link遭遇ORA-12899错误

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在给一个客户使用expdp+network_link导出数据,然后通过impdp导入数据的过程中遇到ORA-12899问题.
20210608215836

对原库和现在库进行分析
20210608215825
20210608215817
原库和目标库表结构一致,原库该表存储数据实际长度确实为1,但是在impdp导入的时候提示需要长度为3.通过分析,确认原库的nls_length_semantics参数设置为char了,直接使用impdp+network_link不落地方式导入该表数据成功
20210608215845
根据上述情况,查询相关文档,确认类似记录为:
ORA-12899 When Using IMPDP Over Network Link (Doc ID 414901.1)
ORA-26059 During Impdp Using Export Dump Taken With NETWORK_LINK Option (Doc ID 2266956.1)
虽然都不是完全匹配该问题,但是基本上可以确认expdp的network_link和nls_length_semantics参数是引起该问题的根本原因,在后续的迁移中,尽量保持nls_length_semantics参数一致.

tab$异常被处理之后报ORA-600 13304故障处理

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：tab$异常被处理之后报ORA-600 13304故障处理

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

又一例数据库启动报ORA-600 16703 1403 20错误故障

Sun Jun 13 14:00:56 2021
NOTE: dependency between database xff and diskgroup resource ora.DG_ARCH_xff.dg is established
Errors in file /opt/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_56340.trc  (incident=348265):
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Incident details in: /opt/oracle/diag/rdbms/xff/xff1/incident/incdir_348265/xff1_ora_56340_i348265.trc
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /opt/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_56340.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Errors in file /opt/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_56340.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Error 704 happened during db open, shutting down database
USER (ospid: 56340): terminating the instance due to error 704
Instance terminated by USER, pid = 56340

这个故障比较明显,根据我们之前的分析经验(警告：互联网中有oracle介质被注入恶意程序导致—ORA-600 16703),应该是tab$被恶意破坏导致,通过分析安装程序,确认是该问题,客户通过互联网上的相关文章,dd方式进行处理,结果数据库报ORA-600 13304错误,无法继续,让我们提供技术支持

SMON: enabling tx recovery
Database Characterset is AL32UTF8
Errors in file /opt/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_83843.trc  (incident=396265):
ORA-00600: internal error code, arguments: [13304], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /opt/oracle/diag/rdbms/xff/xff1/incident/incdir_396265/xff1_ora_83843_i396265.trc
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /opt/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_83843.trc:
ORA-00600: internal error code, arguments: [13304], [], [], [], [], [], [], [], [], [], [], []
Errors in file /opt/oracle/diag/rdbms/xff/xff1/trace/xff1_ora_83843.trc:
ORA-00600: internal error code, arguments: [13304], [], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 83843): terminating the instance due to error 600
Instance terminated by USER, pid = 83843

通过我们的技术对数据库进行一系列恢复之后,open过程报错

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "NAME": invalid identifier
Process ID: 23346
Session ID: 680 Serial number: 51933

通过跟踪启动过程分析

PARSE ERROR #140574232044112:len=45 dep=1 uid=0 oct=3 lid=0 tim=1623621695884944 err=904
select value$ from sys.props$ where name = :1
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "NAME": invalid identifier
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "NAME": invalid identifier

基本上可以确定是由于客户自行恢复导致props$表异常.通过进一步分析,确认是由于在对tab$处理不合适导致,进一步对tab$进行处理,数据库恢复正常,实现数据0丢失

磁盘空间不足迁移数据文件导致故障恢复

Posted on by

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：磁盘空间不足迁移数据文件导致故障恢复

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有客户由于磁盘空间不足，在线把oracle数据迁移到其他位置

Tue Jun 01 11:44:32 2021
Thread 1 advanced to log sequence 28754 (LGWR switch)
  Current log# 2 seq# 28754 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log
Tue Jun 01 11:59:54 2021
Non critical error ORA-48113 caught while writing to trace file
      "/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mmon_23341.trc"
Error message: 
Writing to the above trace file is disabled for now on...
Tue Jun 01 12:00:00 2021
Non critical error ORA-48181 caught while writing to trace file
       "/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_29692.trc"
Error message: Linux-x86_64 Error: 28: No space left on device
Additional information: 1
Writing to the above trace file is disabled for now on...
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_29692.trc:
ORA-12012: error on auto execute of job "XIFENFEI"."STATISTICS_1_JOBS"
ORA-06575: Package or function PKG_STAT_1_2018 is in an invalid state
Tue Jun 01 12:12:26 2021

迁移走数据文件之后,数据库报错，并且强制关闭数据库

ORA-01116: error in opening database file 30
ORA-01110: data file 30: '/u02/orcdate/AAAA.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m001_29106.trc:
ORA-01116: error in opening database file 31
ORA-01110: data file 31: '/u02/orcdate/CBD.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Mon Jun 07 10:25:03 2021
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_9817.trc:
ORA-01116: error in opening database file 24
ORA-01110: data file 24: '/u02/orcdate/ABC.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Mon Jun 07 10:25:10 2021
Shutting down instance (immediate)
Stopping background process SMCO
Shutting down instance: further logons disabled
Read of datafile '/u02/orcdate/XXXXXXX.dbf' (fno 21) header failed with ORA-01208
Rereading datafile 21 header failed with ORA-01208
Mon Jun 07 10:25:36 2021
Adjusting the default value of parameter parallel_max_servers
from 640 to 485 due to the value of parameter processes (500)
Starting ORACLE instance (normal)
Mon Jun 07 10:28:20 2021
Shutting down instance (abort)
License high water mark = 152
USER (ospid: 7987): terminating the instance
Termination issued to instance processes. Waiting for the processes to exit
Mon Jun 07 10:28:30 2021
Instance termination failed to kill one or more processes
Instance terminated by USER, pid = 7987
Mon Jun 07 10:28:31 2021
Instance shutdown complete

然后又把文件迁移回来,并且进行了一系列数据库恢复,最后我们接手是情况是有多个文件被offline,并且有一个文件报WRONG FILE NUMBER,通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)脚本检查,对其中的v$datafile,v$datafile_header,v$tablespace综合分析
20210612154127
20210612154301
20210612154350

确认是WXD_YPT表空间数据文件直接拷贝为WXD表空间数据文件,经过客户确认,WXD数据不重要,客户先忽略.
通过一系列处理，尝试open数据库,报ORA-600 2662错误

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [2662], [3786], [2612118101], [3786], [2612128448], [12583040]
ORA-00600: internal error code, arguments: [2662], [3786], [2612118100], [3786], [2612128448], [12583040]
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [3786], [2612118098], [3786], [2612128448], [12583040]
Process ID: 14888
Session ID: 198 Serial number: 3

修改数据库scn(参考blog相关link:ORA-600 2662)数据库顺利open,并且协助客户导出数据并导入新库,完成数据库恢复.
这次运气比较好,只是丢失了一点数据,没有引起重大事故.再此提醒：不太了解oracle的朋友,操作数据库需谨慎,不要在线直接移动数据文件,另外为了更好的恢复效果,更快的恢复,故障之后,最好尽可能的告知所有操作.