分类目录归档：数据库

plsql 插入blob

发表于 2022 年 5 月 2 日由惜分飞

plsql 插入blob

SQL> create bigfile tablespace t_xifenfei datafile 'e:/oradata/orcl/t_blob.dbf' size 128M autoextend on;

表空间已创建。


SQL> create table u0425.t_blob(a int, b varchar(10), c blob) tablespace t_xifenfei ;

表已创建。


SQL> create or replace directory expdp_dir as 'e:/';

目录已创建。


SQL> declare
  2  b_file bfile;
  3  b_lob blob;
  4  begin
  5  insert into u0425.t_blob(a,b,c) values(1,'xifenfei', empty_blob()) return c into b_lob;
  6  b_file:=bfilename('EXPDP_DIR','0430.DMP');
  7  dbms_lob.open(b_file,dbms_lob.file_readonly);
  8  dbms_lob.loadfromfile(b_lob,b_file,dbms_lob.getlength(b_file));
  9  dbms_lob.close(b_file);
 10  commit;
 11  end;
 12  /

PL/SQL 过程已成功完成。

SQL> select * from u0425.t_blob where rownum<2;

         A B
---------- ----------
C
--------------------------------------------------------------------------------
         1 xifenfei
0301914E3B98006780BC00019FC70146A1A3633DBC4B089DCF3DFCD951D559000000010000100003
6907E6041E0D3037000000020200000077D10000000000FA1A880022535953222E225359535F4558


SQL> select a,b,dbms_lob.getlength(c) from u0425.t_blob where rownum<2;

         A B          DBMS_LOB.GETLENGTH(C)
---------- ---------- ---------------------
         1 xifenfei               142024704

插入的blob字段和实际文件大小，内容一致,插入是成功的

发表在 Oracle 开发 | 评论关闭

分享运气超级好的一次drop tablespace 数据恢复

发表于 2022 年 4 月 15 日由惜分飞

分享一次运气超级好的恢复,本身是一个测试库,应用厂商今天准备把应用正式上线,操作流程是:先删除用户,然后删除表空间,在创建表空间导入数据正式上线,不知何种原因最终客户在测试业务中做了一些正式数据,结果是无情的被删除了,通过alert日志找到应用厂商的一些操作记录
2021年8月份创建了业务表空间

Wed Aug 18 09:49:03 2021
create tablespace xifenfei datafile 'D:\app\Administrator\oradata\xifenfei\xifenfei.dbf' size 10g
Wed Aug 18 09:52:28 2021
Completed: create tablespace xifenfei datafile 'D:\app\Administrator\oradata\xifenfei\xifenfei.dbf' size 10g

今天删除表空间

Tue Apr 12 11:15:02 2022
drop tablespace xifenfei including contents and datafiles
WARNING: Cannot delete file D:\APP\ADMINISTRATOR\ORADATA\xifenfei\xifenfei.DBF
Errors in file d:\app\administrator\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_4296.trc:
ORA-01265: 鏃犳硶鍒犻櫎 DATA D:\APP\ADMINISTRATOR\ORADATA\xifenfei\xifenfei.DBF
ORA-27056: 鏃犳硶鍒犻櫎鏂囦欢
OSD-04024: 无法删除文件。
O/S-Error: (OS 32) 另一个程序正在使用此文件，进程无法访问。
Completed: drop tablespace xifenfei including contents and datafiles

然后客户创建新表空间提示ORA-01119,然后人工删除掉该数据文件

Tue Apr 12 11:49:02 2022
create tablespace xifenfei datafile'D:\oracle\oradata\xifenfei\xifenfei.dbf'size 20480m
ORA-1119 signalled during: create tablespace xifenfei datafile'D:\oracle\oradata\xifenfei\xifenfei.dbf'size 20480m...
Tue Apr 12 11:49:16 2022
create tablespace xifenfei datafile'D:\oracle\oradata\xifenfei\xifenfei.dbf'size 20480m
ORA-1119 signalled during: create tablespace xifenfei datafile'D:\oracle\oradata\xifenfei\xifenfei.dbf'size 20480m...

创建新表空间成功，并增加数据文件

