精华内容
下载资源
问答
  • select * from table_name as of timestamp to_timestamp('2018-12-20 00:00:00', 'yyyy-mm-dd hh24:mi:ss'); --选择恢复的时间 转载于:https://www.cnblogs.com/dewu123/p/10962291.html

    select * from table_name as of timestamp to_timestamp('2018-12-20 00:00:00', 'yyyy-mm-dd hh24:mi:ss');  --选择恢复的时间

    转载于:https://www.cnblogs.com/dewu123/p/10962291.html

    展开全文
  • oracle drop/truncate table 恢复

    千次阅读 2016-02-24 10:52:18
     drop table 并且回收站已经被情况了,如何恢复? 前提:数据库开规档,并且删除之前的归档没有被删掉...2. 利用备份加archivelog 进行不完全恢复(该方法同样适用于truncate恢复) ++++Session 1 SQL>
    
    drop table 并且回收站已经被情况了,如何恢复?
    前提:数据库开规档,并且删除之前的归档没有被删掉。
    思路:rman备份、创建pfile、 创建一个辅助实例恢复之后,再导入到原来实例;

    1.  如果开了闪回,可闪回

    2.  利用备份加archivelog 进行不完全恢复(该方法同样适用于truncate的恢复)

    ++++Session 1

    SQL> conn zw/zw 
    Connected.
    SQL> create table t1 as select * from dba_tables;

    Table created.

    SQL> select count(*) from t1;

      COUNT(*)
    ----------
          1204


    +++++Session 2

    run
    {
    allocate channel c1 type disk;
    allocate channel c2 type disk;
    backup  database format '/oradata/backup/full_%d_%T_%s_%p';
    sql 'alter system archive log current';
    sql 'alter system archive log current';
    sql 'alter system archive log current';
    backup archivelog all format '/oradata/backup/arch_%d_%T_%s_%p';
    backup current controlfile format '/oradata/backup/ctl_%d_%T_%s_%p';
    }

    +++++drop table

    SQL> show user
    USER is "ZW"
    SQL> alter system switch logfile;

    System altered.

    SQL> drop table t1 purge;

    Table dropped.

    SQL> alter system checkpoint;

    System altered.

    3.创建一个pfile

    SQL> conn /as sysdba
    Connected.
    SQL> create pfile='/tmp/zw.ora' from spfile;

    File created.

    SQL>



    4. 修改pfile

    node1new.__db_cache_size=335544320
    node1new.__java_pool_size=4194304
    node1new.__large_pool_size=4194304
    node1new.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
    node1new.__pga_aggregate_target=339738624
    node1new.__sga_target=503316480
    node1new.__shared_io_pool_size=0
    node1new.__shared_pool_size=150994944
    node1new.__streams_pool_size=0
    *.audit_file_dest='/u01/app/oracle/admin/node1new/adump'
    *.audit_trail='db'
    *.compatible='11.2.0.4.0'
    *.control_files='/oradata/node1new/control01.ctl','/u01/app/oracle/fast_recovery_area/node1new/control02.ctl'
    *.db_block_size=8192
    *.db_domain=''
    *.db_file_name_convert='/oradata/node1new','/oradata/node2'
    *.db_name='node1'
    *.db_unique_name='node1new'
    *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
    *.db_recovery_file_dest_size=5218762752
    *.diagnostic_dest='/u01/app/oracle'
    *.java_pool_size=0
    *.log_archive_dest_1='location=/oradata/arch1'
    *.memory_target=842006528
    *.open_cursors=300
    *.processes=150
    *.remote_login_passwordfile='EXCLUSIVE'
    *.undo_tablespace='UNDOTBS1'



    5. 创建各种dump目录
    -----------------------------------------------------
    [oracle@node1 tmp]$ export ORACLE_SID=node1new
    [oracle@node1 tmp]$ echo $ORACLE_SID
    node1new

    11g要创建这些目录

    rm -rf $ORACLE_BASE/admin/$ORACLE_SID
    mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
    mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/dpdump
    mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/pfile
    mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/scripts
    chmod -R 750 $ORACLE_BASE/admin

    rm -rf $ORACLE_BASE/diag/rdbms/$ORACLE_SID
    mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/alert 
    mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/cdump 
    mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/hm    
    mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/incident
    mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/incpkg
    mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/ir    
    mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/lck   
    mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/metadata
    mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/stage 
    mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/sweep 
    mkdir -p $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace
    chmod -R 750 $ORACLE_BASE/diag/rdbms/$ORACLE_SID 
        
        

    6.恢复controlfile
    RMAN> startup nomount pfile='/tmp/pfile.ora';

    Oracle instance started

    Total System Global Area     167772160 bytes

    Fixed Size                     1272600 bytes
    Variable Size                 62915816 bytes
    Database Buffers             100663296 bytes
    Redo Buffers                   2920448 bytes

    RMAN> restore controlfile from '/oradata/backup/ctl_NODE1_20160123_18_1';

    Starting restore at 23-JAN-16
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=1 device type=DISK

    channel ORA_DISK_1: restoring control file
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
    output file name=/oradata/node1new/control01.ctl
    output file name=/u01/app/oracle/fast_recovery_area/node1new/control02.ctl
    Finished restore at 23-JAN-16

    SQL> alter database mount;
    Database altered.


    ---restore datafile
    RMAN>
    run
    {
    set newname for datafile '/oradata/node1/system01.dbf'  to '/oradata/node1new/system01.dbf';
    set newname for datafile '/oradata/node1/sysaux01.dbf'  to '/oradata/node1new/sysaux01.dbf';
    set newname for datafile '/oradata/node1/undotbs01.dbf' to '/oradata/node1new/undotbs01.dbf';
    set newname for datafile '/oradata/node1/users01.dbf'   to '/oradata/node1new/users01.dbf';
    restore database ;
    switch datafile all;
    }

    executing command: SET NEWNAME
    executing command: SET NEWNAME
    executing command: SET NEWNAME
    executing command: SET NEWNAME

    Starting restore at 23-JAN-16
    Starting implicit crosscheck backup at 23-JAN-16
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=19 device type=DISK
    Crosschecked 7 objects
    Finished implicit crosscheck backup at 23-JAN-16

    Starting implicit crosscheck copy at 23-JAN-16
    using channel ORA_DISK_1
    Finished implicit crosscheck copy at 23-JAN-16

    searching for all files in the recovery area
    cataloging files...
    no files cataloged

    using channel ORA_DISK_1

    channel ORA_DISK_1: starting datafile backup set restore
    channel ORA_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_DISK_1: restoring datafile 00002 to /oradata/node1new/sysaux01.dbf
    channel ORA_DISK_1: restoring datafile 00004 to /oradata/node1new/users01.dbf
    channel ORA_DISK_1: reading from backup piece /oradata/backup/full_NODE1_20160123_11_1
    channel ORA_DISK_1: piece handle=/oradata/backup/full_NODE1_20160123_11_1 tag=TAG20160123T140220
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
    channel ORA_DISK_1: starting datafile backup set restore
    channel ORA_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_DISK_1: restoring datafile 00001 to /oradata/node1new/system01.dbf
    channel ORA_DISK_1: restoring datafile 00003 to /oradata/node1new/undotbs01.dbf
    channel ORA_DISK_1: reading from backup piece /oradata/backup/full_NODE1_20160123_10_1
    channel ORA_DISK_1: piece handle=/oradata/backup/full_NODE1_20160123_10_1 tag=TAG20160123T140220
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
    Finished restore at 23-JAN-16

    datafile 1 switched to datafile copy
    input datafile copy RECID=5 STAMP=901898007 file name=/oradata/node1new/system01.dbf
    datafile 2 switched to datafile copy
    input datafile copy RECID=6 STAMP=901898007 file name=/oradata/node1new/sysaux01.dbf
    datafile 3 switched to datafile copy
    input datafile copy RECID=7 STAMP=901898007 file name=/oradata/node1new/undotbs01.dbf
    datafile 4 switched to datafile copy
    input datafile copy RECID=8 STAMP=901898007 file name=/oradata/node1new/users01.dbf



    7.拷贝归档,恢复到最新的时间点

    [oracle@node1 arch]$ cp *.dbf  /oradata/arch1/
    [oracle@node1 node1new]$ env|grep SID
    ORACLE_SID=node1new
    [ora10g@killdb ~]$ rman target /

    Recovery Manager: Release 10.2.0.5.0 - Production on Tue Aug 6 00:40:26 2013

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

    connected to target database: ROGER (DBID=2525832133, not open)

    8.注册归档日志
    RMAN> catalog start with '/oradata/arch1';

    using target database control file instead of recovery catalog
    searching for all files that match the pattern /oradata/arch1

    List of Files Unknown to the Database
    =====================================
    File Name: /oradata/arch1/1_27_901846980.dbf
    File Name: /oradata/arch1/1_29_901846980.dbf
    File Name: /oradata/arch1/1_37_901846980.dbf
    File Name: /oradata/arch1/1_16_901846980.dbf
    File Name: /oradata/arch1/1_32_901846980.dbf
    File Name: /oradata/arch1/1_26_901846980.dbf
    File Name: /oradata/arch1/1_24_901846980.dbf
    File Name: /oradata/arch1/1_25_901846980.dbf
    File Name: /oradata/arch1/1_33_901846980.dbf
    File Name: /oradata/arch1/1_38_901846980.dbf
    File Name: /oradata/arch1/1_23_901846980.dbf
    File Name: /oradata/arch1/1_20_901846980.dbf
    File Name: /oradata/arch1/1_31_901846980.dbf
    File Name: /oradata/arch1/1_30_901846980.dbf
    File Name: /oradata/arch1/1_21_901846980.dbf
    File Name: /oradata/arch1/1_34_901846980.dbf
    File Name: /oradata/arch1/1_36_901846980.dbf
    File Name: /oradata/arch1/1_22_901846980.dbf
    File Name: /oradata/arch1/1_28_901846980.dbf
    File Name: /oradata/arch1/1_18_901846980.dbf
    File Name: /oradata/arch1/1_35_901846980.dbf
    File Name: /oradata/arch1/1_19_901846980.dbf
    File Name: /oradata/arch1/1_17_901846980.dbf

    Do you really want to catalog the above files (enter YES or NO)? yes
    cataloging files...
    cataloging done

    List of Cataloged Files
    =======================
    File Name: /oradata/arch1/1_27_901846980.dbf
    File Name: /oradata/arch1/1_29_901846980.dbf
    File Name: /oradata/arch1/1_37_901846980.dbf
    File Name: /oradata/arch1/1_16_901846980.dbf
    File Name: /oradata/arch1/1_32_901846980.dbf
    File Name: /oradata/arch1/1_26_901846980.dbf
    File Name: /oradata/arch1/1_24_901846980.dbf
    File Name: /oradata/arch1/1_25_901846980.dbf
    File Name: /oradata/arch1/1_33_901846980.dbf
    File Name: /oradata/arch1/1_38_901846980.dbf
    File Name: /oradata/arch1/1_23_901846980.dbf
    File Name: /oradata/arch1/1_20_901846980.dbf
    File Name: /oradata/arch1/1_31_901846980.dbf
    File Name: /oradata/arch1/1_30_901846980.dbf
    File Name: /oradata/arch1/1_21_901846980.dbf
    File Name: /oradata/arch1/1_34_901846980.dbf
    File Name: /oradata/arch1/1_36_901846980.dbf
    File Name: /oradata/arch1/1_22_901846980.dbf
    File Name: /oradata/arch1/1_28_901846980.dbf
    File Name: /oradata/arch1/1_18_901846980.dbf
    File Name: /oradata/arch1/1_35_901846980.dbf
    File Name: /oradata/arch1/1_19_901846980.dbf
    File Name: /oradata/arch1/1_17_901846980.dbf


    9.怎么找到这个点?

    col SEQUENCE# format a40;
    col name format a70;
    SQL> col first_change# clear;
    SQL> col next_change# clear;
    SQL> select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,name from v$archived_log where name like '/oradata/arch%' order by 2;

    SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# NAME
    -----------------------------------------------------------------
         16       215824       215997 /oradata/arch/1_16_901846980.dbf
         17       215997       216209 /oradata/arch/1_17_901846980.dbf
         18       216209       216218 /oradata/arch/1_18_901846980.dbf
         19       216218       216227 /oradata/arch/1_19_901846980.dbf
         20       216227       216235 /oradata/arch/1_20_901846980.dbf
         21       216235       216833 /oradata/arch/1_21_901846980.dbf
         22       216833       216930 /oradata/arch/1_22_901846980.dbf
         23       216930       225589 /oradata/arch/1_23_901846980.dbf
         24       225589       226527 /oradata/arch/1_24_901846980.dbf
         25       226527       226530 /oradata/arch/1_25_901846980.dbf
         26       226530       226533 /oradata/arch/1_26_901846980.dbf
         27       226533       226536 /oradata/arch/1_27_901846980.dbf
         28       226536       226539 /oradata/arch/1_28_901846980.dbf
         29       226539       226542 /oradata/arch/1_29_901846980.dbf
         30       226542       226562 /oradata/arch/1_30_901846980.dbf
         31       226562       226860 /oradata/arch/1_31_901846980.dbf
         32       226860       226881 /oradata/arch/1_32_901846980.dbf
         33       226881       249787 /oradata/arch/1_33_901846980.dbf
         34       249787       249883 /oradata/arch/1_34_901846980.dbf
         35       249883       249892 /oradata/arch/1_35_901846980.dbf
         36       249892       249901 /oradata/arch/1_36_901846980.dbf
         37       249901       249909 /oradata/arch/1_37_901846980.dbf
         38       249909       249939 /oradata/arch/1_38_901846980.dbf
        
    SQL>

    RMAN>  run {
    set until scn 249939;
    sql 'alter database datafile 1,2,3,4 online';
    recover database skip forever tablespace users01;
    }
    recovery过程中可以临时将某个暂无法恢复出来的tablespace skip掉,先恢复其它部分,
    待recovery database完成并open database后,再recover这个被skip掉的表空间;

    executing command: SET until clause

    sql statement: alter database datafile 1,2,3,4 online

    Starting recover at 23-JAN-16
    using channel ORA_DISK_1

    starting media recovery

    archived log for thread 1 with sequence 34 is already on disk as file /oradata/arch1/1_34_901846980.dbf
    archived log for thread 1 with sequence 35 is already on disk as file /oradata/arch1/1_35_901846980.dbf
    archived log for thread 1 with sequence 36 is already on disk as file /oradata/arch1/1_36_901846980.dbf
    archived log for thread 1 with sequence 37 is already on disk as file /oradata/node1/redo01.log
    archived log for thread 1 with sequence 38 is already on disk as file /oradata/node1/redo02.log
    archived log file name=/oradata/arch1/1_34_901846980.dbf thread=1 sequence=34
    archived log file name=/oradata/arch1/1_35_901846980.dbf thread=1 sequence=35
    archived log file name=/oradata/arch1/1_36_901846980.dbf thread=1 sequence=36
    archived log file name=/oradata/node1/redo01.log thread=1 sequence=37
    archived log file name=/oradata/node1/redo02.log thread=1 sequence=38
    media recovery complete, elapsed time: 00:00:00
    Finished recover at 23-JAN-16



    10. 查看logfile路径
    SQL> select member from v$logfile;

    MEMBER
    --------------------------------------------------------------------------------
    /oradata/node1/redo01.log
    /oradata/node1/redo02.log
    /oradata/node1/redo03.log


    11.修改logfile路径

    SQL> alter database rename file '/oradata/node1/redo01.log' to '/oradata/node1new/redo01.log';

    Database altered.

    SQL> alter database rename file '/oradata/node1/redo02.log' to '/oradata/node1new/redo02.log';

    Database altered.

    SQL> alter database rename file '/oradata/node1/redo03.log' to '/oradata/node1new/redo03.log';

    Database altered.

    12.查看并修改temp的路径

    SQL> select name from v$tempfile;

    NAME
    -----------------------------------
    /oradata/node1/temp01.dbf

    SQL> alter database rename file '/oradata/node1/temp01.dbf' to '/oradata/node1new/temp01.dbf';

    Database altered.

    13.打开数据库

    SQL> alter database open read only;

    Database altered.

    SQL> conn zw/zw
    Connected.
    SQL> select count(*) from t1;

      COUNT(*)
    ----------
          1204

    SQL> exit
    可以看到t1表已经恢复出来了

    14.导出恢复出来的表数据
    [oracle@node1 /]$ exp zw/zw file=/home/oracle/exp_t1.dmp tables=t1 direct=y



    15. 查看之前实例的表,可以看到没有t1表
    SQL> select count(*) from t1;
    select count(*) from t1
                         *
    ERROR at line 1:
    ORA-00942: table or view does not exist

    16.导入数据
    [oracle@node1 arch1]$ imp  zw/zw file=/home/oracle/exp_t1.dmp tables=t1;


    SQL> conn zw/zw
    Connected.
    SQL> select count(*) from t1;

      COUNT(*)
    ----------
          1204

    到此为止drop的表已经恢复成功!
    展开全文
  • oracle truncate恢复

    2009-07-03 12:58:45
    使用ODU恢复Truncate表ODUmanual ODU3月 15th, 2009 意外Truncate表的事情时有发生,ODU提供了方便的恢复Truncate表的功能。被Truncate的表,只要原来的空间没有被重用(即数据被覆盖),则数据都是可以恢复的。 ...
  • 环境CentOS Linux release 7.6.1810MySQL 5.7.27-log row模式这个方法只是执行指定时间段内的binlog恢复出单表或多表,若binlog完整未被删除过则追回所有,若被删除过,则只获得已有binlog内的数据实际是将...

    环境

    CentOS Linux release 7.6.1810

    MySQL 5.7.27-log row模式

    这个方法只是执行指定时间段内的binlog恢复出单表或多表,若binlog完整未被删除过则可追回所有,若被删除过,则只可获得已有binlog内的数据

    实际是将binlog重执行一次,将结果重新写入到指定的新库中去存在create database 是否会被重新执行【 执行会报错 Can't create database 】

    rewrite-db

    mysqlbinlog --skip-gtids --rewrite-db='ytest->re_ytest' --start-position=2538 --stop-position=3112 bin.000004|mysql re_ytest

    rewrite-db 会将指定的binlog执行到另一个指定的库中去

    示例

    创建测试数据

    -- 测试表与数据

    create table tb_yq(id int auto_increment primary key ,va varchar(10),vb varchar(10));

    create table tb_mc(id int auto_increment primary key ,va varchar(10),vb varchar(10));

    create table tb_ps(id int auto_increment primary key ,va varchar(10),vb varchar(10));

    insert into tb_yq (va,vb)values ('1','1'),('2','2'),('3','3'),('4','4'),('5','5'),('6','6');

    insert into tb_mc (va,vb)values ('11','11'),('22','22'),('33','33'),('44','44'),('55','55'),('66','66');

    insert into tb_ps (va,vb)values ('111','111'),('222','222'),('333','333'),('444','444'),('555','555'),('666','666');

    update tb_yq set vb = '啦啦啦' where id = 5;

    update tb_yq set vb = '隔壁老苗' where id = 8;

    update tb_mc set vb = '隔壁老苗' where id = 6;

    -- 当前表 tb_yq 数据为

    mysql> select * from tb_yq;

    +----+------+-----------+

    | id | va | vb |

    +----+------+-----------+

    | 1 | 1 | 1 |

    | 2 | 2 | 2 |

    | 3 | 3 | 3 |

    | 4 | 4 | 4 |

    | 5 | 5 | 啦啦啦 |

    | 6 | 6 | 6 |

    +----+------+-----------+

    6 rows in set (0.00 sec)

    mysql> select * from tb_mc;

    +----+------+--------------+

    | id | va | vb |

    +----+------+--------------+

    | 1 | 11 | 11 |

    | 2 | 22 | 22 |

    | 3 | 33 | 33 |

    | 4 | 44 | 44 |

    | 5 | 55 | 55 |

    | 6 | 66 | 隔壁老苗 |

    +----+------+--------------+

    6 rows in set (0.33 sec)

    模拟手抖truncate了

    假设手抖truncate了

    truncate table tb_yq;

    truncate table tb_mc;

    模拟业务继续写入数据,注意这里由于表是设置的主键自增,truncate后写入的数据重新从1开始了,若主键与业务相关的话得注意后续的修正操作

    insert into tb_yq (va,vb)values ('7','7'),('8','8');

    insert into tb_mc (va,vb)values ('77','77'),('88','88');

    update tb_yq set vb = '隔壁老王' where id = 2;

    update tb_yq set vb = '隔壁老潘' where id = 7;

    mysql> select * from tb_yq;

    +----+------+--------------+

    | id | va | vb |

    +----+------+--------------+

    | 1 | 7 | 7 |

    | 2 | 8 | 隔壁老王 |

    +----+------+--------------+

    2 rows in set (0.00 sec)

    mysql> select * from tb_mc;

    +----+------+------+

    | id | va | vb |

    +----+------+------+

    | 1 | 77 | 77 |

    | 2 | 88 | 88 |

    +----+------+------+

    2 rows in set (0.00 sec)注意:主键从1开始了

    找到truncate发生的日志位置

    当前最新的是 bin.000007 ,执行以下 flush logs 刷新下日志文件

    mysql> show binary logs;

    +------------+-----------+

    | Log_name | File_size |

    +------------+-----------+

    | bin.000003 | 4075 |

    | bin.000004 | 3554 |

    | bin.000005 | 1468 |

    | bin.000006 | 4507 |

    | bin.000007 | 5910 |

    +------------+-----------+

    5 rows in set (0.00 sec)

    mysql> flush logs;

    Query OK, 0 rows affected (0.00 sec)

    查看truncate的语句pos点,可得知truncate操作在 4746 与 4937 时准备开始执行,这个binlog初始的pos是4,那我们要恢复执行的可以从 4-4746的范围

    mysql> show binlog events in 'bin.000007';

    bin.000007 | 4 | Format_desc | 10 | 123 | Server ver: 5.7.27-log, Binlog ver: 4 |

    | bin.000007 | 123 | Previous_gtids | 10 | 194 | a7776f71-c8be-11e9-838f-0050563bb195:1-51

    ...略

    | bin.000007 | 4389 | Gtid | 10 | 4454 | SET @@SESSION.GTID_NEXT= 'a7776f71-c8be-11e9-838f-0050563bb195:67' |

    | bin.000007 | 4454 | Query | 10 | 4527 | BEGIN |

    | bin.000007 | 4527 | Table_map | 10 | 4582 | table_id: 373 (ytest.tb_mc) |

    | bin.000007 | 4582 | Update_rows | 10 | 4650 | table_id: 373 flags: STMT_END_F |

    | bin.000007 | 4650 | Xid | 10 | 4681 | COMMIT /* xid=8543 */ |

    | bin.000007 | 4681 | Gtid | 10 | 4746 | SET @@SESSION.GTID_NEXT= 'a7776f71-c8be-11e9-838f-0050563bb195:68' |

    | bin.000007 | 4746 | Query | 10 | 4872 | use `ytest`; /* ApplicationName=DataGrip 2020.1 */ truncate table tb_yq |

    | bin.000007 | 4872 | Gtid | 10 | 4937 | SET @@SESSION.GTID_NEXT= 'a7776f71-c8be-11e9-838f-0050563bb195:69' |

    | bin.000007 | 4937 | Query | 10 | 5063 | use `ytest`; /* ApplicationName=DataGrip 2020.1 */ truncate table tb_mc |

    | bin.000007 | 5063 | Gtid | 10 | 5128 | SET @@SESSION.GTID_NEXT= 'a7776f71-c8be-11e9-838f-0050563bb195:70' |

    | bin.000007 | 5128 | Query | 10 | 5201 | BEGIN |

    | bin.000007 | 5201 | Table_map | 10 | 5256 | table_id: 376 (ytest.tb_yq) |

    | bin.000007 | 5256 | Write_rows | 10 | 5309 | table_id: 376 flags: STMT_END_F |

    | bin.000007 | 5309 | Xid | 10 | 5340 | COMMIT /* xid=9118 */

    ...略

    有时binlog太大,看起来太累,可以使用如下语句查看

    [root@initnode101 ~]# mysqlbinlog -v --base64-output=decode-rows --start-datetime='2020-10-12 09:00:00' --stop-datetime='2020-10-12 10:00:00' /data/mysql_data/bin.000007|grep -i -B 10 truncate

    ### @3='隔壁老苗'

    # at 4650

    #201012 9:46:08 server id 10 end_log_pos 4681 CRC32 0x0c5becf4 Xid = 8543

    COMMIT/*!*/;

    # at 4681

    #201012 9:46:23 server id 10 end_log_pos 4746 CRC32 0xd141756e GTID last_committed=16 sequence_number=17 rbr_only=no

    SET @@SESSION.GTID_NEXT= 'a7776f71-c8be-11e9-838f-0050563bb195:68'/*!*/;

    # at 4746

    #201012 9:46:23 server id 10 end_log_pos 4872 CRC32 0xe21acbb5 Query thread_id=79 exec_time=0 error_code=0

    SET TIMESTAMP=1602467183/*!*/;

    /* ApplicationName=DataGrip 2020.1 */ truncate table tb_yq

    /*!*/;

    # at 4872

    #201012 9:46:23 server id 10 end_log_pos 4937 CRC32 0xce0048c8 GTID last_committed=17 sequence_number=18 rbr_only=no

    SET @@SESSION.GTID_NEXT= 'a7776f71-c8be-11e9-838f-0050563bb195:69'/*!*/;

    # at 4937

    #201012 9:46:23 server id 10 end_log_pos 5063 CRC32 0xc70a75d6 Query thread_id=79 exec_time=0 error_code=0

    SET TIMESTAMP=1602467183/*!*/;

    /* ApplicationName=DataGrip 2020.1 */ truncate table tb_mc

    数据恢复

    创建对应的库表

    create database re_ytest;

    use re_ytest

    -- 由于我这已知在binlog中存在建表语句,因此就不创建了

    create table re_ytest.tb_yq like ytest.tb_yq;

    create table re_ytest.tb_mc like ytest.tb_mc;

    执行恢复操作

    mysqlbinlog --skip-gtids --rewrite-db='ytest->re_ytest' --start-position=4 --stop-position=4746 /data/mysql_data/bin.000007|mysql re_ytest

    查看恢复结果

    数据会被恢复到 re_ytest 库中去,binlog恢复时,表结构不存在数据不会被恢复进来,binlog内有新建表语句的数据会被恢复出来

    mysql> use re_ytest

    mysql> show tables;

    +--------------------+

    | Tables_in_re_ytest |

    +--------------------+

    | tb_mc |

    | tb_ps |

    | tb_yq |

    +--------------------+

    5 rows in set (0.00 sec)

    mysql> select * from tb_yq;

    +----+------+-----------+

    | id | va | vb |

    +----+------+-----------+

    | 1 | 1 | 1 |

    | 2 | 2 | 2 |

    | 3 | 3 | 3 |

    | 4 | 4 | 4 |

    | 5 | 5 | 啦啦啦 |

    | 6 | 6 | 6 |

    +----+------+-----------+

    6 rows in set (0.00 sec)

    mysql> select * from tb_mc;

    +----+------+--------------+

    | id | va | vb |

    +----+------+--------------+

    | 1 | 11 | 11 |

    | 2 | 22 | 22 |

    | 3 | 33 | 33 |

    | 4 | 44 | 44 |

    | 5 | 55 | 55 |

    | 6 | 66 | 隔壁老苗 |

    +----+------+--------------+

    6 rows in set (0.00 sec)

    修正数据

    将源库中新增的数据补到re_ytest中来,id问题就解决了

    mysql> insert into re_ytest.tb_yq(va,vb) select va,vb from ytest.tb_yq;

    Query OK, 2 rows affected (0.00 sec)

    Records: 2 Duplicates: 0 Warnings: 0

    mysql> rename table ytest.tb_yq to ytest.tb_yq_bak;

    Query OK, 0 rows affected (0.00 sec)

    mysql> rename table re_ytest.tb_yq to ytest.tb_yq;

    Query OK, 0 rows affected (0.00 sec)

    mysql> select * from ytest.tb_yq;

    +----+------+--------------+

    | id | va | vb |

    +----+------+--------------+

    | 1 | 1 | 1 |

    | 2 | 2 | 2 |

    | 3 | 3 | 3 |

    | 4 | 4 | 4 |

    | 5 | 5 | 啦啦啦 |

    | 6 | 6 | 6 |

    | 7 | 7 | 7 |

    | 8 | 8 | 隔壁老王 |

    +----+------+--------------+

    8 rows in set (0.00 sec)

    展开全文
  • 「当我打开MP3发现里面什么文件都没有了,但是查看内存还是占用着的,这是怎么回事呢?...作为一款功能强大的数据恢复软件,轻松恢复意外删除的文档,并支持从损坏或格式化的硬盘中恢复所需文件。删除...

    「当我打开MP3发现里面什么文件都没有了,但是查看内存还是占用着的,这是怎么回事呢?我原来的音乐能找回来吗?」

    遇到这种情况不要担心,有可能是你的音乐文件被隐藏了。

    试试:我电脑→查看→隐藏的项目,就能看到MP3里的文件了。

    如果上述方法不能找回文件,推荐下载易我数据恢复软件帮你找回丢失的MP3文件。

    作为一款功能强大的数据恢复软件,可轻松恢复意外删除的文档,并支持从损坏或格式化的硬盘中恢复所需文件。

    • 删除恢复
    • 不小心删除了文档?别担心!易我数据恢复软件可快速找回已删除数据或因清空回收站导致的文档丢失。
    • 硬盘数据恢复
    • 没有把硬盘格式变成 RAW 还惨的事!没关系,易我数据恢复软件可以直接扫描无法访问的储存设备,从中恢复您想要的数据。
    • 格式化恢复
    • 不小心格式化储存设备导致资料丢失?别紧张,所丢失文档可以通过软件进行恢复。不论是硬盘、SSD、SD卡、记忆卡、闪卡、U盘或其他设备上的文档丢失,易我数据恢复软件都能轻松帮您找回!
    • 更多数据恢复场景
    • 易我数据恢复不仅用于恢复删除文档及格式化恢复,还适用其他许多数据丢失场景,如:人为操作失误、电源故障、系统崩溃、重新安装或者升级系统、硬盘故障、软件崩溃或其他未知原因。

    易我数据恢复软件详细使用教程

    注意:

    在音频文件被删除的当下,请立即停止使用该设备。否则,存入新数据将覆盖旧数据,救回被删除的数据成功率降低。

    第一步:运行易我音频恢复软件

    选择音频丢失位置,单击「扫描」查找丢失的音频文件。

    第二步:扫描并找到丢失的音频文件

    a82d25cc5921ede2b1e25e92c048a8b4.png

    点击添加图片描述(最多60个字)编辑

    等待程序完成扫描指定的磁盘来搜索丢失的文件。接着,通过双击找到的音频预览文件内容。

    第三步:恢复音频文件

    9c94d2fb43852fb5c2a1a2ed4a49262b.png

    点击添加图片描述(最多60个字)编辑

    勾选所有需要恢复的音频文件。单击「恢复」将它们储存到计算机或外接式储存设备。

    3365469a46dd94e57e3ab8dcae418386.png

    点击添加图片描述(最多60个字)编辑

    展开全文
  • 互联网营销时代,企业网站是公司必不少的展示平台,很多公司网站都会通过做网站SEO,或者竞价,让网站排名在搜索引擎首页,这是最佳的引流获客方式。所以每个公司的网站是非常重要的,九州营联小编今天与大家分享...
  • (一)truncate操作概述在生产中,truncate是使用较多的命令,在使用不当的情况下,往往会造成表的数据全部丢失,恢复较为困难。...将数据库恢复truncate之前的时刻,但是恢复时间较长;使用odu、prm-dul、...
  • 记录一次阿里云mysql数据库的所有表被truncate后数据库恢复的过程。谁遇到这种事情都会情不自禁的喊几声国骂。还好阿里云做了备份设置。每周二、周四、周六的全量备份。还有每6个小时的日志备份。步骤1:先下载一个...
  • 表被truncate/drop 的恢复方法有: 1闪回数据库(需要开启flashback) 2异机数据库不完全恢复(基于部分表空间),exp导出,再导入源库 3 T...
  • 表被truncate/drop 的恢复方法有: 1 闪回数据库(需要开启flashback) 2 异机数据库不完全恢复(基于部分表空间),exp导出,再导入源库 3 TSPITR (把表空间的所有表恢复的一个时间点,影响较大) 在没有...
  • 前2天因为一次错误的truncate 操作,删除了hz用户下的几个表,费了1天多时间才恢复出来,在这里记录下恢复过程和这个深刻的教训。 使用坏境: oracle11.2+rhce5.5 RAC,归档模式,数据库有1TB容量。 在配置流环境...
  • 使用坏境: oracle11.2+rhce5.5 RAC,归档模式,数据库有1TB容量。 在配置流环境时,因为一个误操作,删除了hz用户下的几个表。在这个数据库上还部署了其它几个用户,停机恢复数据显然是不可能的,分析情况后...
  • 版本信息: Oracle Data Unloader:Release 3.0.5 Copyright (c) 2008,2009 XiongJun. All rights reserved. Web: ... ...很智能,直接生成sqlldr零改动使用的数据文件、控制文件 工具下载点:...
  • drop,delete与truncate

    2019-08-20 15:55:22
    4.恢复:不可恢复 二.truncate 1.概念:删除表中数据 2.用法:truncate 表名(不可以加where) 3.范围:当表被TRUNCATE 后,这个表和索引所占用的空间会恢复到初始大小 4.恢复:不可恢复 三.de...
  • Delete与truncate的区别

    千次阅读 2014-03-19 19:03:11
     truncate table table_name删除"表格记录"不可恢复 。  delete 语句是数据库操作语言(dml),这个操作会放到rollback segement 中,事务提交之后才生效;如果有相应的 trigger,执行的时候将被触发。
  • delete和truncate区别

    2017-03-28 21:15:00
    相同之处:truncate在功能上与不带WHERE子句的delete语句相同:二者均删除表中的全部行。... truncate :删除"表格记录"不可恢复 。 2、delete :每次删除一行,并在事务日志中为所删除的每行记录一项。 tr...
  • 2.delete逐行记录到redo日志中,可恢复truncate和drop只记录少量操作语句,不能恢复。 3.delete可触发trigger,truncate不能 4.delete是DML语句,truncate、drop是DDL语句 5.执行速度drop>truncate>delete...
  • drop、truncate、delete

    2017-05-15 15:30:10
    delete命令删除的数据将存储在系统回滚段中,需要的时候,数据可以回滚恢复,而truncate命令删除数据是不可恢复的。 truncate table与delete table都是删除表里的所有数据(delete不加where字段)。truncate比...
  • 当删除表中的记录时,在通常情况下, 回滚段(rollback segments...而当运用TRUNCATE时, 回滚段不再存放任何恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. (TRUNCATE只在删除全...
  • Mysql的drop/truncate/delete

    2019-04-22 10:28:11
    drop> truncate> delete drop删除结构与数据 truncate删除表的所有数据,不可恢复 delete有条件删除数据,可以恢复
  • 1、truncate与drop是DDL语句,执行后无法回滚;delete是DML语句,回滚 2、truncate只能作用于表;delete,drop作用于表、视图等 ...6、truncate后会使表和索引所占用的空间会恢复到初始大小;delete操作不会减
  • delete 语句 delete from 表名: 1.将表中数据从上至下逐行删除 2.表中数据有可能被恢复 truncate语句 truncate table+表名 1.先删除表,再按照原结构创建表 2.数据不可恢复 truncate效率比较高...
  • truncate与delete的区别

    2013-11-01 11:00:35
    truncate table的删除不会写入日志,因此速度会很快,但是也因为如此,对数据的删除时不可恢复的,所以执行时最好先备份 ②truncate table是DDL(数据库定义语言:create,drop等)   delete属于DML(数据
  • delete和 truncate的区别

    2010-01-07 19:04:00
    truncate 是属于DDL语句 无法回滚,不可恢复 delete 是DML语句 ,可以回滚Truncate 释放所有的Block ,而Delete 不释放空间当要清空一个非常大的表时, truncate要高效的多, 与表中的数据量没什么关系.truncate 在...
  • 删除全表数据delete和truncate的区别 delete from 表名: 1.需要提交才能生效;...2.日志数据可恢复; 3.执行效率低。 truncate table 表名: 1.不需要提交,数据直接删除; 2.删除数据不可恢复; 3.执行效率高。 ...
  • 1、DROP删表,表结构将删了,当然数据也不存在了 2、TRUNCATE和DELETE删数据,表结构还在 3、DELETE可以带条件删除,TRUNCATE是全部删除 ...5、DELETE效率低,数据可以恢复,TRUNCATE效率高,数据不可恢复 ...
  • DROP,DELETE,TRUNCATE区别

    2018-04-05 20:12:59
    2.TRUNCATE表后,表和索引占用的空间会恢复到初始大小;DELETE操作不会减少表或索引占用的空间;DROP语句将表占用的空间全部释放掉。3.DELETE为DML操作,有数据库事物控制,回滚;事物提交后delete操作生效;DROP...
  • 我们一般都认为TRUNCATE是一种不回滚的操作,它会删除表中的所有数据以及重置Identity列。 如果你在事务中进行TRUNCATE操作,就能回滚。反之,它就不会从日志文件文件恢复数据。它不会在日志文件中记录删除的那些...
  • 删除表内容一般会采用以下三种方法: 1、drop table table_name  2、truncate table table_name 3、delete from table_name ...drop删除表结构和内容,不可恢复,所以需要谨慎使用! trunca...
  • TRUNCATE,DELETE,DROP区别

    2014-02-10 20:35:17
    命令 删除内容限制 删除速度 是否删除定义 是否释放空间 被删数据是否可恢复 TRUNCATE 必须为全部 快 √ √ × DELETE 可指定 慢 × × √ DROP 必须为全部 快 √ √ ×

空空如也

空空如也

1 2 3 4 5 ... 10
收藏数 188
精华内容 75
关键字:

truncate可恢复