精华内容
下载资源
问答
  • sql truncate This article explores the recovery of data removed by SQL Delete and SQL Truncate statements using ... 本文探讨了使用SQL数据库备份恢复由SQL Delete和SQL Truncate语句删除的数据的方法。 ...

    sql truncate

    This article explores the recovery of data removed by SQL Delete and SQL Truncate statements using SQL database backups.

    本文探讨了使用SQL数据库备份恢复由SQL Delete和SQL Truncate语句删除的数据的方法。

    Before you go further with this article, go through the following articles to understand how delete and truncate statements work in detail.

    在继续本文之前,请仔细阅读以下文章,以详细了解delete和truncate语句的工作方式。

    创建一个测试数据库环境 (Create a test database environment)

    Let’s create a database environment for this article demonstration.

    让我们为本文演示创建一个数据库环境。

    • Create a database

      建立资料库

      CREATE DATABASE SQLShackDemo;
      GO
      USE SQLShackDemo;
      GO
      
    • Create a SQL table. We will use a delete statement for this table

      创建一个SQL表。 我们将为此表使用一条delete语句

      CREATE TABLE DeletemyData
      (id     INT IDENTITY(1, 1), 
       [Name] VARCHAR(40)
      );
      GO
      
    • Create another SQL table. We will use the truncate statement for this table

      创建另一个SQL表。 我们将在表中使用truncate语句

      CREATE TABLE TruncatemyData
      (id     INT IDENTITY(1, 1), 
       [Name] VARCHAR(40)
      );
      GO
      

    回滚数据演示 (Rollback data demonstration)

    At this point, we have a SQL database with two empty tables [DeletemyData] and [TruncatemyData]. It is a new database, and we do not have any database backup for it. Let’s take a full database backup using the following query. You can also use the backup wizard in SSMS to do it graphically. It is a small database, so no need to worry about backup compression.

    此时,我们有了一个带有两个空表[DeletemyData]和[TruncatemyData]SQL数据库。 这是一个新数据库,我们没有任何数据库备份。 让我们使用以下查询进行完整的数据库备份。 您也可以使用SSMS中的备份向导以图形方式进行备份。 它是一个小型数据库,因此无需担心备份压缩。

    Backup database SQLShackdemo to disk='c:\temp\SQLShackdemo.bak'
    

    Backup database

    Execute the following query to retrieve database backup history from the msdb system database.

    执行以下查询以从msdb系统数据库中检索数据库备份历史记录。

    SELECT bs.database_name AS DatabaseName,
        CASE bs.type
            WHEN 'D'
            THEN 'Full'
            WHEN 'I'
            THEN 'Differential'
            WHEN 'L'
            THEN 'Transaction Log'
        END AS BackupType, 
        CAST(bs.first_lsn AS VARCHAR(50)) AS FirstLSN, 
        CAST(bs.last_lsn AS VARCHAR(50)) AS LastLSN, 
        bmf.physical_device_name AS PhysicalDeviceName, 
        CAST(CAST(bs.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS BackupSize, 
        bs.recovery_model AS RecoveryModel
    FROM msdb.dbo.backupset AS bs
      INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bs.media_set_id = bmf.media_set_id
    WHERE bs.database_name = 'SQLShackdemo'
    ORDER BY backup_start_date DESC, 
          backup_finish_date;
    

    It gives first and last log sequence number (LSN) details as well.

    它还提供了第一个和最后一个日志序列号(LSN)详细信息。

    log sequence number

    Now, insert ten records in both tables.

    现在,在两个表中插入十个记录。

    DECLARE @id INT;
    SET @ID = 10;
    WHILE(@ID > 0)
        BEGIN
            INSERT INTO DeletemyData([Name])
        VALUES('Delete Data' + ' ' + CAST((@ID) AS VARCHAR));
            SET @ID = @ID - 1;
        END;
     
     
    DECLARE @id INT;
    SET @ID = 10;
    WHILE(@ID > 0)
        BEGIN
            INSERT INTO TruncatemyData([Name])
        VALUES('Truncate data' + ' ' + CAST((@ID) AS VARCHAR));
            SET @ID = @ID - 1;
        END;
    

    View sample data

    Now, open two query windows in SSMS.

    现在,在SSMS中打开两个查询窗口。

    In the first query window, delete a few records from [DeletemyData] table.

    在第一个查询窗口中,从[DeletemyData]表中删除一些记录。

    DELETE FROM
    WHERE id < 5;
    

    In the second query window, truncate the SQL table. We cannot specify the WHERE clause in truncate, so it removes all records from a table.

    在第二个查询窗口中,截断SQL表。 我们无法在截断中指定WHERE子句,因此它将从表中删除所有记录。

    Truncate table TruncatemyData
    

    Verify records in both the tables. We have zero records in the [TruncatemyData] table while [DeletemyData] contains six records.

    验证两个表中的记录。 [TruncatemyData]表中有零条记录,而[DeletemyData]包含六条记录。

    Verify records

    We can use undocumented function fn_dblog to get information about delete and truncate statements from the transaction log. Refer to this article, How to continuously read Transaction log file data directly in a SQL Server database with fn_dblog and fn_dump_dblog for more detail.

    我们可以使用未记录的函数fn_dblog从事务日志中获取有关delete和truncate语句的信息。 请参阅本文, 如何使用fn_dblog和fn_dump_dblog在SQL Server数据库中直接直接连续读取事务日志文件数据,以获取更多详细信息。

    We can filter transaction log entry using the delete and truncate table clause in the where condition.

    我们可以使用where条件中的delete和truncate table子句来过滤事务日志条目。

    USE SQLShackDemo;
    GO
    SELECT [Current LSN], 
           [transaction ID] tranID, 
           [begin time], 
           Description, 
           operation, 
           Context
    FROM ::fn_dbLog(NULL, NULL)
    WHERE [Transaction Name] IN('Delete', 'Truncate table');
    

    It shows two transaction log records. We can segregate transactions using the description column. As per the following screenshot, the first entry is for delete while later entry is for the truncate statement. You can note down the begin time of these transaction.

    它显示两个事务日志记录。 我们可以使用描述列来隔离交易。 根据以下屏幕截图,第一个条目用于删除,而后面的条目用于truncate语句。 您可以记下这些事务的开始时间。

    • Delete: 2020/02/26 19:44:27:440

      删除:2020/02/26 19:44:27:440
    • Truncate: 2020/02/26 19:44:45:830

      截断时间:2020/02/26 19:44:45:830

    Output of undocumented function fn_dblog

    In the full recovery model, transaction log backup maintains the log chain. We can also do point in time recovery using transaction log backup. Let’s execute the following query for log backup. It takes backups of all data changes.

    在完全恢复模型中,事务日志备份维护日志链。 我们还可以使用事务日志备份来进行时间点恢复。 让我们执行以下查询进行日志备份。 它备份所有数据更改。

    Backup log SQLShackdemo to disk='c:\temp\SQLShackdemo_log.trn'
    

    Backup log

    View database backup history using the above query from the msdb database. It shows two entries – full and transaction log backup.

    使用上述查询,从msdb数据库查看数据库备份历史记录。 它显示两个条目–完整和事务日志备份。

    View database backup history

    恢复从SQL Delete语句删除的数据 (Recover data deleted from a SQL Delete statement)

    Now, suppose you require to recover the deleted data from the existing backups. We will create a new database from the full backup. We need to restore a backup in NORECOVERY mode so that we can apply further transaction log backup on it.

    现在,假设您需要从现有备份中恢复已删除的数据。 我们将从完整备份中创建一个新数据库。 我们需要以NORECOVERY模式还原备份,以便我们可以在其上应用进一步的事务日志备份。

    USE [master];
    RESTORE DATABASE [SQLShackDemo_restore] FROM DISK = N'C:\TEMP\SQLShackdemo.bak' WITH FILE = 1,
    MOVE N'SQLShackDemo' TO N'C:\sqlshack\Demo\SQLShackDemo.mdf', 
    MOVE N'SQLShackDemo_log' TO N'C:\sqlshack\Demo\SQLShackDemo_log.ldf',
    NORECOVERY, NOUNLOAD, STATS = 5;
    GO
    

    Database [SQLShackDemo_restore] is in restoring mode. We cannot access the database while it is in restoring mode.

    数据库[SQLShackDemo_restore]处于还原模式。 在恢复模式下,我们无法访问数据库。

    Restored database

    In the article, we learned about Point in Time Recovery with SQL Server using the STOPAT parameter of Restore log command. We can specify a specific timestamp or LSN in the STOPAT parameter.

    在本文中,我们使用Restore log命令的STOPAT参数了解了SQL Server的时间点恢复 。 我们可以在STOPAT参数中指定特定的时间戳或LSN。

    Similarly, we can use STOPBEFOREMARK in a restore log statement. As its name suggests, this parameter instructs SQL Server to stop database restore once it reaches a specific timestamp or LSN. You can refer to Microsoft docs for more details on STOPBEFOREMARK.

    同样,我们可以在还原日志语句中使用STOPBEFOREMARK。 顾名思义,此参数指示SQL Server在达到特定的时间戳或LSN后停止数据库还原。 您可以参考Microsoft文档以获取有关STOPBEFOREMARK的更多详细信息。

    以十进制格式转换HEX LSN值 (Convert HEX LSN value in decimal format)

    In the output of fn_dblog above, we have LSN for delete and truncate statements.

    在上面的fn_dblog的输出中,我们具有用于删除和截断语句的LSN。

    • Delete LSN: 00000026:00000230:0001 删除LSN :00000026:00000230:0001
    • Truncate LSN: 00000026:00000268:0001 截断LSN: 00000026:00000268:0001

    LSN values in fn_dblog are in the hexadecimal format. Restore log command requires LSN in a decimal format. We can use the following query to convert it into the decimal format. Here, specify the LSN in the @LSN parameter.

    fn_dblog中的LSN值采用十六进制格式。 Restore log命令要求LSN为十进制格式。 我们可以使用以下查询将其转换为十进制格式。 在这里,在@LSN参数中指定LSN。

    DECLARE @LSN VARCHAR(22), @LSN1 VARCHAR(11), @LSN2 VARCHAR(10), @LSN3 VARCHAR(5);
    SET @LSN = '00000026:00000230:0001';
    SET @LSN1 = LEFT(@LSN, 8);
    SET @LSN2 = SUBSTRING(@LSN, 10, 8);
    SET @LSN3 = RIGHT(@LSN, 4);
    SET @LSN1 = CAST(CONVERT(VARBINARY, '0x' + RIGHT(REPLICATE('0', 8) + @LSN1, 8), 1) AS INT);
    SET @LSN2 = CAST(CONVERT(VARBINARY, '0x' + RIGHT(REPLICATE('0', 8) + @LSN2, 8), 1) AS INT);
    SET @LSN3 = CAST(CONVERT(VARBINARY, '0x' + RIGHT(REPLICATE('0', 8) + @LSN3, 8), 1) AS INT);
    SELECT CAST(@LSN1 AS VARCHAR(8)) + CAST(RIGHT(REPLICATE('0', 10) + @LSN2, 10) AS VARCHAR(10)) + CAST(RIGHT(REPLICATE('0', 5) + @LSN3, 5) AS VARCHAR(5));
    

    Using the above query, we get the following LSN values for both delete and truncate statements.

    使用上面的查询,我们为delete和truncate语句获得以下LSN值。

    • Delete LSN: 38000000056000001 删除LSN :38000000056000001
    • Truncate LSN: 38000000061600001 截断LSN: 38000000061600001

    Now, run the restore log query using the STOPBEFORMARK parameter. This query stops the processing of database restores before the specified LSN.

    现在,使用STOPBEFORMARK参数运行还原日志查询。 此查询在指定的LSN之前停止数据库还原的处理。

    Restore log  [SQLShackDemo_restore] FROM DISK = N'C:\TEMP\SQLShackdemo_log.trn'
    with STOPBEFOREMARK ='lsn:38000000056000001'
    

    We get the following output of above RESTORE LOG command.

    我们得到以上RESTORE LOG命令的以下输出。

    Convert HEX LSN value in decimal format

    Once the log backup is restored, we can access the database. Verify the records in the [DeletemyData] table, and it shows data is available. We can use this data and export to an original database using export and import wizard.

    恢复日志备份后,我们就可以访问数据库了。 验证[DeletemyData]表中的记录,并显示数据可用。 我们可以使用此数据,并使用导出和导入向导将其导出到原始数据库。

    Recover data deleted from a SQL Delete statement

    恢复从SQL Truncate语句中删除的数据 (Recover data deleted from a SQL Truncate statement)

    We have recovered data deleted by a delete statement. Let’s perform a similar test for recovering data from the truncate statement.

    我们已经恢复了由delete语句删除的数据。 让我们执行类似的测试以从truncate语句中恢复数据。

    • Restore full database backup in NORECOVERY mode

      以NORECOVERY模式还原完整的数据库备份

      USE [master];
      RESTORE DATABASE [SQLShackDemo_restore_1] FROM DISK = N'C:\TEMP\SQLShackdemo.bak' WITH FILE = 1,
      MOVE N'SQLShackDemo' TO N'C:\sqlshack\Demo\SQLShackDemo.mdf', 
      MOVE N'SQLShackDemo_log' TO N'C:\sqlshack\Demo\SQLShackDemo_log.ldf',
      NORECOVERY, NOUNLOAD, STATS = 5;
      GO
      
    • Restore transaction log backup with the STOPBEFOREMARK parameter. Specify the LSN we derived above from the hex.

      使用STOPBEFOREMARK参数还原事务日志备份。 指定我们从十六进制导出的LSN。

      Restore log  [SQLShackDemo_restore_1] FROM DISK = N'C:\TEMP\SQLShackdemo_log.trn'
      with STOPBEFOREMARK ='lsn:38000000061600001'
      
    • Verify data in the [TruncatemyData] table

      验证[TruncatemyData]表中的数据

      Recover data deleted from a SQL Truncate statement

    结论 (Conclusion)

    In this article, we recovered deleted data using SQL Delete and SQL Truncate statements with the help of database backups. You should not perform any tests in the production database. You can create a test environment and explore data recovery.

    在本文中,我们在数据库备份的帮助下使用SQL Delete和SQL Truncate语句恢复了已删除的数据。 您不应在生产数据库中执行任何测试。 您可以创建一个测试环境并探索数据恢复。

    翻译自: https://www.sqlshack.com/how-to-use-database-backups-to-recover-data-after-sql-delete-and-sql-truncate-statements/

    sql truncate

    展开全文
  • 非DBA人员也可以恢复truncate掉的数据了。。。哈哈 借着本人犯点错误的机会,连错数据库,truncate掉了10张本不想删除的数据 :o (还好此次错误没照成什么影响,自己负责的小系统刚运行个把月,客户没当回事未...
    软件ParnassusData_PRMForOracle_3002
    非DBA人员也可以恢复truncate掉的数据了。。。哈哈

    借着本人犯点错误的机会,连错数据库,truncate掉了10张本不想删除的数据 :o (还好此次错误没照成什么影响,自己负责的小系统刚运行个把月,客户没当回事未追究,万幸啊!若是其他系统后果不堪设想)
    自己和自己较了把劲,琢磨2天truncate数据恢复的问题,结果记录下来和大家分享:
    或许以后用得着!!(用不到最好。。。。)

    先说明,本人是个菜鸟阶段的研发人员,本文章适合非DBA人员。
    DBA各种高深的数据恢复手段我们这些外界研发人员真是遥不可及啊!!
    当一个小团队压根不存在DBA时,误操作truncate掉了数据怎么办?
    可以尝试使用这个工具进行恢复数据。傻瓜式操作。真心的适合研发人员使用。


    先说下恢复结果吧:
    折腾了两天,数据恢复了90%以上,有一张表未恢复全,最大的还是收获了对这个工具的认识,至少现在个人对truncate的数据恢复有一丁点信心了。因为原来压根就是空白啊。

    数据库环境:
    oracle 11g
    服务器:坑爹的linux

    第一次下载使用此软件的同仁啊。强烈建议你详读PRM技术白皮书.pdf。
    此工具运行环境要求,jdk1.6以上。
    最开始使用的时候,考虑到在linux上运行此软件,所以把全部时间精力都用在linux如何安装jdk以及各种无聊的命令、各种远程工具的使用测试了。
    后来读了下技术书(其实技术书里未说明这点,有些地方误导了我),为什么一定要在数据库服务器上运行此工具呢?
    后来干脆随便找个数据库dbf文件测试一下,在本地环境(没装oracle)运行,结果恍然大悟,原来在本地运行就可以。
    此工具单存的读取数据库dbf文件即可(系统表空间所在的文件+丢失数据所在的文件)。

    后来发现:原来的精力都用错方向了,此软件真是傻瓜式操作。
    个人建议恢复数据时新建个用户及表结构,使用桥接的方式将数据恢复至数据库中,在常规恢复时,使用可执行文件方式将数据load时,大量数据日期类型会报错,很麻烦。耽误时间。而桥接的方式只需新建表结构,配置个连接就可以直接还原。


    好了,总结一下:
    1.
    此软件只需要单存的读取数据库dbf文件就可以,不依赖于数据库的启动(因为很多数据库操作系统都是linux等在上面运行麻烦至极)。

    2.
    此点很重要:此次恢复90%的数据而未能全部恢复的原因(truncate数据后,一定要第一时间、第一时间、第一时间将丢失的数据所在的数据库文件复制出来),此次truncate数据后,是第二天才研究明白这个软件然后才把dbf文件拷贝出来使用的。所以照成了部分数据至今不能恢复。(若哪位高手能提供些其他思路挽救未能恢复的数据,不尽感激)

    3.
    truncate慎用。千万不要连错了数据库。。。。

    4.
    完整的数据库备份机制才是王道。。

    另真心寻求各位DBA们帮助,还有可能找回那些至今未恢复的数据吗 ??

    附件可以下载此软件。
    留着吧。万一以后能用到呢。
    也可以百度直接搜索。
    展开全文
  • 版本信息: Oracle Data Unloader:Release 3.0.5 Copyright (c) 2008,2009 XiongJun. All rights reserved. Web: ... ...很智能,直接生成sqlldr可零改动使用数据文件、控制文件 工具下载点:...

    版本信息:

    Oracle Data Unloader:Release 3.0.5

    Copyright (c) 2008,2009 XiongJun. All rights reserved.

    Web: http://www.laoxiong.net
    Email: magic007cn@gmail.com

    评价:

    很智能,直接生成sqlldr可零改动使用的数据文件、控制文件

    工具下载点:

    https://download.csdn.net/download/viviliving/11422576

    模拟过程:

    1、害怕玩坏先rman备份整库

    run{
    backup full database include current controlfile 
    format 'F:\rman\fullback_%d_%T_%s' 
    plus archivelog format 'F:\rman\arch_%d_%T_%s' delete all input;
    backup as copy current controlfile format 'F:\rman\ctl_%d_%T_%s.ctl';  
    delete noprompt obsolete;
    }
    2、创建测试用户test测试表t

    create user test identified by test;

     create table t as select * from user_tab_comments;

    insert into t select * from t;

    /

    多执行几遍

    SQL> select count(1) from t;
      COUNT(1)
    ----------
         65536

    3、truncate table t;

    4、关闭数据库,开始odu过程,没关闭的情况下odu可能scan不出来表,估计还没脏写盘

    F:\dul\odu\odu>sqlplus "/as sysdba"
    
    SQL*Plus: Release 10.2.0.3.0 - Production on 星期三 7月 24 15:54:58 2019
    
    Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
    
    
    连接到:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> shutdown immediate
    数据库已经关闭。
    已经卸载数据库。
    ORACLE 例程已经关闭。
    SQL> exit
    从 Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options 断开

    5、select ts#,file#,rfile#,name,BLOCK_SIZE from v$datafile;
       将其结构填入odu目录下的control.txt文件中

    SQL> select ts#,file#,rfile#,name,BLOCK_SIZE from v$datafile;
           TS#      FILE#     RFILE# NAME                                                                             BLOCK_SIZE
    ---------- ---------- ---------- -------------------------------------------------------------------------------- ----------
             0          1          1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF                                     8192
             1          2          2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF                                    8192
             2          3          3 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF                                     8192
             4          4          4 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF                                      8192
             6          5          5 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF                                    8192

    文件内容(第一列前空格删掉)

    #ts #fno   #rfno     filename                                          block_size
    0          1          1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF                                     8192
    1          2          2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF                                    8192
    2          3          3 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF                                     8192
    4          4          4 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF                                      8192
    6          5          5 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF                                    8192

    6、进入odu

    F:\dul\odu\odu>odu
    
    Oracle Data Unloader:Release 3.0.5
    
    Copyright (c) 2008,2009 XiongJun. All rights reserved.
    
    Web: http://www.laoxiong.net
    Email: magic007cn@gmail.com
    
    loading default config.......
    
    byte_order little
    block_size  8192
    data_path   data
    lob_path    lob
    charset_name ZHS16GBK
    ncharset_name AL16UTF16
    output_format text
    lob_storage file
    clob_byte_order little
    trace_level 1
    delimiter |
    
    
    
    
    load control file 'config.txt' successful
    loading default control file ......
    
    
     ts#   fn  rfn bsize   blocks bf offset filename
    ---- ---- ---- ----- -------- -- ------ --------------------------------------------
       0    1    1  8192    61440 N       0 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
       1    2    2  8192     8960 N       0 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
       2    3    3  8192    32000 N       0 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
       4    4    4  8192      800 N       0 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
       6    5    5  8192    12800 N       0 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
    load control file 'control.txt' successful
    loading dictionary data......

    7、执行命令:unload dict

    执行命令:scan extent 

    ODU> unload dict
    CLUSTER C_USER# file_no: 1 block_no: 89
    TABLE OBJ$ file_no: 1 block_no: 121
    CLUSTER C_OBJ# file_no: 1 block_no: 25
    CLUSTER C_OBJ# file_no: 1 block_no: 25
    found IND$'s obj# 19
    found IND$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:3
    found TABPART$'s obj# 266
    found TABPART$'s dataobj#:266,ts#:0,file#:1,block#:2121,tab#:0
    found INDPART$'s obj# 271
    found INDPART$'s dataobj#:271,ts#:0,file#:1,block#:2161,tab#:0
    found TABSUBPART$'s obj# 278
    found TABSUBPART$'s dataobj#:278,ts#:0,file#:1,block#:2217,tab#:0
    found INDSUBPART$'s obj# 283
    found INDSUBPART$'s dataobj#:283,ts#:0,file#:1,block#:2257,tab#:0
    found IND$'s obj# 19
    found IND$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:3
    found LOB$'s obj# 151
    found LOB$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:6
    found LOBFRAG$'s obj# 299
    found LOBFRAG$'s dataobj#:299,ts#:0,file#:1,block#:2393,tab#:0
    ODU> scan extent
    
    scan extent start: 2019-07-24 16:01:00
    scanning extent...
    scanning extent finished.
    scan extent completed: 2019-07-24 16:01:14
    

    8、desc [用户名].[被删除数据的表名]

    ODU> desc test.t
    
    
    Object ID:52936
    Storage(Obj#=52936 DataObj#=52936 TS#=4 File#=4 Block#=419 Cluster=0)
    
    NO. SEG INT Column Name                    Null?     Type
    --- --- --- ------------------------------ --------- ------------------------------
      1   1   1 TABLE_NAME                     NOT NULL  VARCHAR2(30)
      2   2   2 TABLE_TYPE                               VARCHAR2(11)
      3   3   3 COMMENTS                                 VARCHAR2(4000)
    

    做了个truncate前后的信息对比(发现dataobj发生变化)

    truncate之前 ODU>
     desc test.t 
    Object ID:52936 
    Storage(Obj#=52936 DataObj#=52936 TS#=4 File#=4 Block#=419 Cluster=0) truncate之后 
    ODU> desc test.t
     Object ID:52936 
    Storage(Obj#=52936 DataObj#=52946 TS#=4 File#=4 Block#=419 Cluster=0) 

    9、使用ODU来确定T1表原来的data object id。一般来说,数据段的数据块,一般是在段头后面相邻的块中。但是我们可以从段头来确认:

    File#=4 Block#=419

    ODU> dump datafile 4 block 419
    Block Header:
    block type=0x23 (ASSM segment header block)
    block format=0xa2 (oracle 10)
    block rdba=0x010001a3 (file#=4, block#=419)
    scn=0x0000.00094658, seq=2, tail=0x46582302
    block checksum value=0x65f1=26097, flag=4
    Data Segment Header:
      Extent Control Header
      -------------------------------------------------------------
      Extent Header:: extents: 17  blocks: 256
                      last map: 0x00000000  #maps: 0  offset: 2716
          Highwater:: 0x01000309  (rfile#=4,block#=777)
                      ext#: 16  blk#: 128   ext size:128
          #blocks in seg. hdr's freelists: 0
          #blocks below: 247
          mapblk: 0x00000000   offset: 16
      --------------------------------------------------------
      Low HighWater Mark :
          Highwater::  0x01000221  ext#: 15      blk#: 8      ext size: 8
      #blocks in seg. hdr's freelists: 0
      #blocks below: 121
      mapblk  0x00000000  offset: 15
      Level 1 BMB for High HWM block: 0x01000211
      Level 1 BMB for Low HWM block: 0x0100028a
      --------------------------------------------------------
      Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0
      L2 Array start offset:  0x00001434
      First Level 3 BMB:  0x00000000
      L2 Hint for inserts:  0x010001a2
      Last Level 1 BMB:  0x0100028a
      Last Level 1I BMB:  0x010001a2
      Last Level 1II BMB:  0x00000000
         Map Header:: next  0x00000000  #extents: 17    obj#: 52936  flag: 0x21000000
      Extent Map
      -------------------------------------------------------------
       0x010001a1  length: 8
       0x010001a9  length: 8
       0x010001b1  length: 8
       0x010001b9  length: 8
       0x010001c1  length: 8
       0x010001c9  length: 8
       0x010001d1  length: 8
       0x010001d9  length: 8
       0x010001e1  length: 8
       0x010001e9  length: 8
       0x010001f1  length: 8
       0x010001f9  length: 8
       0x01000201  length: 8
       0x01000209  length: 8
       0x01000211  length: 8
       0x01000219  length: 8
       0x01000289  length: 128
    
      Auxillary Map
      -------------------------------------------------------------
       Extent 0      :  L1 dba:  0x010001a1 Data dba:  0x010001a4
       Extent 1      :  L1 dba:  0x010001a1 Data dba:  0x010001a9
       Extent 2      :  L1 dba:  0x010001b1 Data dba:  0x010001b2
       Extent 3      :  L1 dba:  0x010001b1 Data dba:  0x010001b9
       Extent 4      :  L1 dba:  0x010001c1 Data dba:  0x010001c2
       Extent 5      :  L1 dba:  0x010001c1 Data dba:  0x010001c9
       Extent 6      :  L1 dba:  0x010001d1 Data dba:  0x010001d2
       Extent 7      :  L1 dba:  0x010001d1 Data dba:  0x010001d9
       Extent 8      :  L1 dba:  0x010001e1 Data dba:  0x010001e2
       Extent 9      :  L1 dba:  0x010001e1 Data dba:  0x010001e9
       Extent 10     :  L1 dba:  0x010001f1 Data dba:  0x010001f2
       Extent 11     :  L1 dba:  0x010001f1 Data dba:  0x010001f9
       Extent 12     :  L1 dba:  0x01000201 Data dba:  0x01000202
       Extent 13     :  L1 dba:  0x01000201 Data dba:  0x01000209
       Extent 14     :  L1 dba:  0x01000211 Data dba:  0x01000212
       Extent 15     :  L1 dba:  0x01000211 Data dba:  0x01000219
       Extent 16     :  L1 dba:  0x01000289 Data dba:  0x0100028b
      -------------------------------------------------------------
    
       Second Level Bitmap block DBAs
      -------------------------------------------------------------
       DBA 1:   0x010001a2

    其中:block rdba=0x010001a3 (file#=4, block#=419)

    其次: Extent 0      :  L1 dba:  0x010001a1 Data dba:  0x010001a4

    从Data dba:  0x010001a4比段头rdba=0x010001a3 大1;

    可以推算到Extent 0 的第一个块号file#=4, block#=420

    ODU> dump datafile 4 block 420 header
    Block Header:
    block type=0x06 (table/index/cluster segment data block)
    block format=0xa2 (oracle 10)
    block rdba=0x010001a4 (file#=4, block#=420)
    scn=0x0000.000940e1, seq=2, tail=0x40e10602
    block checksum value=0xc6ef=50927, flag=4
    Data Block Header Dump:
     Object id on Block? Y
     seg/obj: 0xcec8=52936  csc: 0x00.940e0  itc: 3  flg: E  typ: 1 (data)
         brn: 0  bdba: 0x10001a1 ver: 0x01
    
     Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
    0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.000940e0
    0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
    0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
    Data Block Dump:
    ================
    flag=0x0 --------
    ntab=1
    nrow=1
    frre=-1
    fsbo=0x14
    ffeo=0x1f75
    avsp=0x1f61
    tosp=0x1f61

    可以发现

     Object id on Block? Y
     seg/obj: 0xcec8=52936  

    test.t表的object ID=52936  

    10、使用ODU来unload数据:

    ODU> unload table test.t object 52936

    Unloading table: T,object ID: 52936
    Unloading segment,storage(Obj#=52936 DataObj#=52936 TS#=4 File#=4 Block#=419 Cluster=0)
    65536 rows unloaded
    11、创建表结构(odu\data目录下的test_t.sql就是)

    sqlplus test/test @data\test_t.sql

    加载数据

    F:\dul\odu\odu\data>sqlldr test/test control=TEST_T.ctl
    
    SQL*Loader: Release 10.2.0.3.0 - Production on 星期三 7月 24 16:24:29 2019
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    达到提交点 - 逻辑记录计数 2072
    达到提交点 - 逻辑记录计数 4144
    达到提交点 - 逻辑记录计数 6216
    达到提交点 - 逻辑记录计数 8288
    达到提交点 - 逻辑记录计数 10360
    达到提交点 - 逻辑记录计数 12432
    达到提交点 - 逻辑记录计数 14504
    达到提交点 - 逻辑记录计数 16576
    达到提交点 - 逻辑记录计数 18648
    达到提交点 - 逻辑记录计数 20720
    达到提交点 - 逻辑记录计数 22792
    达到提交点 - 逻辑记录计数 24864
    达到提交点 - 逻辑记录计数 26936
    达到提交点 - 逻辑记录计数 29008
    达到提交点 - 逻辑记录计数 31080
    达到提交点 - 逻辑记录计数 33152
    达到提交点 - 逻辑记录计数 35224
    达到提交点 - 逻辑记录计数 37296
    达到提交点 - 逻辑记录计数 39368
    达到提交点 - 逻辑记录计数 41440
    达到提交点 - 逻辑记录计数 43512
    达到提交点 - 逻辑记录计数 45584
    达到提交点 - 逻辑记录计数 47656
    达到提交点 - 逻辑记录计数 49728
    达到提交点 - 逻辑记录计数 51800
    达到提交点 - 逻辑记录计数 53872
    达到提交点 - 逻辑记录计数 55944
    达到提交点 - 逻辑记录计数 58016
    达到提交点 - 逻辑记录计数 60088
    达到提交点 - 逻辑记录计数 62160
    达到提交点 - 逻辑记录计数 64232
    达到提交点 - 逻辑记录计数 65536
    
    F:\dul\odu\odu\data>\

     

    展开全文
  • 这次的数据恢复操作我我使用第一种方式,以下是操作步骤: 1、拷贝数据文件和2节点的归档日志文件。由于故障库的备份是nfs挂载到备份机上的,省去了拷贝数据文件的时间,只需要把2节点下的archive日志文件拷贝到测试...
    这次的数据恢复操作我我使用第一种方式,以下是操作步骤:
    1、拷贝数据文件和2节点的归档日志文件。由于故障库的备份是nfs挂载到备份机上的,省去了拷贝数据文件的时间,只需要把2节点下的archive日志文件拷贝到测试库上就行。
    2、创建新库的pfile参数文件,拷贝故障库的spfile文件,修改参数并删除有关rac的不需要参数信息。
    3、创建新库的密码文件.
         orapwd file=/u02/app/oracle/product/11.2.0/db1/dbs/orapwklir passord=oracle entries=10 force=y
    4、创建新库的dump目录。
    5、启动新库到nomount下。
    [oracle@kms2 dbs]$ export ORACLE_SID=klir
    [oracle@kms2 dbs]$ sqlplus / as sysdba
    SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 18 09:56:35 2010
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    Connected to an idle instance.
    SQL> startup nomount pfile='/u02/pfile_klir.ora'
    6、从备份集中恢复控制文件,并启动到mount状态下。
    RMAN>   restore controlfile from /orabk/ctl_c-949039848-20101116-00.ctl;
    RMAN>  alter database mount;
     
    7、恢复表空间。因为只是一个表空间下的一些表被删除了,朋友建议我只恢复有问题的表空间就可以了,当然system,sysaux,undo这些基本的表空间是要恢复的。

    RMAN> restore tablespace system;
    Starting restore at 16-NOV-10
    Starting implicit crosscheck backup at 16-NOV-10
    allocated channel: ORA_DISK_1
    Crosschecked 198 objects
    Finished implicit crosscheck backup at 16-NOV-10
    Starting implicit crosscheck copy at 16-NOV-10
    using channel ORA_DISK_1
    Finished implicit crosscheck copy at 16-NOV-10
    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 00001 to +DG1/system01.dbf
    channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13771_1_1_735012733.dbf
    channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13771_1_1_735012733.dbf tag=TAG20101114T021212
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:09:15
    Finished restore at 16-NOV-10
    RMAN> restore tablespace sysaux;
    Starting restore at 16-NOV-10
    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 +DG1/sysaux01.dbf
    channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13770_1_1_735012528.dbf
    channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13770_1_1_735012528.dbf tag=TAG20101114T021212                               
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:08:50
    Finished restore at 16-NOV-10
    RMAN> restore tablespace HZDATATBS;
    Starting restore at 16-NOV-10
    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 00011 to +DG1/hzdata01.dbf
    channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13770_1_1_735012528.dbf
    channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13770_1_1_735012528.dbf tag=TAG20101114T021212
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:16:35
    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 00154 to +DG1/hzdata02.dbf
    channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13938_1_1_735186686.dbf
    channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13938_1_1_735186686.dbf tag=TAG20101116T023123
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:02:05
    Finished restore at 16-NOV-10
    RMAN> restore tablespace hzindtbs;
    Starting restore at 16-NOV-10
    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 00017 to +DG1/hzind02.dbf
    channel ORA_DISK_1: restoring datafile 00021 to +DG1/hzind03.dbf
    channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13773_1_1_735012734.dbf
    channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13773_1_1_735012734.dbf tag=TAG20101114T021212
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:13:45
    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 00012 to +DG1/hzind01.dbf
    channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13771_1_1_735012733.dbf
    channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13771_1_1_735012733.dbf tag=TAG20101114T021212
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:14:46
    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 00153 to +DG1/hzind04.dbf
    channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13937_1_1_735186478.dbf
    channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13937_1_1_735186478.dbf tag=TAG20101116T023123
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:03:15
    Finished restore at 17-NOV-10

    RMAN> restore tablespace undotbs2;
    Starting restore at 17-NOV-10
    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 00006 to +DG1/undotbs201.dbf
    channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13770_1_1_735012528.dbf
    channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13770_1_1_735012528.dbf tag=TAG20101114T021212
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:04:46
    Finished restore at 17-NOV-10
    RMAN> restore tablespace undotbs1;
    Starting restore at 17-NOV-10
    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 00003 to +DG1/undotbs101.dbf
    channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13773_1_1_735012734.dbf
    channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13773_1_1_735012734.dbf tag=TAG20101114T021212
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:11:05
    Finished restore at 17-NOV-10
     
    8.Offline 其它不用的表空间。
    SQL> alter database datafile 5,7 offline drop;
    Database altered.
    SQL> alter database datafile 13,14,15,16 offline drop;
    Database altered.
     
    9、不完全恢复数据库。

    SQL> recover database until time '2010-11-16 16:25:00'   using backup  controlfile;
    ORA-00279: change 2602636589 generated at 11/14/2010 13:16:46 needed for thread
    1
    ORA-00289: suggestion : +ARCHIVE
    ORA-00280: change 2602636589 for thread 1 is in sequence #5709

    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    thread_1_seq_5709.528.735293603
    ORA-00308: cannot open archived log 'thread_1_seq_5709.528.735293603'
    ORA-27037: unable to obtain file status
    Linux-x86_64 Error: 2: No such file or directory
    Additional information: 3

    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    /orabk/aaa/thread_1_seq_5709.528.735293603
    ORA-00279: change 2602636589 generated at 11/14/2010 06:43:25 needed for thread
    2
    ORA-00289: suggestion : +ARCHIVE
    ORA-00280: change 2602636589 for thread 2 is in sequence #6914
     
    ORA-00289: suggestion : +ARCHIVE
    ORA-00280: change 2609694870 for thread 1 is in sequence #5824
    ORA-00278: log file '/orabk/aaa/arch11/1_5823_719402218.dbf' no longer needed
    for this recovery

    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    /orabk/aaa/arch11/1_5824_719402218.dbf
    ORA-00279: change 2609942602 generated at 11/16/2010 16:07:30 needed for thread
    2
    ORA-00289: suggestion : +ARCHIVE
    ORA-00280: change 2609942602 for thread 2 is in sequence #6990
    ORA-00278: log file '/orabk/aaa/arch22/2_6989_719402218.dbf' no longer needed
    for this recovery
    ........................省略中间部分
    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    /orabk/aaa/arch22/2_6990_719402218.dbf
    ORA-00279: change 2609955345 generated at 11/16/2010 16:08:25 needed for thread
    1
    ORA-00289: suggestion : +ARCHIVE
    ORA-00280: change 2609955345 for thread 1 is in sequence #5825
    ORA-00278: log file '/orabk/aaa/arch11/1_5824_719402218.dbf' no longer needed
    for this recovery

    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    /orabk/aaa/arch11/1_5825_719402218.dbf
    Log applied.
    Media recovery complete.
     
    10、打开数据库。
    SQL> alter database open resetlogs;
    经过几分钟的等待后,数据成功打开,登录数据库查询需要的文件都已经恢复出来了。谢天谢地!
     
     
    这次数据恢复操作,用了近1天的时间,当时发生问题时我都快搞懵了,幸好在朋友的帮助下顺利完数据恢复,在这里要非常感谢他们!顺便记录下恢复期间遇到的几个问题。
    <1>、有些日志文件在故障库的日志目录里是没有的,需要从备份中还原,不同节点的日志文件恢复时要写上thread=*命令,例如:
     
    RMAN>  restore archivelog from logseq 6914 until logseq 6915 thread=2;
    Starting restore at 17-NOV-10
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=182 instance=klir2 device type=DISK
    channel ORA_DISK_1: starting archived log restore to default destination
    channel ORA_DISK_1: restoring archived log
    archived log thread=2 sequence=6914
    channel ORA_DISK_1: reading from backup piece /orabk/arc_KLIR_13864_1_1_735184831.dbf
    channel ORA_DISK_1: piece handle=/orabk/arc_KLIR_13864_1_1_735184831.dbf tag=TAG20101116T020016
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
    channel ORA_DISK_1: starting archived log restore to default destination
    channel ORA_DISK_1: restoring archived log
    archived log thread=2 sequence=6915
    channel ORA_DISK_1: reading from backup piece /orabk/arc_KLIR_13866_1_1_735184956.dbf
    channel ORA_DISK_1: piece handle=/orabk/arc_KLIR_13866_1_1_735184956.dbf tag=TAG20101116T020016
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
    Finished restore at 17-NOV-10
     
    否则会提示以下错误,
    RMAN>  restore archivelog from logseq 6914 until logseq 6915;
    Starting restore at 17-NOV-10
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=182 instance=klir2 device type=DISK
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of restore command at 11/17/2010 08:15:07
    RMAN-20242: specification does not match any archived log in the repository
    <2>、故障库恢复出日志文件后,因为在asm磁盘组里,要进入asmcmd里拷贝出日志文件。
    ASMCMD> cp +ARCHIVE/klir/archivelog/2010_11_17/thread_1_seq_5705.369.735274973 /orabk/aaa/
     
    <3>、恢复命令要写对,刚开始写的几种命令都不正确。
    SQL> recover database until time '2010-11-16 16:25:00'
    ORA-00283: recovery session canceled due to errors
    ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
     
    SQL>  recover database until time to_date('2010-11-16 16:25:00','yyyy-mm-dd HH24:MI:SS');
    ORA-00285: TIME not given as a string constant
    <4>、对于这种只是丢失部分表的情况,就可以只还原需要的表空间来打开数据库,可以节省大量的恢复时间。
     


    本文转自 gjm008 51CTO博客,原文链接:http://blog.51cto.com/gaoshan/426326,如需转载请自行联系原作者
    展开全文
  • 通常情况下误删除数据后只要恢复备份数据即可,那么也不排除有一部分特殊情况下数据库备份无法使用、还原报错等,今天为大家介绍的是一例真是的oracle数据库误truncate table 的数据库恢复过程,如果出现误操作...
  • 使用坏境: oracle11.2+rhce5.5 RAC,归档模式,数据库有1TB容量。 在配置流环境时,因为一个误操作,删除了hz用户下的几个表。在这个数据库上还部署了其它几个用户,停机恢复数据显然是不可能的,分析情况可...
  • 从其他表中导数据时不小心选择了truncate table,执行了sql之后先truncate后又insert,现在使用fy_recover_data恢复不出来数据,prmdul显示该表没有被截断的数据。请问如何才能恢复truncate操作之前的数据呢?后面...
  • 但技术上因truncate不会产生日志记录和未生成回滚段,因此不能使用常规在线方式恢复,当然也不能用闪回恢复。常用的补救方法有:1、有备份的情况下可以用rman恢复,但是在生产业务库中,一般不能轻易停库,而且...
  • 电脑开机之后硬盘F盘保存的电影等大文件都找不到了,存放电影的文件夹是空,可能是我不小心误删了,然后我就疯狂找硬盘数据恢复方法想要恢复回来,经过我不懈的努力,尝试了很多恢复方法以及硬盘数据恢复软件,...
  • 效率上truncate比delete快,但truncate删除不记录mysql日志,不可以恢复数据。 其语法结构为: 代码如下: TRUNCATE [TABLE] tbl_name 这里简单的给出个示例, 我想删除 friends 表中所有的记录,可以使用如下语句...
  • 在生产中,极有可能遇到不小心truncate表的情况,truncate不会产生日志记录和回滚段空间的使用,不能用闪回恢复。尤其是在没有任何备份的情况下所以恢复起来相当麻烦,虽然在有备份的情况下是可以用rman恢复,...
  • oracle truncate恢复工具

    2016-04-18 13:30:09
    用户truncate误删 schema下的若干数据表,无法使用flashback query等技术恢复数据,尝试从之前的全备份中恢复,数据库restore速度较快,但是archivelog恢复时由于HP data Protecter的不明原因导致归档恢复十分缓慢,...
  • oracle truncate恢复

    2009-07-03 12:58:45
    使用ODU恢复Truncate表ODUmanual ODU3月 15th, 2009 意外Truncate表的事情时有发生,ODU提供了方便的恢复Truncate表的功能。被Truncate的表,只要原来的空间没有被重用(即数据被覆盖),则数据都是可以恢复的。 ...
  • 删除数据库表中的数据,效率上高于delete,后面不能加限制条件,加限制条件只能使用delete from [tablename] where `````` DELETE 语句每次删除一行,并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过...
  • 清空mysql表中数据 delete from 表名; truncate table 表名; 不带where参数的delete语句...效率上truncate比delete快,但truncate删除不记录mysql日志,不可以恢复数据。 delete的效果有点像将mysql表中所...
  • MySQL truncate

    2015-06-13 18:34:52
    truncate:清空数据表 delete from 表名; truncate table 表名; ...效率上truncate比delete快,但truncate删除不记录mysql日志,不可以恢复数据。 delete的效果有点像将mysql表中所有记录一条
  • 由于对于truncate命令没有回滚方法来还原,因此就需要对数据库进行恢复操作以将数据恢复回表中。 本文中将给出truncate命令恢复思路及步骤: RECOVER DATABASE UNTIL TIME 恢复步骤方案 注意: 在开始使用旧...
  • truncate delete 清空内容

    2016-05-25 13:19:01
    delete from 表名; truncate table 表名; 不带where参数的delete...效率上truncate比delete快,但truncate删除不记录mysql日志,不可以恢复数据。 delete的效果有点像将mysql表中所有记录一条一条删除到
  • 无论是用delete,还是truncate.并且发给我一个工具叫:Log Explorer for SQL Serverv.要使用log Exploer,有个前堤,数据库故障还原模型必须为:完全. 中午没事测试了一下。把log文件备份下来,将数据...
  • delete和truncate区别

    2017-03-28 21:15:00
    小心使用truncate,删除就没有了 1、delete : 删除"表格记录"会把操作记录在日志中,可以通过事务回滚来恢复删除的数据。  truncate :删除"表格记录"不可恢复 。 2、delete :每次删除一行,并在事务日志...
  • drop与truncate

    2019-10-06 19:57:50
    drop的表被放在回收站(user_recyclebin)里,而不是直接删除掉。这样,回收站里的表信息就可以被恢复,或彻底...将回收站里的表恢复为原名称或指定新名称,表中数据不会丢失。若要彻底删除表,则使用语句:drop ...
  • mysql---truncate--delete

    2018-05-17 14:35:30
    delete from 表名; truncate table 表名;...效率上truncate比delete快,但truncate删除不记录mysql日志,不可以恢复数据。 delete的效果有点像将mysql表中所有记录一条一条删除到删完, 而truncate...
  • 当表被truncate后,需要马上恢复。首先要做的就是关闭数据库所有应用,或者OFFLINE那个表所在的表空间。目的只有一个,确保空间不会被重用数据不会被覆盖。只要原来的空间没有被重用(即数据被覆盖),则数据都是...
  • Mysql中的truncate table和delete语句都...(1)truncate table删除速度更快,,但truncatetable删除不记录mysql日志,不可以恢复数据。(谨慎使用) (2)如果没有外键关联,innodb执行truncate是先drop table(...
  • 清空某个mysql表中所有内容 delete from 表名; truncate table 表名; ...不带where参数的delete语句...效率上truncate比delete快,但truncate删除不记录mysql日志,不可以恢复数据。 delete的效果有点像将my...

空空如也

空空如也

1 2 3 4 5 ... 9
收藏数 176
精华内容 70
关键字:

truncate使用后恢复数据