Tue Apr 12 12:08:43 2022
create tablespace xifenfei datafile'D:\app\Administrator\oradata\xifenfei\xifenfei.dbf'size 5120m
Tue Apr 12 12:10:25 2022
Completed: create tablespace xifenfei datafile'D:\app\Administrator\oradata\xifenfei\xifenfei.dbf'size 5120m
Tue Apr 12 12:11:19 2022
alter tablespace xifenfei add datafile'D:\app\Administrator\oradata\xifenfei\xifenfei1.dbf'size 5120m
Tue Apr 12 12:13:02 2022
Completed: alter tablespace xifenfei add datafile'D:\app\Administrator\oradata\xifenfei\xifenfei1.dbf'size 5120m
alter tablespace xifenfei add datafile'D:\app\Administrator\oradata\xifenfei\xifenfei2.dbf'size 5120m
Tue Apr 12 12:14:52 2022
Completed: alter tablespace xifenfei add datafile'D:\app\Administrator\oradata\xifenfei\xifenfei2.dbf'size 5120m

基本情况就是客户删除了一个10G的业务数据文件，然后创建了3个5G的业务数据文件,现在要恢复被以前的两个表的核心数据，需要做的就是把以前的10G的数据文件找出来,但是由于删除10G文件之后又写入了15G的数据文件(而且这里面有文件的file#和删除的文件一致),理论上无法直接做block层面扫描恢复,对于此类情况,尝试文件系统层面直接反删除恢复,不过没有任何记录,文件目录被覆盖,这条路走不通.通过block扫描,发现2个file# 5文件的起始位置（分别是block 2和block 0）,而且结束位置文件大小分别是10G和5G,根据经验这两个连续的磁盘分配空间很可能就是这两个file# 5的文件

通过winhex把数据拷贝出来,使用工具检测

除损坏的block 1之外（block 0 不统计在内）,其他block都正常,也就是说这个10G的被删除的数据文件,只是丢失一个文件头,业务数据全部再,后续通过dul恢复客户需要数据,完成这次数据恢复,类似这种文件丢失,文件系统损坏,文件大小为0kb等类似恢复,参见以前类似blog:
win文件系统损坏oracle恢复
dbca删除库和rm删库恢复
文件系统重新分区oracle恢复
restore database误操作恢复
文件系统损坏导致数据文件异常恢复
Oracle 数据文件大小为0kb或者文件丢失恢复
rm -rf 删除数据文件恢复方法—文件系统反删除+oracle碎片重组

发表在非常规恢复 | 标签为 0kb dbf恢复, drop tablespace恢复, 文件丢失恢复, 文件系统损坏恢复 | 评论关闭

segment header异常对象删除处理

发表于 2022 年 4 月 14 日由惜分飞

对于某些极端情况下，segment header出现损坏的对象该如何处理,这里通过一个实验来说明这类情况该如何处理,创建表并查询相关segment信息

SQL> create tablespace t_xff datafile '/u01/app/oracle/oradata/orcl/t_xifenfei01.dbf' size 128M autoextend on;

Tablespace created.

SQL> create table t_xifenfei tablespace t_xff as select * from dba_objects;

Table created.

SQL> select header_file,header_block from dba_SEGMENTS where segment_name='T_XIFENFEI'  and owner='SYS';

HEADER_FILE HEADER_BLOCK
----------- ------------
          5          130
----segment header 为 file 5，block 130

SQL> select   EXTENT_ID , FILE_ID ,BLOCK_ID  from dba_extents where segment_name='T_XIFENFEI'  and owner='SYS';

 EXTENT_ID    FILE_ID   BLOCK_ID
---------- ---------- ----------
         0          5        128
         1          5        136
         2          5        144
         3          5        152
         4          5        160
         5          5        168
         6          5        176
         7          5        184
         8          5        192
         9          5        200
        10          5        208
        11          5        216
        12          5        224
        13          5        232
        14          5        240
        15          5        248
        16          5        256
        17          5        384
        18          5        512
        19          5        640
        20          5        768
        21          5        896
        22          5       1024
        23          5       1152
        24          5       1280

25 rows selected.

---有25个extent信息

SQL> select * from dba_free_space where file_id=5;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
T_XFF                                   5       1408  122683392      14976
           5
--空闲block是从1408开始

模拟segment header损坏(通过dd破坏block)

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> 
SQL> 
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@xifenfei ~]$ 
[oracle@xifenfei ~]$ 
[oracle@xifenfei ~]$echo xifenfei.com|dd of=/u01/app/oracle/oradata/orcl/t_xifenfei01.dbf bs=8192 conv=notrunc seek=130
0+1 records in
0+1 records out
17 bytes (17 B) copied, 5.4389e-05 s, 313 kB/s
[oracle@xifenfei ~]$ dbv file=/u01/app/oracle/oradata/orcl/t_xifenfei01.dbf

