精华内容
下载资源
问答
  • 关于如何在Oracle 12c 数据库中,使用RMAN恢复被DROP / TRUNCATE / DELETE的表的方法 可用版本: Oracle Database - Enterprise Edition - Version 12.1.0.1及之后版本目的: 说明12c关于从RMAN备份中恢复表的新特性...

    关于如何在Oracle 12c 数据库中,使用RMAN恢复被DROP / TRUNCATE / DELETE的表的方法


    可用版本:
    Oracle Database - Enterprise Edition - Version 12.1.0.1及之后版本
    目的:
    说明12c关于从RMAN备份中恢复表的新特性 
    示例: 

    RMAN> recover table pd.t1 OF PLUGGABLE DATABASE PDB1 until time "to_date('11/23/2016 14:58:30','mm/dd/yyyy hh24:mi:ss')" auxiliary destination '/u04/' remap table pd.t1:t12 preview;

    如果你不希望生产库受到任何影响,可以使用以下步骤来进行PITR 时间点恢复来对drop/truncate的表进行恢复。


    解决方案:

    • 首先,我们需要,我们需要确定进行手工时间点恢复的所需备份,将其拷贝至目标服务器。在测试案例中,我们将备份拷贝并存放在/u03/backup/下。
    • 在目标服务器

            -- 设置ORACLE_SID并将数据库打开至nomount状态
            -- 从备份中抽取spfile并新建pfile

    $export ORACLE_SID=ora12mt
    
    $ rman target /
    
    Recovery Manager: Release 12.1.0.2.0 - Production on Thu Dec 15 09:36:25 2016
    
    Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
    
    connected to target database (not started)
    
    RMAN> startup nomount force;
    
    startup failed: ORA-01078: failure in processing system parameters
    LRM-00109: could not open parameter file '/u03/app/oracle/product/database/12.1.0.2/dbs/initora12mt.ora'
    
    starting Oracle instance without parameter file for retrieval of spfile
    Oracle instance started
    
    Total System Global Area 1073741824 bytes
    
    Fixed Size 2932632 bytes
    Variable Size 293601384 bytes
    Database Buffers 771751936 bytes
    Redo Buffers 5455872 bytes
    
    RMAN> restore spfile from '/u03/backup/o1_mf_s_928681105_d3br9ton_.bkp';
    
    Starting restore at 15-DEC-16
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=6 device type=DISK
    
    channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u03/backup/o1_mf_s_928681105_d3br9ton_.bkp
    channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
    Finished restore at 15-DEC-16
    
    RMAN>shutdown immediate;
    
    
    
    << 通过恢复的spfile新建pfile
    
    SQL> create pfile from spfile;
    
    File created.
    
    SQL>
    • 修改pfile参数如在辅助库上的路径/内存使用等参数,并将辅助库起到nomount状态
    $ sqlplus "/as sysdba"
    
    SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 09:52:56 2016
    
    Copyright (c) 1982, 2014, Oracle. All rights reserved.
    
    Connected to an idle instance.
    
    SQL> startup nomount pfile='initora12mt.ora';
    ORACLE instance started.
    
    Total System Global Area 629145600 bytes
    Fixed Size 2927528 bytes
    Variable Size 310379608 bytes
    Database Buffers 310378496 bytes
    Redo Buffers 5459968 bytes
    SQL> exit
    Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    • 恢复控制文件并mount数据库
    $ rman target /
    
    Recovery Manager: Release 12.1.0.2.0 - Production on Thu Dec 15 09:53:40 2016
    
    Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
    
    connected to target database: ORA12MT (not mounted)
    
    RMAN> restore controlfile from '/u03/backup/o1_mf_s_928683729_d3btvsvn_.bkp';
    
    Starting restore at 15-DEC-16
    using channel ORA_DISK_1
    
    channel ORA_DISK_1: restoring control file
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
    output file name=/u03/database/oradata/ora12mt/control01.dbf
    Finished restore at 15-DEC-16
    
    RMAN> alter database mount;
    
    Statement processed
    released channel: ORA_DISK_1
    • Catalog备份片, 执行crosscheck并删除过期备份
    RMAN> catalog start with '/u03/backup/' noprompt;
    
    Starting implicit crosscheck backup at 15-DEC-16
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=242 device type=DISK
    Crosschecked 6 objects
    Finished implicit crosscheck backup at 15-DEC-16
    
    Starting implicit crosscheck copy at 15-DEC-16
    using channel ORA_DISK_1
    Finished implicit crosscheck copy at 15-DEC-16
    
    searching for all files in the recovery area
    cataloging files...
    no files cataloged
    
    searching for all files that match the pattern /u03/backup/
    
    List of Files Unknown to the Database
    =====================================
    File Name: /u03/backup/o1_mf_annnn_TAG20161123T145824_d3br9rpz_.bkp
    File Name: /u03/backup/o1_mf_nnndf_TAG20161123T145518_d3br6n46_.bkp
    File Name: /u03/backup/o1_mf_nnndf_TAG20161123T145518_d3br3yyn_.bkp
    File Name: /u03/backup/o1_mf_s_928683729_d3btvsvn_.bkp
    File Name: /u03/backup/o1_mf_nnndf_TAG20161123T145518_d3br8c6d_.bkp
    File Name: /u03/backup/o1_mf_s_928681105_d3br9ton_.bkp
    File Name: /u03/backup/o1_mf_annnn_TAG20161123T145514_d3br3v24_.bkp
    cataloging files...
    cataloging done
    
    List of Cataloged Files
    =======================
    File Name: /u03/backup/o1_mf_annnn_TAG20161123T145824_d3br9rpz_.bkp
    File Name: /u03/backup/o1_mf_nnndf_TAG20161123T145518_d3br6n46_.bkp
    File Name: /u03/backup/o1_mf_nnndf_TAG20161123T145518_d3br3yyn_.bkp
    File Name: /u03/backup/o1_mf_s_928683729_d3btvsvn_.bkp
    File Name: /u03/backup/o1_mf_nnndf_TAG20161123T145518_d3br8c6d_.bkp
    File Name: /u03/backup/o1_mf_s_928681105_d3br9ton_.bkp
    File Name: /u03/backup/o1_mf_annnn_TAG20161123T145514_d3br3v24_.bkp
    
    RMAN> crosscheck backup;
    
    using channel ORA_DISK_1
    crosschecked backup piece: found to be 'EXPIRED'
    backup piece handle=/u04/fra/ora12mt/ORA12MT/backupset/2016_11_23/o1_mf_annnn_TAG20161123T145514_d3br3v24_.bkp RECID=3 STAMP=928680915
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/u03/backup/o1_mf_annnn_TAG20161123T145514_d3br3v24_.bkp RECID=15 STAMP=930650099
    crosschecked backup piece: found to be 'EXPIRED'
    backup piece handle=/u04/fra/ora12mt/ORA12MT/backupset/2016_11_23/o1_mf_nnndf_TAG20161123T145518_d3br3yyn_.bkp RECID=4 STAMP=928680918
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/u03/backup/o1_mf_nnndf_TAG20161123T145518_d3br3yyn_.bkp RECID=11 STAMP=930650099
    crosschecked backup piece: found to be 'EXPIRED'
    backup piece handle=/u04/fra/ora12mt/ORA12MT/37E8C85AD7431EDBE0531602A8C02428/backupset/2016_11_23/o1_mf_nnndf_TAG20161123T145518_d3br6n46_.bkp RECID=5 STAMP=928681004
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/u03/backup/o1_mf_nnndf_TAG20161123T145518_d3br6n46_.bkp RECID=10 STAMP=930650099
    crosschecked backup piece: found to be 'EXPIRED'
    backup piece handle=/u04/fra/ora12mt/ORA12MT/37E8BA3824481B77E0531602A8C080DD/backupset/2016_11_23/o1_mf_nnndf_TAG20161123T145518_d3br8c6d_.bkp RECID=6 STAMP=928681059
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/u03/backup/o1_mf_nnndf_TAG20161123T145518_d3br8c6d_.bkp RECID=13 STAMP=930650099
    crosschecked backup piece: found to be 'EXPIRED'
    backup piece handle=/u04/fra/ora12mt/ORA12MT/backupset/2016_11_23/o1_mf_annnn_TAG20161123T145824_d3br9rpz_.bkp RECID=7 STAMP=928681104
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/u03/backup/o1_mf_annnn_TAG20161123T145824_d3br9rpz_.bkp RECID=9 STAMP=930650099
    crosschecked backup piece: found to be 'EXPIRED'
    backup piece handle=/u04/fra/ora12mt/ORA12MT/autobackup/2016_11_23/o1_mf_s_928681105_d3br9ton_.bkp RECID=8 STAMP=928681106
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/u03/backup/o1_mf_s_928681105_d3br9ton_.bkp RECID=14 STAMP=930650099
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/u03/backup/o1_mf_s_928683729_d3btvsvn_.bkp RECID=12 STAMP=930650099
    Crosschecked 13 objects
    
    RMAN> delete expired backup ;
    
    using channel ORA_DISK_1
    
    List of Backup Pieces
    BP Key BS Key Pc# Cp# Status Device Type Piece Name
    ------- ------- --- --- ----------- ----------- ----------
    3 3 1 1 EXPIRED DISK /u04/fra/ora12mt/ORA12MT/backupset/2016_11_23/o1_mf_annnn_TAG20161123T145514_d3br3v24_.bkp
    4 4 1 1 EXPIRED DISK /u04/fra/ora12mt/ORA12MT/backupset/2016_11_23/o1_mf_nnndf_TAG20161123T145518_d3br3yyn_.bkp
    5 5 1 1 EXPIRED DISK /u04/fra/ora12mt/ORA12MT/37E8C85AD7431EDBE0531602A8C02428/backupset/2016_11_23/o1_mf_nnndf_TAG20161123T145518_d3br6n46_.bkp
    6 6 1 1 EXPIRED DISK /u04/fra/ora12mt/ORA12MT/37E8BA3824481B77E0531602A8C080DD/backupset/2016_11_23/o1_mf_nnndf_TAG20161123T145518_d3br8c6d_.bkp
    7 7 1 1 EXPIRED DISK /u04/fra/ora12mt/ORA12MT/backupset/2016_11_23/o1_mf_annnn_TAG20161123T145824_d3br9rpz_.bkp
    8 8 1 1 EXPIRED DISK /u04/fra/ora12mt/ORA12MT/autobackup/2016_11_23/o1_mf_s_928681105_d3br9ton_.bkp
    
    Do you really want to delete the above objects (enter YES or NO)? yes
    deleted backup piece
    backup piece handle=/u04/fra/ora12mt/ORA12MT/backupset/2016_11_23/o1_mf_annnn_TAG20161123T145514_d3br3v24_.bkp RECID=3 STAMP=928680915
    deleted backup piece
    backup piece handle=/u04/fra/ora12mt/ORA12MT/backupset/2016_11_23/o1_mf_nnndf_TAG20161123T145518_d3br3yyn_.bkp RECID=4 STAMP=928680918
    deleted backup piece
    backup piece handle=/u04/fra/ora12mt/ORA12MT/37E8C85AD7431EDBE0531602A8C02428/backupset/2016_11_23/o1_mf_nnndf_TAG20161123T145518_d3br6n46_.bkp RECID=5 STAMP=928681004
    deleted backup piece
    backup piece handle=/u04/fra/ora12mt/ORA12MT/37E8BA3824481B77E0531602A8C080DD/backupset/2016_11_23/o1_mf_nnndf_TAG20161123T145518_d3br8c6d_.bkp RECID=6 STAMP=928681059
    deleted backup piece
    backup piece handle=/u04/fra/ora12mt/ORA12MT/backupset/2016_11_23/o1_mf_annnn_TAG20161123T145824_d3br9rpz_.bkp RECID=7 STAMP=928681104
    deleted backup piece
    backup piece handle=/u04/fra/ora12mt/ORA12MT/autobackup/2016_11_23/o1_mf_s_928681105_d3br9ton_.bkp RECID=8 STAMP=928681106
    Deleted 6 EXPIRED objects
    • 获取数据库结构(需要准备编写恢复脚本)
    RMAN> report schema;
    
    using target database control file instead of recovery catalog
    RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
    Report of database schema for database with db_unique_name ORA12MT
    
    List of Permanent Datafiles
    ===========================
    File Size(MB) Tablespace RB segs Datafile Name
    ---- -------- -------------------- ------- ------------------------
    1 0 SYSTEM *** /u04/database/oradata/ora12mt/data_data_D-ORA12MT_I-4146636107_TS-SYSTEM_FNO-1_0brcua0p
    3 0 SYSAUX *** /u04/database/oradata/ora12mt/data_data_D-ORA12MT_I-4146636107_TS-SYSAUX_FNO-3_0crcua0s
    4 0 UNDOTBS1 *** /u04/database/oradata/ora12mt/data_data_D-ORA12MT_I-4146636107_TS-UNDOTBS1_FNO-4_0frcua1q
    5 0 PDB$SEED:SYSTEM *** /u04/database/oradata/ora12mt/data_data_D-ORA12MT_I-4146636107_TS-SYSTEM_FNO-5_0grcua21
    6 0 USERS *** /u04/database/oradata/ora12mt/data_data_D-ORA12MT_I-4146636107_TS-USERS_FNO-6_0ircua2f
    7 0 PDB$SEED:SYSAUX *** /u04/database/oradata/ora12mt/data_data_D-ORA12MT_I-4146636107_TS-SYSAUX_FNO-7_0drcua1b
    8 0 PDB1:SYSTEM *** /u04/database/oradata/ora12mt/data_data_D-ORA12MT_I-4146636107_TS-SYSTEM_FNO-8_0hrcua28
    9 0 PDB1:SYSAUX *** /u04/database/oradata/ora12mt/data_data_D-ORA12MT_I-4146636107_TS-SYSAUX_FNO-9_0ercua1i
    10 0 PDB1:USERS *** /u04/database/oradata/ora12mt/data_data_D-ORA12MT_I-4146636107_TS-USERS_FNO-10_0jrcua2g
    11 0 T1 *** /u04/database/oradata/ora12mt/t1.dbf
    12 0 PDB1:T1 *** /u04/database/oradata/ora12mt/t1-pdb1.dbf
    
    List of Temporary Files
    =======================
    File Size(MB) Tablespace Maxsize(MB) Tempfile Name
    ---- -------- -------------------- ----------- --------------------
    1 60 TEMP 32767 /u04/database/oradata/ora12mt/ORA12MT/datafile/o1_mf_temp_ctpogy8o_.tmp
    2 20 PDB$SEED:TEMP 32767 /u03/database/oradata/ora12mt/ORA12MT/datafile/pdbseed_temp012016-07-18_05-31-24-PM.dbf
    3 20 PDB1:TEMP 32767 /u04/database/oradata/ora12mt/ORA12MT/37E8C85AD7431EDBE0531602A8C02428/datafile/o1_mf_temp_cttxkklv_.tmp
    
    
    • 我们仅恢复表数据所涉及的表空间及文件

    -- SYSTEM,SYSAUX,<undo tablespace> of ROOT (如果RAC下每个节点使用包括所有undo表空间)

    -- SYSTEM,SYSAUX of SEED

    -- SYSTEM,SYSAUX 和可插拔数据库下的 <包含表数据的表空间> 

    恢复语法如下:

    restore database root skip tablespace <除system和sysaux外的表空间> database "PDB$SEED" database <包含有相关表数据的PDB> skip tablespace <不包含数据的表空间>;

    (这里例子中表数据在PDB1下USERS表空间中)

    RMAN> run{
    allocate channel t1 type disk;
    allocate channel t2 type disk;
    allocate channel t3 type disk;
    allocate channel t4 type disk;
    SET UNTIL TIME = "to_date('2016-11-23 14:56:30','YYYY/MM/DD HH24:MI:SS')";
    set newname for database root to '/u03/database/oradata/ora12mt/%U';
    set newname for database "PDB$SEED" to '/u03/database/oradata/ora12mt/%U';
    set newname for datafile 8 to '/u03/database/oradata/ora12mt/%U';
    set newname for datafile 9 to '/u03/database/oradata/ora12mt/%U';
    set newname for datafile 10 to '/u03/database/oradata/ora12mt/%U';
    restore database root skip tablespace users database "PDB$SEED" database PDB1 skip tablespace PDB1:T1;
    switch datafile 1;
    switch datafile 3;
    switch datafile 4;
    switch datafile 5;
    switch datafile 7;
    switch datafile 8;
    switch datafile 9;
    switch datafile 10;
    }2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21>
    
    released channel: ORA_DISK_1
    allocated channel: t1
    channel t1: SID=242 device type=DISK
    
    allocated channel: t2
    channel t2: SID=355 device type=DISK
    
    allocated channel: t3
    channel t3: SID=8 device type=DISK
    
    allocated channel: t4
    channel t4: SID=125 device type=DISK
    
    executing command: SET until clause
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    executing command: SET NEWNAME
    
    Starting restore at 15-DEC-16
    
    channel t1: starting datafile backup set restore
    channel t1: specifying datafile(s) to restore from backup set
    channel t1: restoring datafile 00001 to /u03/database/oradata/ora12mt/data_D-ORA12MT_TS-SYSTEM_FNO-1
    channel t1: restoring datafile 00003 to /u03/database/oradata/ora12mt/data_D-ORA12MT_TS-SYSAUX_FNO-3
    channel t1: restoring datafile 00004 to /u03/database/oradata/ora12mt/data_D-ORA12MT_TS-UNDOTBS1_FNO-4
    channel t1: reading from backup piece /u03/backup/o1_mf_nnndf_TAG20161123T145518_d3br3yyn_.bkp
    channel t2: starting datafile backup set restore
    channel t2: specifying datafile(s) to restore from backup set
    channel t2: restoring datafile 00008 to /u03/database/oradata/ora12mt/data_D-ORA12MT_TS-SYSTEM_FNO-8
    channel t2: restoring datafile 00009 to /u03/database/oradata/ora12mt/data_D-ORA12MT_TS-SYSAUX_FNO-9
    channel t2: restoring datafile 00010 to /u03/database/oradata/ora12mt/data_D-ORA12MT_TS-USERS_FNO-10
    channel t2: reading from backup piece /u03/backup/o1_mf_nnndf_TAG20161123T145518_d3br6n46_.bkp
    channel t3: starting datafile backup set restore
    channel t3: specifying datafile(s) to restore from backup set
    channel t3: restoring datafile 00005 to /u03/database/oradata/ora12mt/data_D-ORA12MT_TS-SYSTEM_FNO-5
    channel t3: restoring datafile 00007 to /u03/database/oradata/ora12mt/data_D-ORA12MT_TS-SYSAUX_FNO-7
    channel t3: reading from backup piece /u03/backup/o1_mf_nnndf_TAG20161123T145518_d3br8c6d_.bkp
    channel t3: piece handle=/u03/backup/o1_mf_nnndf_TAG20161123T145518_d3br8c6d_.bkp tag=TAG20161123T145518
    channel t3: restored backup piece 1
    channel t3: restore complete, elapsed time: 00:01:45
    channel t2: piece handle=/u03/backup/o1_mf_nnndf_TAG20161123T145518_d3br6n46_.bkp tag=TAG20161123T145518
    channel t2: restored backup piece 1
    channel t2: restore complete, elapsed time: 00:01:56
    channel t1: piece handle=/u03/backup/o1_mf_nnndf_TAG20161123T145518_d3br3yyn_.bkp tag=TAG20161123T145518
    channel t1: restored backup piece 1
    channel t1: restore complete, elapsed time: 00:02:36
    Finished restore at 15-DEC-16
    
    datafile 1 switched to datafile copy
    input datafile copy RECID=53 STAMP=930653716 file name=/u03/database/oradata/ora12mt/data_D-ORA12MT_TS-SYSTEM_FNO-1
    
    datafile 3 switched to datafile copy
    input datafile copy RECID=54 STAMP=930653716 file name=/u03/database/oradata/ora12mt/data_D-ORA12MT_TS-SYSAUX_FNO-3
    
    datafile 4 switched to datafile copy
    input datafile copy RECID=55 STAMP=930653716 file name=/u03/database/oradata/ora12mt/data_D-ORA12MT_TS-UNDOTBS1_FNO-4
    
    datafile 5 switched to datafile copy
    input datafile copy RECID=56 STAMP=930653716 file name=/u03/database/oradata/ora12mt/data_D-ORA12MT_TS-SYSTEM_FNO-5
    
    datafile 7 switched to datafile copy
    input datafile copy RECID=57 STAMP=930653716 file name=/u03/database/oradata/ora12mt/data_D-ORA12MT_TS-SYSAUX_FNO-7
    
    datafile 8 switched to datafile copy
    input datafile copy RECID=58 STAMP=930653716 file name=/u03/database/oradata/ora12mt/data_D-ORA12MT_TS-SYSTEM_FNO-8
    
    datafile 9 switched to datafile copy
    input datafile copy RECID=59 STAMP=930653716 file name=/u03/database/oradata/ora12mt/data_D-ORA12MT_TS-SYSAUX_FNO-9
    
    datafile 10 switched to datafile copy
    input datafile copy RECID=60 STAMP=930653717 file name=/u03/database/oradata/ora12mt/data_D-ORA12MT_TS-USERS_FNO-10
    released channel: t1
    released channel: t2
    released channel: t3
    released channel: t4
    • 从备份中定位归档并恢复
    RMAN> list backup of archivelog all;
    
    List of Backup Sets
    ===================
    
    BS Key Size Device Type Elapsed Time Completion Time
    ------- ---------- ----------- ------------ ---------------
    3 10.21M DISK 00:00:03 23-NOV-16
    BP Key: 15 Status: AVAILABLE Compressed: YES Tag: TAG20161123T145514
    Piece Name: /u03/backup/o1_mf_annnn_TAG20161123T145514_d3br3v24_.bkp
    
    List of Archived Logs in backup set 3
    Thrd Seq Low SCN Low Time Next SCN Next Time
    ---- ------- ---------- --------- ---------- ---------
    1 54 2107527 11-OCT-16 7688704 23-NOV-16
    1 55 7688704 23-NOV-16 7689057 23-NOV-16
    
    BS Key Size Device Type Elapsed Time Completion Time
    ------- ---------- ----------- ------------ ---------------
    7 13.00K DISK 00:00:00 23-NOV-16
    BP Key: 9 Status: AVAILABLE Compressed: YES Tag: TAG20161123T145824
    Piece Name: /u03/backup/o1_mf_annnn_TAG20161123T145824_d3br9rpz_.bkp
    
    List of Archived Logs in backup set 7
    Thrd Seq Low SCN Low Time Next SCN Next Time
    ---- ------- ---------- --------- ---------- ---------
    1 56 7689057 23-NOV-16 7689108 23-NOV-16
    1 57 7689108 23-NOV-16 7689190 23-NOV-16
    
    RMAN> restore archivelog from logseq 54 until logseq 57;
    
    Starting restore at 15-DEC-16
    using channel ORA_DISK_1
    
    channel ORA_DISK_1: starting archived log restore to default destination
    channel ORA_DISK_1: restoring archived log
    archived log thread=1 sequence=54
    channel ORA_DISK_1: restoring archived log
    archived log thread=1 sequence=55
    channel ORA_DISK_1: reading from backup piece /u03/backup/o1_mf_annnn_TAG20161123T145514_d3br3v24_.bkp
    channel ORA_DISK_1: piece handle=/u03/backup/o1_mf_annnn_TAG20161123T145514_d3br3v24_.bkp tag=TAG20161123T145514
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
    channel ORA_DISK_1: starting archived log restore to default destination
    channel ORA_DISK_1: restoring archived log
    archived log thread=1 sequence=56
    channel ORA_DISK_1: restoring archived log
    archived log thread=1 sequence=57
    channel ORA_DISK_1: reading from backup piece /u03/backup/o1_mf_annnn_TAG20161123T145824_d3br9rpz_.bkp
    channel ORA_DISK_1: piece handle=/u03/backup/o1_mf_annnn_TAG20161123T145824_d3br9rpz_.bkp tag=TAG20161123T145824
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
    Finished restore at 15-DEC-16
    
    
    
    Recovery Manager complete.
    • 现在将所有不需要进行恢复的数据文件offline掉
    $ sqlplus "/as sysdba"
    
    SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 11:37:16 2016
    
    Copyright (c) 1982, 2014, Oracle. All rights reserved.
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    
    SQL> select 'container : '||c.name|| ' : alter database datafile '||d.file#||' offline drop;' from v$datafile d ,v$containers c
    2 where d.con_id=c.con_id
    3 and d.file# in (select file# from v$datafile_header where length(error)>=1)
    4 order by c.con_id;
    
    'CONTAINER:'||C.NAME||':ALTERDATABASEDATAFILE'||D.FILE#||'OFFLINEDROP;'
    --------------------------------------------------------------------------------
    container : CDB$ROOT : alter database datafile 6 offline drop;
    container : CDB$ROOT : alter database datafile 11 offline drop;
    container : PDB1 : alter database datafile 12 offline drop;
    
    SQL> show con_name
    
    CON_NAME
    ------------------------------
    CDB$ROOT
    SQL> alter database datafile 6 offline drop;
    
    Database altered.
    
    SQL> alter database datafile 11 offline drop;
    
    Database altered.
    
    SQL> alter session set container=PDB1;
    
    Session altered.
    
    SQL> alter database datafile 12 offline drop;
    
    Database altered.
    
    SQL>
    • 完成表时间点恢复
     SQL>recover automatic database UNTIL TIME '2016/11/23 14:56:30' USING BACKUP CONTROLFILE;
    
    Media recovery complete.
    • 查看redo日志位置和命名,按需修改并以resetlogs打开数据库。
    SQL> select member from v$logfile;
    
    MEMBER
    --------------------------------------------------------------------------------
    /u04/database/oradata/ora12mt/redo04_1.dbf
    /u04/database/oradata/ora12mt/redo05_1.dbf
    /u04/database/oradata/ora12mt/redo06_1.dbf
    
    SQL> alter database rename file '/u04/database/oradata/ora12mt/redo04_1.dbf' to '/u03/database/oradata/ora12mt/redo-01.dbf';
    
    Database altered.
    
    SQL> alter database rename file '/u04/database/oradata/ora12mt/redo05_1.dbf' to '/u03/database/oradata/ora12mt/redo-02.dbf';
    
    Database altered.
    
    SQL> alter database rename file '/u04/database/oradata/ora12mt/redo06_1.dbf' to '/u03/database/oradata/ora12mt/redo-03.dbf';
    
    Database altered.
    
    SQL> alter database open resetlogs;
    
    Database altered.
    
    SQL>
    • 好了,这样我们就能从辅助库中将表数据导出并导入回生产库了。

    展开全文
  • 关于硬盘数据恢复方法,我想我是有发言权的,前两天不知道什么原因,电脑开机之后硬盘F盘保存的电影等大文件都找不到了,存放电影的文件夹是空,可能是我不小心误删了,然后我就疯狂找硬盘数据恢复方法想要恢复回来...

    硬盘坏了数据可以恢复吗?关于硬盘数据恢复方法,我想我是有发言权的,前两天不知道什么原因,电脑开机之后硬盘F盘保存的电影等大文件都找不到了,存放电影的文件夹是空,可能是我不小心误删了,然后我就疯狂找硬盘数据恢复方法想要恢复回来,经过我不懈的努力,尝试了很多恢复方法以及硬盘数据恢复软件后,终于找回了误删除的硬盘数据!

    知乎视频www.zhihu.com

    一、选择一款靠谱的数据恢复软件,我一般使用的是【嗨格式数据恢复大师】

    这款数据恢复软件有以下优势:

    1、简易的界面,向导模式,使用起来极其简单。

    2、软件支持免费的扫描预览功,事先就可以判断数据能否恢复。

    3、恢复能力强,仅需简单的几个步骤完成数据的恢复。

    4、在扫描的时候,可以边预览边扫描,不用一直等到扫描的结束。

    5、功能多样化,可以恢复存储卡,相机,SD卡,u盘,电脑,回收站等存储设备上的数据。

    6、支持恢复照片,文档(Word,Excel,PPT等),视频,音频,等数据类型。

    55c7aa948778100257d26abb1e1b67df.png

    二、接下来,我们看看基本的恢复数据教程吧。

    1、打开软件,选择主界面的【磁盘分区丢失恢复】

    2、选择要恢复的分区,然后点击搜索分区,待查找到该分区,点击下方开始扫描即可。

    3、扫描结束后,界面右侧就会展示所扫描到的数据文件。因为恢复的文件较多,可以根据【文件类型】或【文件路径】来找到要恢复的文件,然后点击恢复即可。

    9b69761eec4c332a0d9b68aad349d691.png

    三、此外,还要提醒大家在运用硬盘时要注意的几个问题:

    1. 在硬盘进行数据读写过程中,不要关闭电源

    2. 注意防尘,保持运用环境的清洁卫生

    3. 防止硬盘受震动

    4. 控制温度防止高温潮湿,磁场的影响

    5. 定期整理硬盘

    6. 防止计算机病毒对硬盘的破坏

    c68eb6d924d4a665fba477c4d5e9a131.png

    以上就是硬盘坏了数据恢复的方法介绍啦,希望能帮助到大家找到硬盘丢失的数据,也提醒大家要经常检查硬盘健康程度,及时发现并修复硬盘。

    展开全文
  • truncate 命令删除恢复

    2015-04-30 16:29:00
    truncate命令可以一次性删除当前表中所有记录并且不留任何日志,同时这个表的ID就自动初化从1开始,今天我就来给大家尝试一个利用truncate清除记录之后恢复过程。 实际线上的场景比较复杂,当时涉及了truncate, ...

    truncate命令可以一次性删除当前表中所有记录并且不留任何日志,同时这个表的ID就自动初化从1开始,今天我就来给大家尝试一个利用truncate清除记录之后恢复过程。

    实际线上的场景比较复杂,当时涉及了truncate, delete 两个操作,经确认丢数据差不多7万多行,等停下来时,差不多又有共计1万多行数据写入。 这里为了简单说明,只拿弄一个简单的业务场景举例。

    测试环境: Percona-Server-5.6.16
    日志格式: mixed 没起用gtid
    表结构如下:
    代码如下 复制代码
    CREATETABLE`tb_wubx`(`id`INT(11)NOTNULLAUTO_INCREMENT,`name`VARCHAR(32)DEFAULTNULL,PRIMARYKEY(`id`)) ENGINE=InnoDB AUTO_INCREMENT=2DEFAULT CHARSET=utf8
    基于某个时间点有一个备份或是有全量的binlog是能恢复数据的一个唯一保证。 例如我们的备份就是一个表结构创建语句,binlog pos相关信息: mysql-bin.000004 , 4,然后进行了如下:
    代码如下 复制代码
    -t1时间 程序写入:
    insert into tb_wubx(name) values(‘张三’),(‘李四’);
    insert into tb_wubx(name) values(‘隔壁老王’);
    -t2时间 某个人员失误
    truncate table tb_wubx;
    -t3时间 程序写入
    insert into tb_wubx(name) values(‘老赵’);
    update tb_wubx set name=’老赵赵’ where id=1;
    现在表里的数据情况:
    代码如下 复制代码
    mysql>SELECT*FROM tb_wubx;
    +----+-----------+| id | name |+----+-----------+|1| 老赵赵 |+----+-----------+1ROWINSET(0.00 sec)
    可以见truncate table操作后,表的自增id又变更为从1开始,原来写入的数据应该是:
    +—-+———-+
    | id | name |
    +—-+———-+
    | 1 | 张三 |
    +—-+———-+
    | 2 | 李四 |
    +—-+———-+
    | 3 | 隔壁老王 |
    +—-+———-+
    如果没生truncate table操作,实际的数据应该为:
    代码如下 复制代码
    +—-+———-+
    | id | name |
    +—-+———-+
    | 1 | 张三 |
    +—-+———-+
    | 2 | 李四 |
    +—-+———-+
    | 3 | 隔壁老王 |
    +—-+———-+
    | 4 | 老赵赵 |
    +—-+———-+
    而且线上的恢复那个表时和序序开发人员了解才知道,原来那个id和缓存及其它地方有依赖,因为id乱了,也会造成程序错乱。这个时间修复id在程序层错乱的事,留给开发人员了关建是给他们讲明白恢复的结果是什么样,我们的关建任务是把数据恢复出来。好,接下来的工作是开始从binlog中恢复数据。
    利用: show binary logs; 查看当的log文件分布, 然后利用show binlog events in ‘binary log文件’; 查看log文件的内容,目的是找到truncate发生的日志位置。
    另外因为基于备份(由log的启始位置)或是从量log, 如果基于备份有log的起始位置,我们需要处理的log文件是启始位置到发生truncate的日值(后面的数据处理不了,会发生主建冲突的错误造成truncate后的数据不能恢复),
    如果是全量日志,需要从创建完mysql后库后的日志去处理到当前的发生truncate的位置(后面数据会因为主建冲突写不进去)
    恢复准备工作,创建一个库用于恢复数据,这里创建了一个re_wubx, 及原结构的表: tb_wubx (相当于恢复了备份,过程省略)
    作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.
    代码如下 复制代码
    mysql>SHOWBINARY logs;
    +------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 |143|| mysql-bin.000002 |261|| mysql-bin.000003 |562|| mysql-bin.000004 |1144|+------------------+-----------+4ROWSINSET(0.00 sec)
    我这里有一个备份文件就是那个创建表的sql语句,位置是mysql-bin.000004 , 4
    在这个案例里我只用cover住mysql-bin.000004这个文件。
    代码如下 复制代码
    mysql>SHOW binlog events IN'mysql-bin.000004';
    +------------------+------+-------------+-----------+-------------+----------------------------------------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+------------------+------+-------------+-----------+-------------+----------------------------------------------------+| mysql-bin.000004 |4| Format_desc |753306|120| Server ver: 5.6.16-64.2-rel64.2-log, Binlog ver: 4|| mysql-bin.000004 |120| Query |753306|209|USE`wubx`; TRUNCATETABLE tb_wubx || mysql-bin.000004 |209| Query |753306|281|BEGIN|| mysql-bin.000004 |281| Table_map |753306|334| table_id: 91(wubx.tb_wubx)|| mysql-bin.000004 |334| Write_rows |753306|393| table_id: 91 flags: STMT_END_F || mysql-bin.000004 |393| Xid |753306|424| COMMIT /* xid=1073 */|| mysql-bin.000004 |424| Query |753306|496|BEGIN|| mysql-bin.000004 |496| Table_map |753306|549| table_id: 91(wubx.tb_wubx)|| mysql-bin.000004 |549| Write_rows |753306|602| table_id: 91 flags: STMT_END_F || mysql-bin.000004 |602| Xid |753306|633| COMMIT /* xid=1074 */|| mysql-bin.000004 |633| Query |753306|722|USE`wubx`; TRUNCATETABLE tb_wubx || mysql-bin.000004 |722| Query |753306|794|BEGIN|| mysql-bin.000004 |794| Table_map |753306|847| table_id: 92(wubx.tb_wubx)|| mysql-bin.000004 |847| Write_rows |753306|894| table_id: 92 flags: STMT_END_F || mysql-bin.000004 |894| Xid |753306|925| COMMIT /* xid=1081 */|| mysql-bin.000004 |925| Query |753306|997|BEGIN|| mysql-bin.000004 |997| Table_map |753306|1050| table_id: 92(wubx.tb_wubx)|| mysql-bin.000004 |1050| Update_rows |753306|1113| table_id: 92 flags: STMT_END_F || mysql-bin.000004 |1113| Xid |753306|1144| COMMIT /* xid=1084 */|+------------------+------+-------------+-----------+-------------+----------------------------------------------------+19ROWSINSET(0.00 sec)
    看到这个表刚开始就发生一次truncate, 那其实也可以说明我就恢复刚开始那个truncate到后来那个误操作的truncate table的语句之间的数据就是丢失的数据。
    这个恢复可以从mysql-bin.000004 pos: 4到mysql-bin.000004 pos: 633 即:
    代码如下 复制代码
    mysqlbinlog --rewrite-db='wubx-&gt;re_wubx' --start-position=4 --stop-position=633 mysql-bin.000004 |mysql -S /tmp/mysql.sock re_wubx
    恢复结果如下:
    代码如下 复制代码
    mysql -S /tmp/mysql.sock re_wubx;
    mysql>SELECTCOUNT(*)FROM tb_wubx;
    +----------+|COUNT(*)|+----------+|3|+----------+1ROWINSET(0.02 sec)

    mysql>SELECT*FROM tb_wubx;
    +----+--------------+| id | name |+----+--------------+|1| 张三 ||2| 李四 ||3| 隔壁老王 |+----+--------------+3ROWSINSET(0.00 sec)

    mysql>INSERTINTO tb_wubx(name)SELECT name FROM wubx.tb_wubx;
    Query OK,1ROW affected (0.00 sec)
    Records: 1 Duplicates: 0 Warnings: 0

    mysql>RENAMETABLE wubx.tb_wubx TO wubx.bak_tb_wubx;
    Query OK,0ROWS affected (0.04 sec)

    mysql>RENAMETABLE re_wubx.tb_wubx TO wubx.tb_wubx;
    Query OK,0ROWS affected (0.03 sec)

    mysql>SELECT*FROM wubx.tb_wubx;
    +----+--------------+| id | name |+----+--------------+|1| 张三 ||2| 李四 ||3| 隔壁老王 ||4| 老赵赵 |+----+--------------+4ROWSINSET(0.00 sec)
    恢复完成。
    想一想,如果我跳过那个truncate继续执行那些binlog会怎么样
    总结:从这次数据丢失给删除之后得到一些感想了我们还是要经常对数据库进行备份了,这样可以保证我们的数据不给丢失了,同时也可以保证我们网站数据安全。

    转载于:https://www.cnblogs.com/jiechn/p/4469239.html

    展开全文
  • 从其他表中导数据时不小心选择了truncate table,执行了sql之后truncate后又insert,现在使用fy_recover_data恢复不出来数据,prmdul显示该表没有被截断的数据。请问如何才能恢复truncate操作之前的数据呢?后面...
  • oracle truncate恢复工具

    2016-04-18 13:30:09
    PRM DUL for oracle恢复被truncate截断掉的表 Oracle DBA神器:PRM灾难恢复工具,Schema级别数据恢复。PRM For Oracle Database – schema级别oracle数据库数据恢复特性 ,PRM即ParnassusData Recovery Manager是...
  • 最近在写oracle抽取数据文件工具,在抽取数据文件恢复数据的时候发现,恢复数据比预期的多,经过对比之后发现是有一些表被truncate了,由于被truncate之后部分块还没有被覆盖。所以这样的数据也就抽取出来了。其实...

     最近在写oracle抽取数据文件工具,在抽取数据文件恢复数据的时候发现,恢复的数据比预期的多,经过对比之后发现是有一些表被truncate了,由于被truncate之后部分块还没有被覆盖。所以这样的数据也就抽取出来了。其实抽取工具的原理就是根据段头、三级块位图、二级块位图、一级块位图等,再加上系统表空间对应的数据文件。最后整合之后就可以转换成表数据。
    1、创建测试数据

    SQL> create tablespace py datafile '/u01/app/oradata/QXY/py.dbf' size 5M autoextend off;

    Tablespace created.

    SQL> create table pygc(id int) tablespace py;

    Table created.

    SQL> insert into pygc values (10);

    1 row created.

    SQL> insert into pygc values (11);

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select object_id, data_object_id, object_name from dba_objects where object_name='PYGC';

    OBJECT_ID DATA_OBJECT_ID OBJECT_NAME


    136026         136026 PYGC

    SQL>
    创建一个表空间PY,并在该表空间创建一张表PYGC插入两行数据。表PYGC的对象号为136026。

    2、dump表PYGC数据块的信息

    SQL> select distinct dbms_rowid.ROWID_RELATIVE_FNO(rowid), dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from PYGC;

    DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)


                                  15                                  134

    SQL>

    SQL>
    SQL> alter system dump datafile 15 block 134;

    System altered.

    SQL>

    SQL> select value from v$diag_info where name = 'Default Trace File';

    VALUE

    /u01/app/diag/rdbms/qxy/QXY/trace/QXY_ora_4123.trc

    SQL>

    Block dump from disk:
    buffer tsn: 17 rdba: 0x03c00086 (15/134)
    scn: 0x0000.0225a6ce seq: 0x01 flg: 0x06 tail: 0xa6ce0601
    frmt: 0x02 chkval: 0x8361 type: 0x06=trans data
    Hex dump of block: st=0, typ_found=1
    Dump of memory from 0x00007F43CF4AF000 to 0x00007F43CF4B1000
    7F43CF4AF000 0000A206 03C00086 0225A6CE 06010000 [..........%.....]
    7F43CF4AF010 00008361 00000001 0002135A 0225A6CB [a.......Z.....%.]
    7F43CF4AF020 00000000 0032F802 03C00080 00010017 [......2.........]
    7F43CF4AF030 00002CD0 04C023C2 00060593 00002002 [.,...#....... ..]
    7F43CF4AF040 0225A6CE 00000000 00000000 00000000 [..%.............]
    7F43CF4AF050 00000000 00000000 00000000 00000000 [................]
    7F43CF4AF060 00000000 00020100 0016FFFF 1F701F8C [..............p.]
    7F43CF4AF070 00001F70 1F920002 00001F8C 00000000 [p...............]
    7F43CF4AF080 00000000 00000000 00000000 00000000 [................]
    Repeat 502 times
    7F43CF4B0FF0 0201012C 012C0CC1 0BC10201 A6CE0601 [,.....,.........]
    Block header dump: 0x03c00086
    Object id on Block? Y
    seg/obj: 0x2135a csc: 0x00.225a6cb itc: 2 flg: E typ: 1 - DATA
    brn: 0 bdba: 0x3c00080 ver: 0x01 opc: 0
    inc: 0 exflg: 0

    Itl Xid Uba Flag Lck Scn/Fsc
    0x01 0x0017.001.00002cd0 0x04c023c2.0593.06 --U- 2 fsc 0x0000.0225a6ce
    0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
    bdba: 0x03c00086
    data_block_dump,data header at 0x7f43cf4af064

    tsiz: 0x1f98
    hsiz: 0x16
    pbl: 0x7f43cf4af064
    76543210
    flag=--------
    ntab=1
    nrow=2
    frre=-1
    fsbo=0x16
    fseo=0x1f8c
    avsp=0x1f70
    tosp=0x1f70
    0xe:pti[0] nrow=2 offs=0
    0x12:pri[0] offs=0x1f92
    0x14:pri[1] offs=0x1f8c
    block_row_dump:
    tab 0, row 0, @0x1f92
    tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
    col 0: [ 2] c1 0b
    tab 0, row 1, @0x1f8c
    tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
    col 0: [ 2] c1 0c
    end_of_block_dump
    End dump data blocks tsn: 17 file#: 15 minblk 134 maxblk 134
    可以看到15号文件的134块包含了2行数据c10b、c10c。该格式是raw格式,转换成number就是10、11。可以通过oracle有自带转换工具dbms_stats.convert_raw_value,我这里进行了封装,转换之后如下:

    SQL> select displayraw('c10b','NUMBER') from dual;

    DISPLAYRAW('C10B','NUMBER')

    10

    SQL> select displayraw('c10c','NUMBER') from dual;

    DISPLAYRAW('C10C','NUMBER')

    11

    SQL>
    3、查看表PYGC段头信息

    SQL> col segment_name for a20
    SQL> select SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name = 'PYGC';

    SEGMENT_NAME HEADER_FILE HEADER_BLOCK


    PYGC 15 130

    SQL>

    SQL> alter system dump datafile 15 block 130;

    System altered.

    SQL> select value from v$diag_info where name = 'Default Trace File';

    VALUE

    /u01/app/diag/rdbms/qxy/QXY/trace/QXY_ora_4893.trc

    SQL>

    buffer tsn: 17 rdba: 0x03c00082 (15/130)
    scn: 0x0000.0225a6cb seq: 0x01 flg: 0x04 tail: 0xa6cb2301
    frmt: 0x02 chkval: 0xbaf6 type: 0x23=PAGETABLE SEGMENT HEADER
    Hex dump of block: st=0, typ_found=1
    Dump of memory from 0x00007F193760EA00 to 0x00007F1937610A00
    7F193760EA00 0000A223 03C00082 0225A6CB 04010000 [#.........%.....]
    7F193760EA10 0000BAF6 00000000 00000000 00000000 [................]
    7F193760EA20 00000000 00000001 00000008 00000A9C [................]
    7F193760EA30 00000000 00000008 00000008 03C00088 [................]
    7F193760EA40 00000000 00000000 00000000 00000005 [................]
    7F193760EA50 00000000 00000000 00000000 00000000 [................]
    7F193760EA60 00000008 00000008 03C00088 00000000 [................]
    7F193760EA70 00000000 00000000 00000005 03C00080 [................]
    7F193760EA80 03C00080 00000000 00000000 00000000 [................]
    7F193760EA90 00000000 00000000 00000000 00000000 [................]
    Repeat 3 times
    7F193760EAD0 00000001 00002000 00000000 00001434 [..... ......4...]
    7F193760EAE0 00000000 03C00081 00000001 03C00080 [................]
    7F193760EAF0 03C00081 00000000 00000000 00000000 [................]
    7F193760EB00 00000000 00000000 00000001 00000000 [................]
    7F193760EB10 0002135A 10000000 03C00080 00000008 [Z...............]
    7F193760EB20 00000000 00000000 00000000 00000000 [................]
    Repeat 152 times
    7F193760F4B0 03C00080 03C00083 00000000 00000000 [................]
    7F193760F4C0 00000000 00000000 00000000 00000000 [................]
    Repeat 151 times
    7F193760FE40 00000000 00000000 03C00081 00000000 [................]
    7F193760FE50 00000000 00000000 00000000 00000000 [................]
    Repeat 185 times
    7F19376109F0 00000000 00000000 00000000 A6CB2301 [.............#..]
    Extent Control Header

    Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
    last map 0x00000000 #maps: 0 offset: 2716
    Highwater:: 0x03c00088 ext#: 0 blk#: 8 ext size: 8
    #blocks in seg. hdr's freelists: 0
    #blocks below: 5
    mapblk 0x00000000 offset: 0
    Unlocked

    Low HighWater Mark :
    Highwater:: 0x03c00088 ext#: 0 blk#: 8 ext size: 8
    #blocks in seg. hdr's freelists: 0
    #blocks below: 5
    mapblk 0x00000000 offset: 0
    Level 1 BMB for High HWM block: 0x03c00080
    Level 1 BMB for Low HWM block: 0x03c00080

    Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
    L2 Array start offset: 0x00001434
    First Level 3 BMB: 0x00000000
    L2 Hint for inserts: 0x03c00081
    Last Level 1 BMB: 0x03c00080
    Last Level II BMB: 0x03c00081
    Last Level III BMB: 0x00000000
    Map Header:: next 0x00000000 #extents: 1 obj#: 136026 flag: 0x10000000
    Inc # 0
    Extent Map

    0x03c00080 length: 8

    Auxillary Map

    Extent 0 : L1 dba: 0x03c00080 Data dba: 0x03c00083

    Second Level Bitmap block DBAs

    DBA 1: 0x03c00081

    End dump data blocks tsn: 17 file#: 15 minblk 130 maxblk 130
    该dump数据包含了高水位信息、块位图信息等。

    4、truncate前后用解析工具得到的数据

    [oracle@QXY1 jiexi]$ ./header
    kkkkkkk=130 <====代表段头数据在130块
    objectid=136026 <====对象号是136026
    address 3c00080 8 <====该对象包含的extent和对应的区长度
    addrend
    rdba=3c00086 <====真正的数据块地址,包含在了 address 3c00080 和3c00080+8之间,说明下面的内容属于对象136026
    columns_count=1 <====真正的数据内容,该对象只有1列
    c10c
    columns_count=1
    c10b
    [oracle@QXY1 jiexi]$

    SQL> alter system switch logfile;

    System altered.

    SQL> col member for a50
    SQL> select l.member from v$logfile l, v$log g where l.group#=g.group# and g.status='CURRENT';

    MEMBER

    /u01/app/oradata/QXY/redo02.log

    SQL>

    <======做truncate操作
    SQL> select * from pygc;

        ID

        10
        11

    SQL> truncate table pygc;

    Table truncated.

    SQL> select * from pygc;

    no rows selected

    SQL>

    [oracle@QXY1 jiexi]$
    [oracle@QXY1 jiexi]$ ./header <=====第二次解析
    kkkkkkk=130
    objectid=136027 <======解析之后发现对象号从136026变成了136027
    address 3c00080 8
    addrend
    rdba=3c00086
    columns_count=1
    c10c
    columns_count=1
    c10b
    [oracle@QXY1 jiexi]$
    这里不介绍把raw数据怎么结合解析system表空间数据文件得到数据转换成对应的表。可以参考之前写的文章。

    truncate之后,object_id和data_object_id的值就不一样了,如下:

    SQL> select object_id, data_object_id, object_name from dba_objects where object_name='PYGC';

    OBJECT_ID DATA_OBJECT_ID OBJECT_NAME


    136026         136027 PYGC

    SQL>
    5、再做一次段头块dump

    SQL> alter system dump datafile 15 block 130;

    System altered.

    SQL> select value from v$diag_info where name = 'Default Trace File';

    VALUE

    /u01/app/diag/rdbms/qxy/QXY/trace/QXY_ora_4925.trc

    SQL>

    buffer tsn: 17 rdba: 0x03c00082 (15/130)
    scn: 0x0000.0225a8a2 seq: 0x01 flg: 0x04 tail: 0xa8a22301
    frmt: 0x02 chkval: 0x8c34 type: 0x23=PAGETABLE SEGMENT HEADER
    Hex dump of block: st=0, typ_found=1
    Dump of memory from 0x00007FC77D732A00 to 0x00007FC77D734A00
    7FC77D732A00 0000A223 03C00082 0225A8A2 04010000 [#.........%.....]
    7FC77D732A10 00008C34 00000000 00000000 00000000 [4...............]
    7FC77D732A20 00000000 00000001 00000008 00000A9C [................]
    7FC77D732A30 00000000 00000003 00000008 03C00083 [................]
    7FC77D732A40 00000000 00000000 00000000 00000000 [................]
    7FC77D732A50 0014001B 000036CD 00000001 00000000 [.....6..........]
    7FC77D732A60 00000003 00000008 03C00083 00000000 [................]
    7FC77D732A70 00000000 00000000 00000000 03C00080 [................]
    7FC77D732A80 03C00080 00000000 00000000 00000000 [................]
    7FC77D732A90 00000000 00000000 00000000 00000000 [................]
    Repeat 3 times
    7FC77D732AD0 00000001 00002000 00000000 00001434 [..... ......4...]
    7FC77D732AE0 00000000 03C00081 00000001 03C00080 [................]
    7FC77D732AF0 03C00081 00000000 00000000 00000000 [................]
    7FC77D732B00 00000000 00000000 00000001 00000000 [................]
    7FC77D732B10 0002135B 10000000 03C00080 00000008 [[...............]
    7FC77D732B20 00000000 00000000 00000000 00000000 [................]
    Repeat 152 times
    7FC77D7334B0 03C00080 03C00083 00000000 00000000 [................]
    7FC77D7334C0 00000000 00000000 00000000 00000000 [................]
    Repeat 151 times
    7FC77D733E40 00000000 00000000 03C00081 00000000 [................]
    7FC77D733E50 00000000 00000000 00000000 00000000 [................]
    Repeat 185 times
    7FC77D7349F0 00000000 00000000 00000000 A8A22301 [.............#..]
    Extent Control Header

    Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
    last map 0x00000000 #maps: 0 offset: 2716
    Highwater:: 0x03c00083 ext#: 0 blk#: 3 ext size: 8 <===和truncate之前对比发现,高水位发生了变化
    #blocks in seg. hdr's freelists: 0
    #blocks below: 0
    mapblk 0x00000000 offset: 0
    Disk Lock:: Locked by xid: 0x001b.014.000036cd

    Low HighWater Mark :
    Highwater:: 0x03c00083 ext#: 0 blk#: 3 ext size: 8
    #blocks in seg. hdr's freelists: 0
    #blocks below: 0
    mapblk 0x00000000 offset: 0
    Level 1 BMB for High HWM block: 0x03c00080
    Level 1 BMB for Low HWM block: 0x03c00080

    Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
    L2 Array start offset: 0x00001434
    First Level 3 BMB: 0x00000000
    L2 Hint for inserts: 0x03c00081
    Last Level 1 BMB: 0x03c00080
    Last Level II BMB: 0x03c00081
    Last Level III BMB: 0x00000000
    Map Header:: next 0x00000000 #extents: 1 obj#: 136027 flag: 0x10000000
    Inc # 0
    Extent Map

    0x03c00080 length: 8

    Auxillary Map

    Extent 0 : L1 dba: 0x03c00080 Data dba: 0x03c00083

    Second Level Bitmap block DBAs

    DBA 1: 0x03c00081

    End dump data blocks tsn: 17 file#: 15 minblk 130 maxblk 130
    可以发现truncate前后,高位数的数据发生了变化,offset 272位置标记object_id的值从原来的136026(0002135A)变成了136027(0002135B ), truncate之前的高水位的地址为0x03c00088,并且高水位在0号区的第8个块的位置。
      truncate之后高水位的地址为0x03c00083,并且高水位在0号区的第3个块上面。

    6、truncate之后做数据块的dump

    SQL> alter system dump datafile 15 block 134;

    System altered.

    SQL> select value from v$diag_info where name = 'Default Trace File';

    VALUE

    /u01/app/diag/rdbms/qxy/QXY/trace/QXY_ora_4951.trc

    SQL>

    buffer tsn: 17 rdba: 0x03c00086 (15/134)
    scn: 0x0000.0225a6ce seq: 0x01 flg: 0x06 tail: 0xa6ce0601
    frmt: 0x02 chkval: 0x8361 type: 0x06=trans data
    Hex dump of block: st=0, typ_found=1
    Dump of memory from 0x00007FCD7A101A00 to 0x00007FCD7A103A00
    7FCD7A101A00 0000A206 03C00086 0225A6CE 06010000 [..........%.....]
    7FCD7A101A10 00008361 00000001 0002135A 0225A6CB [a.......Z.....%.]
    7FCD7A101A20 00000000 0032F802 03C00080 00010017 [......2.........]
    7FCD7A101A30 00002CD0 04C023C2 00060593 00002002 [.,...#....... ..]
    7FCD7A101A40 0225A6CE 00000000 00000000 00000000 [..%.............]
    7FCD7A101A50 00000000 00000000 00000000 00000000 [................]
    7FCD7A101A60 00000000 00020100 0016FFFF 1F701F8C [..............p.]
    7FCD7A101A70 00001F70 1F920002 00001F8C 00000000 [p...............]
    7FCD7A101A80 00000000 00000000 00000000 00000000 [................]
    Repeat 502 times
    7FCD7A1039F0 0201012C 012C0CC1 0BC10201 A6CE0601 [,.....,.........]
    Block header dump: 0x03c00086
    Object id on Block? Y
    seg/obj: 0x2135a csc: 0x00.225a6cb itc: 2 flg: E typ: 1 - DATA
    brn: 0 bdba: 0x3c00080 ver: 0x01 opc: 0
    inc: 0 exflg: 0

    Itl Xid Uba Flag Lck Scn/Fsc
    0x01 0x0017.001.00002cd0 0x04c023c2.0593.06 --U- 2 fsc 0x0000.0225a6ce
    0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
    bdba: 0x03c00086
    data_block_dump,data header at 0x7fcd7a101a64

    tsiz: 0x1f98
    hsiz: 0x16
    pbl: 0x7fcd7a101a64
    76543210
    flag=--------
    ntab=1
    nrow=2
    frre=-1
    fsbo=0x16
    fseo=0x1f8c
    avsp=0x1f70
    tosp=0x1f70
    0xe:pti[0] nrow=2 offs=0
    0x12:pri[0] offs=0x1f92
    0x14:pri[1] offs=0x1f8c
    block_row_dump:
    tab 0, row 0, @0x1f92
    tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
    col 0: [ 2] c1 0b
    tab 0, row 1, @0x1f8c
    tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
    col 0: [ 2] c1 0c
    end_of_block_dump
    End dump data blocks tsn: 17 file#: 15 minblk 134 maxblk 134
    truncate前后,数据块中的数据还是两行,可以理解为数据并没有被删除。(这个时候这个数据块是可以被覆盖的,因为当前块已经不属于任意段头块中的区范围)

    7、修改obj$

    SQL> desc obj$
    Name Null? Type


    OBJ# NOT NULL NUMBER
    DATAOBJ# NUMBER
    OWNER# NOT NULL NUMBER
    NAME NOT NULL VARCHAR2(30)
    NAMESPACE NOT NULL NUMBER
    SUBNAME VARCHAR2(30)
    TYPE# NOT NULL NUMBER
    CTIME NOT NULL DATE
    MTIME NOT NULL DATE
    STIME NOT NULL DATE
    STATUS NOT NULL NUMBER
    REMOTEOWNER VARCHAR2(30)
    LINKNAME VARCHAR2(128)
    FLAGS NUMBER
    OID$ RAW(16)
    SPARE1 NUMBER
    SPARE2 NUMBER
    SPARE3 NUMBER
    SPARE4 VARCHAR2(1000)
    SPARE5 VARCHAR2(1000)
    SPARE6 DATE

    SQL> select obj#,DATAOBJ#,NAME from obj$ where obj#=136026;

      OBJ#   DATAOBJ# NAME

    136026     136027 PYGC

    SQL>

    和dba_objects视图查到的结果一致
    SQL> select object_id, data_object_id, object_name from dba_objects where object_name='PYGC';

    OBJECT_ID DATA_OBJECT_ID OBJECT_NAME


    136026         136027 PYGC

    SQL>

    SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid), dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from obj$ where obj#=136026;

    DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)


                                   1                                82466

    SQL> alter system dump datafile 1 block 82466;

    System altered.

    SQL> select value from v$diag_info where name = 'Default Trace File';

    VALUE

    /u01/app/diag/rdbms/qxy/QXY/trace/QXY_ora_4996.trc

    SQL>

    把OBJECT_ID=136026转换成raw格式,然后在82466块的trace中查询位置

    SQL> select utl_raw.CAST_FROM_NUMBER(136026) from dual;

    UTL_RAW.CAST_FROM_NUMBER(136026)

    C30E3D1B

    SQL>

    SQL> select utl_raw.CAST_FROM_NUMBER(136027) from dual;

    UTL_RAW.CAST_FROM_NUMBER(136027)

    C30E3D1C

    SQL>

    即136026 = c3 0e 3d 1b
    136027 = c3 0e 3d 1c

    在文件/u01/app/diag/rdbms/qxy/QXY/trace/QXY_ora_4996.trc中查询对应的数据在row=69(这个数据和obj$对应)
    tab 0, row 69, @0x424
    tl: 67 fb: --H-FL-- lb: 0x1 cc: 18
    col 0: [ 4] c3 0e 3d 1b <===obj#
    col 1: [ 4] c3 0e 3d 1c <===dataobj#
    col 2: [ 1] 80
    col 3: [ 4] 50 59 47 43 <===name
    col 4: [ 2] c1 02
    col 5: NULL
    col 6: [ 2] c1 03
    col 7: [ 7] 78 77 06 0a 10 12 26
    col 8: [ 7] 78 77 06 0a 10 24 25
    col 9: [ 7] 78 77 06 0a 10 12 26
    col 10: [ 2] c1 02
    col 11: NULL
    col 12: NULL
    col 13: [ 1] 80
    col 14: NULL
    col 15: [ 2] c1 07
    col 16: [ 2] c1 02
    col 17: [ 1] 80

    把name=50 59 47 43 转换成varchar2

    SQL> select displayraw('50594743','VARCHAR2') from dual;

    DISPLAY_RAW('50594743','VARCHAR2')

    PYGC

    SQL>

    现在就用bbed,把dataobj#=136027 改成 136026

    BBED> info all
    File# Name Size(blks)


     1  /u01/app/oradata/QXY/system01.dbf                                    0
     2  /u01/app/oradata/QXY/sysaux01.dbf                                    0
     3  /u01/app/oradata/QXY/untbs1.dbf                                      0
     4  /u01/app/oradata/QXY/users01.dbf                                     0
     5  /u01/app/oradata/QXY/example01.dbf                                   0
     6  /u01/app/oradata/QXY/OGG.dbf                                         0
     7  /u01/app/oradata/test.dbf                                            0
     8  /u01/app/oradata/QXY/corrupt.dbf                                     0
     9  /u01/app/oradata/QXY/utb1.dbf                                        0
    10  /u01/app/oradata/QXY/undotbs03.dbf                                   0
    11  /u01/app/oradata/QXY/streams_tbs01.dbf                               0
    12  /u01/app/oradata/QXY/chenhao01.dbf                                   0
    13  /u01/app/oradata/QXY/t.dbf                                           0
    14  /u01/app/oradata/QXY/xxx.dbf                                         0
    15  /u01/app/oradata/QXY/py.dbf                                          0

    BBED>
    BBED> set dba 1,82466
    DBA 0x00414222 (4276770 1,82466)

    BBED> map
    File: /u01/app/oradata/QXY/system01.dbf (1)
    Block: 82466 Dba:0x00414222

    KTB Data Block (Table/Cluster)

    struct kcbh, 20 bytes @0

    struct ktbbh, 72 bytes @20

    struct kdbh, 14 bytes @92

    struct kdbt[1], 4 bytes @106

    sb2 kdbr[72] @110

    ub1 freespace[898] @254

    ub1 rowdata[7036] @1152

    ub4 tailchk @8188

    BBED> p *kdbr[69] <======从dump中可以知道在row=69
    rowdata[0]

    ub1 rowdata[0] @1152 0x2c

    BBED>

    BBED> d
    File: /u01/app/oradata/QXY/system01.dbf (1)
    Block: 82466 Offsets: 1152 to 1663 Dba:0x00414222

    2c011204 c30e3d1b 04c30e3d 1c018004 50594743 02c102ff 02c10307 7877060a
    10122607 7877060a 10242507 7877060a 10122602 c102ffff 0180ff02 c10702c1
    0201802c 001204c3 0e3d1804 c30e3d18 01800358 414202c1 02ff02c1 03077877
    060a0c20 25077877 060a0c20 25077877 060a0c20 2502c102 ffff0180 ff02c107
    02c10201 803c0112 04c30e3d 1604c30e 3d170180 014302c1 02ff02c1 03077877
    060a0b1c 3b077877 060a0b1f 2e077877 060a0b1c 3b02c102 ffff0180 ff02c107
    02c10201 803c0112 04c30e3d 1404c30e 3d150180 014302c1 02ff02c1 03077877
    060a0b17 01077877 060a0b1a 26077877 060a0b17 0102c102 ffff0180 ff02c107
    02c10201 803c0212 04c30e3d 1304c30e 3d130180 014302c1 02ff02c1 03077877
    060a0b16 2b077877 060a0b16 2b077877 060a0b16 2b02c102 ffff0180 ff02c107
    02c10201 803c0212 04c30e3d 0f04c30e 3d120180 04414243 4402c102 ff02c103
    07787706 0a071c08 07787706 0a0b102b 07787706 0a071c08 02c102ff ff0180ff
    02c10702 c1020180 3c021204 c30e3d0e 04c30e3d 0e018004 41424344 02c102ff
    02c10307 7877060a 05051507 7877060a 05051507 7877060a 05051502 c102ffff
    0180ff02 c10702c1 0201803c 011204c3 0e3d0804 c30e3d09 01800642 4c4f434b
    3302c102 ff02c103 07787706 060a3927 07787706 060a3a37 07787706 060a3927

    <32 bytes per line>

    BBED>
    现在把c30e3d1c改成c30e3d1b

    BBED> set offset +12
    OFFSET 1164

    BBED> d
    File: /u01/app/oradata/QXY/system01.dbf (1)
    Block: 82466 Offsets: 1164 to 1675 Dba:0x00414222

    1c018004 50594743 02c102ff 02c10307 7877060a 10122607 7877060a 10242507
    7877060a 10122602 c102ffff 0180ff02 c10702c1 0201802c 001204c3 0e3d1804
    c30e3d18 01800358 414202c1 02ff02c1 03077877 060a0c20 25077877 060a0c20
    25077877 060a0c20 2502c102 ffff0180 ff02c107 02c10201 803c0112 04c30e3d
    1604c30e 3d170180 014302c1 02ff02c1 03077877 060a0b1c 3b077877 060a0b1f
    2e077877 060a0b1c 3b02c102 ffff0180 ff02c107 02c10201 803c0112 04c30e3d
    1404c30e 3d150180 014302c1 02ff02c1 03077877 060a0b17 01077877 060a0b1a
    26077877 060a0b17 0102c102 ffff0180 ff02c107 02c10201 803c0212 04c30e3d
    1304c30e 3d130180 014302c1 02ff02c1 03077877 060a0b16 2b077877 060a0b16
    2b077877 060a0b16 2b02c102 ffff0180 ff02c107 02c10201 803c0212 04c30e3d
    0f04c30e 3d120180 04414243 4402c102 ff02c103 07787706 0a071c08 07787706
    0a0b102b 07787706 0a071c08 02c102ff ff0180ff 02c10702 c1020180 3c021204
    c30e3d0e 04c30e3d 0e018004 41424344 02c102ff 02c10307 7877060a 05051507
    7877060a 05051507 7877060a 05051502 c102ffff 0180ff02 c10702c1 0201803c
    011204c3 0e3d0804 c30e3d09 01800642 4c4f434b 3302c102 ff02c103 07787706
    060a3927 07787706 060a3a37 07787706 060a3927 02c102ff ff0180ff 02c10702

    <32 bytes per line>

    BBED> m /x 1b
    Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
    File: /u01/app/oradata/QXY/system01.dbf (1)
    Block: 82466 Offsets: 1164 to 1675 Dba:0x00414222

    1b018004 50594743 02c102ff 02c10307 7877060a 10122607 7877060a 10242507
    7877060a 10122602 c102ffff 0180ff02 c10702c1 0201802c 001204c3 0e3d1804
    c30e3d18 01800358 414202c1 02ff02c1 03077877 060a0c20 25077877 060a0c20
    25077877 060a0c20 2502c102 ffff0180 ff02c107 02c10201 803c0112 04c30e3d
    1604c30e 3d170180 014302c1 02ff02c1 03077877 060a0b1c 3b077877 060a0b1f
    2e077877 060a0b1c 3b02c102 ffff0180 ff02c107 02c10201 803c0112 04c30e3d
    1404c30e 3d150180 014302c1 02ff02c1 03077877 060a0b17 01077877 060a0b1a
    26077877 060a0b17 0102c102 ffff0180 ff02c107 02c10201 803c0212 04c30e3d
    1304c30e 3d130180 014302c1 02ff02c1 03077877 060a0b16 2b077877 060a0b16
    2b077877 060a0b16 2b02c102 ffff0180 ff02c107 02c10201 803c0212 04c30e3d
    0f04c30e 3d120180 04414243 4402c102 ff02c103 07787706 0a071c08 07787706
    0a0b102b 07787706 0a071c08 02c102ff ff0180ff 02c10702 c1020180 3c021204
    c30e3d0e 04c30e3d 0e018004 41424344 02c102ff 02c10307 7877060a 05051507
    7877060a 05051507 7877060a 05051502 c102ffff 0180ff02 c10702c1 0201803c
    011204c3 0e3d0804 c30e3d09 01800642 4c4f434b 3302c102 ff02c103 07787706
    060a3927 07787706 060a3a37 07787706 060a3927 02c102ff ff0180ff 02c10702

    <32 bytes per line>

    BBED> sum apply
    Check value for File 1, Block 82466:
    current = 0xa9ac, required = 0xa9ac

    BBED> verify
    DBVERIFY - Verification starting
    FILE = /u01/app/oradata/QXY/system01.dbf
    BLOCK = 82466

    DBVERIFY - Verification complete

    Total Blocks Examined : 1
    Total Blocks Processed (Data) : 1
    Total Blocks Failing (Data) : 0
    Total Blocks Processed (Index): 0
    Total Blocks Failing (Index): 0
    Total Blocks Empty : 0
    Total Blocks Marked Corrupt : 0
    Total Blocks Influx : 0
    Message 531 not found; product=RDBMS; facility=BBED

    BBED>

    SQL> alter system flush buffer_cache;

    System altered.

    SQL> alter system flush shared_pool;

    System altered.

    SQL> select obj#,DATAOBJ#,NAME from obj$ where obj#=136026;

      OBJ#   DATAOBJ# NAME

    136026     136026 PYGC

    SQL> select object_id, data_object_id, object_name from dba_objects where object_name='PYGC';

    OBJECT_ID DATA_OBJECT_ID OBJECT_NAME


    136026         136026      PYGC

    SQL> select count() from pygc; <====但是这个时候查询会报ORA-08103的错误,如下:
    select count(
    ) from pygc
    *
    ERROR at line 1:
    ORA-08103: object no longer exists

    SQL>
    SQL>
    8、修改tab$

    表结构如下:

    SQL>
    SQL> desc tab$
    Name Null? Type


    OBJ# NOT NULL NUMBER
    DATAOBJ# NUMBER
    TS# NOT NULL NUMBER
    FILE# NOT NULL NUMBER
    BLOCK# NOT NULL NUMBER
    BOBJ# NUMBER
    TAB# NUMBER
    COLS NOT NULL NUMBER
    CLUCOLS NUMBER
    PCTFREE$ NOT NULL NUMBER
    PCTUSED$ NOT NULL NUMBER
    INITRANS NOT NULL NUMBER
    MAXTRANS NOT NULL NUMBER
    FLAGS NOT NULL NUMBER
    AUDIT$ NOT NULL VARCHAR2(38)
    ROWCNT NUMBER
    BLKCNT NUMBER
    EMPCNT NUMBER
    AVGSPC NUMBER
    CHNCNT NUMBER
    AVGRLN NUMBER
    AVGSPC_FLB NUMBER
    FLBCNT NUMBER
    ANALYZETIME DATE
    SAMPLESIZE NUMBER
    DEGREE NUMBER
    INSTANCES NUMBER
    INTCOLS NOT NULL NUMBER
    KERNELCOLS NOT NULL NUMBER
    PROPERTY NOT NULL NUMBER
    TRIGFLAG NUMBER
    SPARE1 NUMBER
    SPARE2 NUMBER
    SPARE3 NUMBER
    SPARE4 VARCHAR2(1000)
    SPARE5 VARCHAR2(1000)
    SPARE6 DATE

    SQL>
    查询对象dataobj#=136027在tab$表中的位置

    SQL>
    SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid), dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from tab$ where obj#=136026;

    DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)


                                   1                                90285

    SQL>
    SQL> alter system dump datafile 1 block 90285;

    System altered.

    SQL> select value from v$diag_info where name = 'Default Trace File';

    VALUE

    /u01/app/diag/rdbms/qxy/QXY/trace/QXY_ora_5076.trc

    SQL>

    tab 1, row 1, @0x1a25
    tl: 123 fb: -CH-FL-- lb: 0x2 cc: 36 cki: 1
    col 0: [ 4] c3 0e 3d 1c <======DATAOBJ# 转换成数字 136027
    col 1: [ 2] c1 12
    col 2: [ 2] c1 10
    col 3: [ 3] c2 02 1f
    col 4: NULL
    col 5: NULL
    col 6: [ 2] c1 02
    col 7: NULL
    col 8: [ 2] c1 0b
    col 9: [ 2] c1 29
    col 10: [ 2] c1 02
    col 11: [ 3] c2 03 38
    col 12: [ 2] c1 02
    col 13: [38]
    2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d
    2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d
    col 14: NULL
    col 15: NULL
    col 16: NULL
    col 17: NULL
    col 18: NULL
    col 19: NULL
    col 20: NULL
    col 21: NULL
    col 22: NULL
    col 23: NULL
    col 24: NULL
    col 25: NULL
    col 26: [ 2] c1 02
    col 27: [ 2] c1 02
    col 28: [ 6] c5 06 25 58 0a 0d
    col 29: [ 1] 80
    col 30: [ 3] c2 08 25
    col 31: NULL
    col 32: NULL
    col 33: NULL
    col 34: NULL
    col 35: [ 7] 78 77 06 0a 08 24 24
    8、使用bbed把136027 修改成136026, 也就是把c3 0e 3d 1c 修改成c3 0e 3d 1b

    BBED> d
    File: /u01/app/oradata/QXY/system01.dbf (1)
    Block: 90285 Offsets: 6790 to 7301 Dba:0x004160ad

    c30e3d1c 02c11202 c11003c2 021fffff 02c102ff 02c10b02 c12902c1 0203c203
    3802c102 262d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d
    2d2d2d2d 2d2d2d2d 2d2d2dff ffffffff ffffffff ffffff02 c10202c1 0206c506
    25580a0d 018003c2 0825ffff ffff0778 77060a08 2424ac00 01020002 00004160
    ad000100 4160ad00 0104c30e 3d1b6c00 140002c1 1602c116 02c10801 80065350
    41524536 02c10d02 c1080180 ffff0180 ffff02c1 16018001 80018001 80018001
    806c0014 0002c115 02c11502 c20b0180 06535041 52453502 c10202c2 0b0180ff
    ff0180ff ff02c115 018003c2 093502c1 02018001 8002c20b 6c001400 02c11402
    c11402c2 0b018006 53504152 453402c1 0202c20b 0180ffff 0180ffff 02c11401
    8003c209 3502c102 01800180 02c20b6c 00140002 c11302c1 1302c117 01800653
    50415245 3302c103 02c11701 80ffff01 80ffff02 c1130180 01800180 01800180
    01806c00 140002c1 1202c112 02c11701 80065350 41524532 02c10302 c1170180
    ffff0180 ffff02c1 12018001 80018001 80018001 806c0014 0002c111 02c11102
    c1170180 06535041 52453102 c10302c1 170180ff ff0180ff ff02c111 01800180
    01800180 01800180 6c001400 02c11002 c11002c1 11018004 4f494424 02c11802
    c1110180 ffff0180 ffff02c1 10018001 80018001 80018001 806c0014 0002c10f

    <32 bytes per line>

    BBED> set offset +3
    OFFSET 6793

    BBED> m /x 1b
    Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
    File: /u01/app/oradata/QXY/system01.dbf (1)
    Block: 90285 Offsets: 6793 to 7304 Dba:0x004160ad

    1b02c112 02c11003 c2021fff ff02c102 ff02c10b 02c12902 c10203c2 033802c1
    02262d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d
    2d2d2d2d 2d2d2d2d ffffffff ffffffff ffffffff 02c10202 c10206c5 0625580a
    0d018003 c20825ff ffffff07 7877060a 082424ac 00010200 02000041 60ad0001
    004160ad 000104c3 0e3d1b6c 00140002 c11602c1 1602c108 01800653 50415245
    3602c10d 02c10801 80ffff01 80ffff02 c1160180 01800180 01800180 01806c00
    140002c1 1502c115 02c20b01 80065350 41524535 02c10202 c20b0180 ffff0180
    ffff02c1 15018003 c2093502 c1020180 018002c2 0b6c0014 0002c114 02c11402
    c20b0180 06535041 52453402 c10202c2 0b0180ff ff0180ff ff02c114 018003c2
    093502c1 02018001 8002c20b 6c001400 02c11302 c11302c1 17018006 53504152
    453302c1 0302c117 0180ffff 0180ffff 02c11301 80018001 80018001 8001806c
    00140002 c11202c1 1202c117 01800653 50415245 3202c103 02c11701 80ffff01
    80ffff02 c1120180 01800180 01800180 01806c00 140002c1 1102c111 02c11701
    80065350 41524531 02c10302 c1170180 ffff0180 ffff02c1 11018001 80018001
    80018001 806c0014 0002c110 02c11002 c1110180 044f4944 2402c118 02c11101
    80ffff01 80ffff02 c1100180 01800180 01800180 01806c00 140002c1 0f02c10f

    <32 bytes per line>

    BBED>
    BBED>
    BBED> sum apply;
    Check value for File 1, Block 90285:
    current = 0xb3b5, required = 0xb3b5

    BBED>
    再次查询dba_object视图

    SQL> select obj#, dataobj# from tab$ where obj#=136026;

      OBJ#   DATAOBJ#

    136026     136027

    SQL> alter system flush buffer_cache;

    System altered.

    SQL> alter system flush shared_pool;

    System altered.

    SQL> select obj#, dataobj# from tab$ where obj#=136026;

      OBJ#   DATAOBJ#

    136026     136026

    SQL>
    可以看到dataobj#的值已经从原来的136027变成了136026。但是查询PYGC还是会报ORA-08103的错误。

    SQL> alter system flush buffer_cache;

    System altered.

    SQL> alter system flush shared_pool;

    System altered.

    SQL> select obj#, dataobj# from tab$ where obj#=136026;

      OBJ#   DATAOBJ#

    136026     136026

    SQL>

    SQL> select from pygc;
    select
    from pygc
    *
    ERROR at line 1:
    ORA-08103: object no longer exists
    9、修改段头块中的object_id的值,把136027改为136026

    修改pygc表的段头信息,在偏移量272的位置是objectid
    SQL> col segment_name for a20
    SQL> select SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name = 'PYGC';

    SEGMENT_NAME HEADER_FILE HEADER_BLOCK


    PYGC 15 130

    BBED> set dba 15,130
    DBA 0x03c00082 (62914690 15,130)

    BBED> set offset 272
    OFFSET 272

    BBED> d
    File: /u01/app/oradata/QXY/py.dbf (15)
    Block: 130 Offsets: 272 to 783 Dba:0x03c00082

    5b130200 00000010 8000c003 08000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    136026转换成10进制是2135A

    SQL> select to_char(136026,'xxxxxxxx') from dual;

    TO_CHAR(1

    2135a

    SQL>

    现在只需把5b改成5a,也就是把136027改成136026

    BBED> m /x 5a
    Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
    File: /u01/app/oradata/QXY/py.dbf (15)
    Block: 130 Offsets: 272 to 783 Dba:0x03c00082

    5a130200 00000010 8000c003 08000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
    00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

    <32 bytes per line>

    BBED> sum apply;
    Check value for File 15, Block 130:
    current = 0xbaf6, required = 0xbaf6

    BBED>

    SQL> alter system flush shared_pool;

    System altered.

    SQL> select * from pygc; <=======查询已经没有报错,但是查询结果没有值。

    no rows selected

    SQL>
    可以看到,这个时候查询PYGC已经不报错了,但是没有值,没有值的原因是段头块中的高水位被修改了。表的数据被屏蔽在了高水位之上,所以查询表得不到任何数据,就把高水位改成truncate之前的。

    truncate前后高水位的信息

    truncate之前的高水位
    Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
    last map 0x00000000 #maps: 0 offset: 2716
    Highwater:: 0x03c00088 ext#: 0 blk#: 8 ext size: 8
    #blocks in seg. hdr's freelists: 0
    #blocks below: 5
    mapblk 0x00000000 offset: 0
    Unlocked

    Low HighWater Mark :
    Highwater:: 0x03c00088 ext#: 0 blk#: 8 ext size: 8
    #blocks in seg. hdr's freelists: 0
    #blocks below: 5
    mapblk 0x00000000 offset: 0
    Level 1 BMB for High HWM block: 0x03c00080
    Level 1 BMB for Low HWM block: 0x03c00080

    truncate之后的高水位信息
    Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
    last map 0x00000000 #maps: 0 offset: 2716
    Highwater:: 0x03c00083 ext#: 0 blk#: 3 ext size: 8
    #blocks in seg. hdr's freelists: 0
    #blocks below: 0
    mapblk 0x00000000 offset: 0
    Disk Lock:: Locked by xid: 0x001b.014.000036cd

    Low HighWater Mark :
    Highwater:: 0x03c00083 ext#: 0 blk#: 3 ext size: 8
    #blocks in seg. hdr's freelists: 0
    #blocks below: 0
    mapblk 0x00000000 offset: 0
    Level 1 BMB for High HWM block: 0x03c00080
    Level 1 BMB for Low HWM block: 0x03c00080

    可以看到truncate之前的高水位的地址为0x03c00088,并且高水位在0号区的第8个块的位置。
    truncate之后高水位的地址为0x03c00083,并且高水位在0号区的第3个块上面。
        这里是因为truncate前后都dump了段头块的信息,现实中根本不会有这样的机会的。 但是可以通过redo来查看truncate前后的信息,方法如下:

    SQL> alter system dump logfile '/u01/app/oradata/QXY/redo02.log';

    System altered.

    SQL> select value from v$diag_info where name = 'Default Trace File';

    VALUE

    /u01/app/diag/rdbms/qxy/QXY/trace/QXY_ora_5936.trc

    SQL>

    REDO RECORD - Thread:1 RBA: 0x00015d.0000000f.0110 LEN: 0x01b8 VLD: 0x01
    SCN: 0x0000.0225a89c SUBSCN: 6 06/10/2019 15:35:36
    CHANGE #1 TYP:0 CLS:70 AFN:19 DBA:0x04c01287 OBJ:4294967295 SCN:0x0000.0225a89c SEQ:2 OP:5.1 ENC:0 RBL:0
    ktudb redo: siz: 164 spc: 7536 flg: 0x0022 seq: 0x0611 rec: 0x06
    xid: 0x001b.014.000036cd
    ktubu redo: slt: 20 rci: 5 opc: 13.29 objn: 136026 objd: 136026 tsn: 17
    Undo type: Regular undo Undo type: Last buffer split: No
    Tablespace Undo: No
    0x00000000
    Segment Header Undo
    Seghdr dba: 0x03c00082 Mapblock dba: 0x00000000 Mapredo Offset: 4 scls: 4 mcls: 140668768878599
    Both the HWMs
    Low HWM
    Highwater:: 0x03c00088 ext#: 0 blk#: 8 ext size: 8 <=====高水位信息,包含地址、高水位所在的区、块、区包含的块
    #blocks in seg. hdr's freelists: 0
    #blocks below: 5 <=====高水位下面包含5个块
    mapblk 0x00000000 offset: 0
    lfdba: 0x03c00080
    High HWM
    Highwater:: 0x03c00088 ext#: 0 blk#: 8 ext size: 8
    #blocks in seg. hdr's freelists: 0
    #blocks below: 5
    mapblk 0x00000000 offset: 0
    lfdba: 0x03c00080 hint dba: 0x03c00081
    Lasts in Header
    LF: 62914688 LS: 62914689 LT: 0 FT: 0
    CHANGE #2 TYP:0 CLS:4 AFN:15 DBA:0x03c00082 OBJ:136026 SCN:0x0000.0225a89c SEQ:1 OP:13.28 ENC:0 RBL:0 <====truncate操作
    Both the HWMs
    Low HWM
    Highwater:: 0x03c00083 ext#: 0 blk#: 3 ext size: 8 <====truncate之后的高水位信息
    #blocks in seg. hdr's freelists: 0
    #blocks below: 0
    mapblk 0x00000000 offset: 0
    lfdba: 0x03c00080
    High HWM
    Highwater:: 0x03c00083 ext#: 0 blk#: 3 ext size: 8
    #blocks in seg. hdr's freelists: 0
    #blocks below: 0
    mapblk 0x00000000 offset: 0
    lfdba: 0x03c00080 hint dba: 0x03c00081
    Lasts in Header
    LF: 62914688 LS: 62914689 LT: 0 FT: 0
    CHANGE #3 TYP:0 CLS:8 AFN:15 DBA:0x03c00080 OBJ:136026 SCN:0x0000.0225a89c SEQ:2 OP:13.22 ENC:0 RBL:0
    Redo on Level1 Bitmap Block
    Redo to set hwm
    Opcode: 32 Highwater:: 0x03c00083 ext#: 0 blk#: 3 ext size: 8
    #blocks in seg. hdr's freelists: 0
    #blocks below: 0
    mapblk 0x00000000 offset: 0
    dump logfile得到的数据和dump 段头块的高水位数据一致。

    10、使用bbed修改段头块中的高水位信息

    BBED> set dba 15,130
    DBA 0x03c00082 (62914690 15,130)

    BBED> set offset 52
    OFFSET 52

    BBED> d /v count 16
    File: /u01/app/oradata/QXY/py.dbf (15)
    Block: 130 Offsets: 52 to 67 Dba:0x03c00082

    03000000 08000000 8300c003 00000000 l ................

    <16 bytes per line>

    BBED> m /x 08 <======把高水所在0号区的块从3改成8
    Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
    File: /u01/app/oradata/QXY/py.dbf (15)
    Block: 130 Offsets: 52 to 67 Dba:0x03c00082

    08000000 08000000 8300c003 00000000

    <32 bytes per line>

    BBED>

    BBED> d /v count 16
    File: /u01/app/oradata/QXY/py.dbf (15)
    Block: 130 Offsets: 60 to 75 Dba:0x03c00082

    8300c003 00000000 00000000 00000000 l ................

    <16 bytes per line>

    BBED> m /x 88 <======0x03c00083改成0x03c00088
    File: /u01/app/oradata/QXY/py.dbf (15)
    Block: 130 Offsets: 60 to 75 Dba:0x03c00082

    8800c003 00000000 00000000 00000000

    <32 bytes per line>

    BBED>

    <====同样的道理,修改Low HighWater Mark的值

    BBED> set offset 96
    OFFSET 96

    BBED> d
    File: /u01/app/oradata/QXY/py.dbf (15)
    Block: 130 Offsets: 96 to 111 Dba:0x03c00082

    03000000 08000000 8300c003 00000000

    <32 bytes per line>

    BBED> m /x 08
    File: /u01/app/oradata/QXY/py.dbf (15)
    Block: 130 Offsets: 96 to 111 Dba:0x03c00082

    08000000 08000000 8300c003 00000000

    <32 bytes per line>

    BBED> set offset +8
    OFFSET 104

    BBED> d
    File: /u01/app/oradata/QXY/py.dbf (15)
    Block: 130 Offsets: 104 to 119 Dba:0x03c00082

    8300c003 00000000 00000000 00000000

    <32 bytes per line>

    BBED> m /x 88
    File: /u01/app/oradata/QXY/py.dbf (15)
    Block: 130 Offsets: 104 to 119 Dba:0x03c00082

    8800c003 00000000 00000000 00000000

    <32 bytes per line>

    BBED> sum apply;
    Check value for File 15, Block 130:
    current = 0xbaf6, required = 0xbaf6

    BBED>
    再次查询表pygc的数据就可以看到被truncate之前的数据了

    SQL> alter system flush buffer_cache;

    System altered.

    SQL> alter system flush shared_pool;

    System altered.

    SQL> select * from pygc;

        ID

        10
        11

    SQL>
    但是现在有一个问题,就是pygc这个表无法drop和insert。如果该表需要继续使用,需要使用CTAS的方式创建一个新表。具体报错如下:

    SQL> drop table pygc;
    drop table pygc
    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 2
    ORA-08102: index key not found, obj# 39, file 1, block 62284 (2)

    SQL>
    SQL> insert into pygc select from pygc;
    insert into pygc select
    from pygc
    *
    ERROR at line 1:
    ORA-00600: internal error code, arguments: [ktspgfblk3:kcbz_objdchk], [0], [0],
    [1], [], [], [], [], [], [], [], []

    SQL>
    如果需要drop该表,需要把修改obj$、tab$中修改的数据改回去。然后就可以drop掉,这里就不在演示,已经测试可以drop。

    所以,表被truncate之后,表就会回收高水位,这样之前的数据就会被放到高水位之上,再次查询该表的时候就无记录返回。同时高水位之前的数据可以被其他需要分配块的操作占用。如果数据块被占用,则占用的数据是无法恢复的。上面的整个过程就是演示了truncate操作的原理。真正恢复数据的时候还是用DRT恢复工具恢复会方便很多,如果数据量很大,并且数据没有被覆盖,修改元数据会更快的恢复数据,但是这种方式存在风险,因为无法全部知道oracle更新了什么操作。 如果发现表被误操作,尽可能不要在操作DML,或者把表对应的表空间设置为read only模式,这样恢复数据的概率就会更高。

    展开全文
  • RESETLOG之后数据文件丢失的RMAN恢复 一、场景 在T0时间点做了RMAN全备,随后在T1时间点由于误操作将T0表truncate。在T2时间点通过RMAN的不完全恢复,将数据库恢复到T1时间点(truncate之前),resetlog之后没有做...
  • 关于通过Dataguard ...本文主要了解通过备库闪回操作来进行对主库drop/truncate/delete表的数据恢复步骤 解决方法 前提要求: 备库必须已经处于闪回数据库模式 被drop/truncate/delete表的发生时间应该在db_...
  •  delete删除一条数据 drop和truncate删除表格中数据  1.delete删除的解决方法(我就是使用delete误删除的)  原理:利用oracle提供的闪回方法,如果在删除数据后还没做大量的操作(只要保证被删除数据的块没被...
  • 本文从实际使用经验出发,介绍一款开源的MySQL数据库InnoDB数据恢复工具:innodb-tools,它通过从原始数据文件中提取表的行记录,实现从丢失的或者被毁坏的MySQL表中恢复数据。例如,当你不小心执行DROP TABLE、...
  • 在Oracle恢复案例中,也有xx企业erp该功能导致数据被删除请求恢复),导致MySQL数据库被直接drop database掉了,之前没有做任何备份,只是发生故障之后,他们立即封存现场,备份出来了ibdata1文件。接到请求之后,通过...
  • 例如,当你不小心执行DROP TABLE、TRUNCATE TABLE或者DROP DATABASE之后,可以通过以下方式恢复数据。以下内容大部分参考自:Percona Data Recovery Tool for InnoDB,文档是英文的,而且写的比较晦涩,这里是个人的...
  • truncate与delete的区别

    2017-09-13 08:52:33
    truncate删除表中的所有数据,删除之后数据不可以恢复。 delete删除速度比truncate慢,删除数据可以恢复,后跟where时可以指定删除某一条数据。
  • 本文从实际使用经验出发,介绍一款开源的MySQL数据库InnoDB数据恢复工具:innodb-tools,它通过从原始数据文件中提取表的行记录,实现从丢失的或者被毁坏的MySQL表中恢复数据。例如,当你不小心执行DROP TABLE、...
  • MySQL数据库InnoDB数据恢复工具使用总结本文从实际使用经验出发,介绍一款开源的MySQL数据库InnoDB数据恢复工具:innodb-tools,它通过从原始数据文件中提取表的行 记录,实现从丢失的或者被毁坏的MySQL表中恢复数据...
  • truncate与delete的区别  truncate table命令将快速删除数据表中的所有记录,但保留数据表结构。这种快速删除与delete from 数据表的删除全部数据表记录不一样; delete命令删除的数据将存储在系统...2.TRUNCATE之后
  • Delete与truncate的区别

    千次阅读 2014-03-19 19:03:11
    Delete table_name 删除"表格记录"会把操作记录在日志中,可以通过事务回滚来恢复删除的数据。  truncate table table_name删除"表格记录"不可恢复 。  delete 语句是数据库操作语言(dml),这个操作会放到...
  • oracle恢复删除数据.txt

    2019-09-27 17:38:07
    oracle数据被误删,并且commit之后,对误删的表记录,只要没有truncate语句,就可以根据事务的提交时间进行选择恢复
  • ORA-1410 invalid rows错误是与ORA-... 解决方案之一: 可以通过如下PL/SQL过程将健康数据复制到新建表中,对于问题数据块中的数据将被跳过,对于能够容忍数据丢失的场景可以考虑这样恢复之后truncate 原表/分区...
  • 我们都知道当Truncate命令发起之后,Oracle实际上并没有在删除底层数据块上的数据,而是要等到重用的时候才会把这一部分数据回收,于是这给了我们一个能够恢复数据库的机会。 二、知己知彼 对于Truncate单表来说,...
  • Oracle 恢复误删数据

    2017-08-29 10:27:02
    对于Oracle中,使用delete误删了数据,使用以下语法进行恢复(注:必须是使用delete删除的才可以,使用truncate和drop的无法恢复) alter table table_Name enable row movement ; --行迁移,意思就是,一个现存...

空空如也

空空如也

1 2 3 4 5 ... 7
收藏数 126
精华内容 50
关键字:

truncate之后数据恢复