DBVERIFY: Release 11.2.0.4.0 - Production on Wed Apr 13 20:29:41 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/t_xifenfei01.dbf
Page 130 is marked corrupt
Corrupt block relative dba: 0x01400082 (file 5, block 130)
Bad header found during dbv: 
Data in bad block:
 type: 119 format: 7 rdba: 0x65666978
 last change scn: 0x632e.6965666e seq: 0x6f flg: 0x6d
 spare1: 0x77 spare2: 0x2e spare3: 0x0
 consistency value in tail: 0xada72301
 check value in block header: 0xf30a
 computed block checksum: 0x5eb9



DBVERIFY - Verification complete

Total Pages Examined         : 16384
Total Pages Processed (Data) : 1234
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 154
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 14995
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 6466979 (0.6466979)

查询表数据报错

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 13 20:29:48 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  734892032 bytes
Fixed Size                  2256872 bytes
Variable Size             452984856 bytes
Database Buffers          276824064 bytes
Redo Buffers                2826240 bytes
Database mounted.
Database opened.
SQL> select count(1) from t_xifenfei;
select count(1) from t_xifenfei
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 130)
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/t_xifenfei01.dbf'

查询extent信息为空

SQL> select   EXTENT_ID   , FILE_ID   ,BLOCK_ID  from dba_extents where segment_name='T_XIFENFEI'  and owner='SYS';

no rows selected

SQL> select header_file,header_block from dba_SEGMENTS where segment_name='T_XIFENFEI'  and owner='SYS';

HEADER_FILE HEADER_BLOCK
----------- ------------
          5          130

SQL> select * from dba_free_space where file_id=5;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
T_XFF                                   5       1408  122683392      14976
           5

尝试删除表报错

SQL> drop table t_xifenfei;
drop table t_xifenfei
           *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 130)
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/t_xifenfei01.dbf'

通过加purge删除成功

SQL> drop table t_xifenfei purge;

Table dropped.

删除表成功，但是磁盘空间未释放，通过查询确认变为一个临时段

SQL> select * from dba_free_space where file_id=5;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
T_XFF                                   5       1408  122683392      14976
           5


SQL> select segment_name,segment_type,owner from dba_segments where header_file=5;

SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE       OWNER
------------------ ------------------------------
5.130
TEMPORARY          SYS

清理临时段,彻底删除segment header异常对象删除后遗症

SQL> exec dbms_space_admin.segment_corrupt('T_XFF',5,130); 

PL/SQL procedure successfully completed.

SQL> exec dbms_space_admin.segment_drop_corrupt('T_XFF',5,130);

PL/SQL procedure successfully completed.

SQL> exec DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS ('T_XFF');

PL/SQL procedure successfully completed.

SQL> select segment_name,segment_type,owner from dba_segments where header_file=5;

no rows selected

SQL> select * from dba_free_space where file_id=5;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
T_XFF                                   5        128  133169152      16256
           5

发表在 ORA-xxxxx | 标签为 segment header ORA-1578, 段头坏块 | 评论关闭

17813235971

QQ 咨询

分类目录归档：数据库

plsql 插入blob

分享运气超级好的一次drop tablespace 数据恢复

segment header异常对象删除处理

文章分类

最近发表

分类目录归档：数据库

plsql 插入blob

分享运气超级好的一次drop tablespace 数据恢复

segment header异常对象删除处理

标签云

文章分类

最近发表