精华内容
下载资源
问答
  • 实际线上的场景比较复杂,当时涉及了truncate, delete 两个操作,经确认丢数据差不多7万多行,等停下来时,差不多又有共计1万多行数据写入。 这里为了简单说明,只拿弄一个简单的业务场景举例。 测试环境: Percona-...
  • ORACLE truncate 数据恢复

    2019-01-16 12:12:37
    因自己前段时间因truncate一张表无法找回,且数据超过60W,平常的企业软件超过1W收费,故整理此内容,希望和我遇到同样问题的新手有帮助
  • oracle truncate恢复工具

    2016-04-18 13:30:09
    注意这边文章针对的是PRM在 数据字典模式下的Truncate恢复选项不可用时使用数据字典模式下的Truncate恢复选项是最简单、易用的一种模式,具体使用见《使用PRM恢复Oracle数据库中误truncate截断的表数据》...
  • NULL 博文链接:https://steve-111.iteye.com/blog/750326
  • 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

    展开全文
  • 一次truncate table 数据恢复记录

    千次阅读 2017-11-27 16:22:00
    这次的数据恢复操作我我使用第一种方式,以下是操作步骤: 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,如需转载请自行联系原作者
    展开全文
  • 可能有一些数据库知识的应该会知道,在正常逻辑中,如果我们将表truncate掉了,是找不回来了,TRUNCATE TABLE 是一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存,删除行是不能恢复的。...

    在我的另外几篇文章中,介绍了关于数据库闪回的一些内容,对于drop和delete的数据闪回。对于truncate,可能有一些数据库知识的应该会知道,在正常逻辑中,如果我们将表truncate掉了,是找不回来了,TRUNCATE TABLE 是一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存,删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
    那么问题来了,如果我们生产过程中,一不小心,将业务表truncate掉了该怎么办呢?其实也是有办法解决的,具体操作过程如下:

    1,新建测试表t_test,插入几条测试数据
    在这里插入图片描述
    2,执行我们truncate命令,清除t_test中的所有数据
    在这里插入图片描述
    3,truncate恢复需要一个存储过程fy_recover_data(我个人觉得,能写出这种存储过程的,怕真的是科学家级别的了)
    下载地址:https://download.csdn.net/download/xxbb0101/10275209

    4,下载完成后,使用sys用户登录db(注意一定要sys用户登录,否则会出现执行报错的情况),然后执行存储过程

    5,在command界面,登录sys用户,执行命令:exec fy_recover_data.recover_truncated_table('userName','tableName');
    注意该程序有两个参数,前一个为被truncate表所在用户的用户名,后者为表名
    在这里插入图片描述
    4,用被普通用户登录,查询临时表:select * from tablename$$;(临时表名为表名+“$$”)
    可见,被truncate掉的数据已经全部在我们的临时表中了
    在这里插入图片描述
    5,最后将临时表的数据全部插入的业务表即可,你看业务表数据,已经被全部恢复了
    在这里插入图片描述
    6,到此虽然我们数据恢复工作已经做完了,但是由于其恢复需要生成临时表文件,所以我们要找到其对应的表空间文件,做相关删除操作,可以看到生成的临时表空间为;FY_RST_DATA
    在这里插入图片描述
    7,根据数据字典DBA_DATA_FILES,查出该表空间对应的磁盘文件
    在这里插入图片描述
    8,在磁盘上找到对应的文件,rm即可
    在这里插入图片描述

    到此为止,整个truncate数据恢复操作就算全部完成了!希望对大家有所帮助

    展开全文
  • 概述:北京某国企客户 Oracle 11g R2 数据库误truncate table CM_CHECK_ITEM_HIS,表数据丢失,业务查询到该表时报错,此时发现数据库的备份不能用,表数据无法查询。 客户Oracle 11g R2 数据库误truncate table CM_...

    概述:北京某国企客户 Oracle 11g R2 数据库误truncate table CM_CHECK_ITEM_HIS,表数据丢失,业务查询到该表时报错,此时发现数据库的备份不能用,表数据无法查询。

    客户Oracle 11g R2 数据库误truncate table CM_CHECK_ITEM_HIS,表数据丢失,业务查询到该表时报错,此时发现数据库的备份不能用,表数据无法查询。
    Truncate数据的机理是,ORACLE会在数据字典和Segment Header中更新表的Data Object ID,而实际数据部分的块则不会做修改。由于数据字典与段头的DATA_OBJECT_ID与后续的数据块中的并不一致,所以ORACLE服务进程在读取全表数据时不会读取到已经被TRUNCATE的记录,但是实际仍未被覆盖的数据。

    数据恢复过程

    为保护用户数据,本文档构造与客户相同的故障。Scott用户创建表emp1,复制emp表,连续复制多次,总记录数为:7340032条。truncate表emp1,之后没有进行任何操作。查询该表,数据库中该表的记录为0条。
    图一:
    Oracle数据库truncate table后恢复原来数据的方法
    注: Os:win server 2008R2。数据库版本:win_oracle_11.2.0.1_x64;
    1 对system表空间文件进行分析,找到truncate表的原始数据所在的位置
    图二:
    Oracle数据库truncate table后恢复原来数据的方法
    Oracle数据库truncate table后恢复原来数据的方法
    Oracle数据库truncate table后恢复原来数据的方法
    Oracle数据库truncate table后恢复原来数据的方法
    2 解析表所在的数据文件数据库,找到truncate的数据
    3 将truncate的数据库插入到数据库中

    数据恢复结果

    通过解析system01.dbf文件,找到truncate的数据所在的位置,找到被删除的数据。解析表所在的数据文件,将truncate的数据插入到数据库中。
    在数据库中,查找被truncate,发现数据回来了,直接备份数据。
    图三:
    Oracle数据库truncate table后恢复原来数据的方法
    Exp导出scott用户;
    图四:
    Oracle数据库truncate table后恢复原来数据的方法

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

    千次阅读 2016-04-17 20:43:01
    truncate后恢复方法总结 1.1...
  • ORACLE数据truncate、delete删除恢复

    千次阅读 2017-11-15 10:39:56
    1、DELETE删除。根据快照快速恢复删除的数据 一、select *from 表名 as ...2、truncate 表删除恢复,注意:根据此方法回滚的表,在truncate后,不能insert新数据插入 1、根据Fy_Recover_data恢复truncate数据 2、网
  • 故障检测 误操作导致表被 truncate,数据丢失需要恢复.但是后续插入了新纪录导致覆盖. 修复结果 直接从磁盘提取客户需要的表数据完成恢复,数据恢复率达99% 客户非常满意 分享以下解密 网址供大家参考希望可以帮到您 ...
  • #在数据库可视化工具中,通过以下两条语句,查询mysql操作日志,查找最近两次truncate清理表数据的节点,准备恢复两次truncate之间新建的数据,并新建备份数据库和表,表结构要和原数据库表结构一致 show binary ...
  • truncate恢复

    2018-03-13 15:47:16
    truncate恢复,包含了相应的操作步骤以及脚本。遇到紧急情况,很实用哦!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
  • 恢复truncate删除的表

    千次阅读 2019-12-23 21:49:46
    当然可以,而且是完全恢复:http://blog.sina.com.cn/s/blog_61cd89f60102ed97.html,但是truncate掉的一张表,数据还可以恢复吗?也是可以的,如果恢复及时也可以做到100%完全恢复,具体方法如下: 恢复原理: ...
  • 是一次数据恢复中,我们老大在Sql中敲的,我这里最想记录的是他优良的代码风格.
  • 在生产中,极有可能遇到不小心truncate表的情况,truncate不会产生日志记录和回滚段空间的使用,不能用闪回恢复。尤其是在没有任何备份的情况下所以恢复起来相当麻烦,虽然在有备份的情况下是可以用rman恢复,...
  • Truncate 不会逐个清除用户数据块上的数据,而仅仅重置数据字典和元数据块上的...也就是说,此时,基本数据表并未破坏,而是被系统回收,等待被重新分配—因此,要恢复truncate数据,需要及时备份所在的数据文件。
  • 此类是不完全恢复类型,基于时间点恢复恢复后,应该再次进行一次全备.
  • Oracle 数据库误truncate table恢复过程

    万次阅读 2017-01-06 14:42:31
    北京某国企客户 Oracle 11g R2 数据库误truncate table CM_CHECK_ITEM_HIS,表数据丢失,业务查询到该表时报错,此时发现数据库的备份不能用,表数据无法查询。  客户Oracle 11g R2 数据库误truncate table CM_...
  • 非DBA人员也可以恢复truncate掉的数据了。。。哈哈 借着本人犯点错误的机会,连错数据库,truncate掉了10张本不想删除的数据 :o (还好此次错误没照成什么影响,自己负责的小系统刚运行个把月,客户没当回事未...
  • 效率上truncate比delete快,但truncate删除不记录mysql日志,不可以恢复数据。 其语法结构为: 代码如下: TRUNCATE [TABLE] tbl_name 这里简单的给出个示例, 我想删除 friends 表中所有的记录,可以使用如下语句...
  • Truncate 表之恢复

    千次阅读 2017-02-08 09:06:16
    Truncate 表之恢复 1. 测试环境: Linux:172.28.145.21 ...也就是说,此时,基本数据表并未破坏,而是被系统回收,等待被重新分配—因此,要恢复truncate数据,需要及时备份所在的数据文件
  • truncate table发生时,如何进行恢复,相信大部分人都会选择通过还原备份到truncate table前,然后将数据重新导入正式表中。那么在SQL Server中是不是真的只有这种方法呢,当然不是,这也是本文即将介绍的内容,...
  • truncate table 误删除数据后恢复

    千次阅读 2011-08-17 23:26:46
    晚上吃饭时间,接到软件部开发组电话,用truncate table方式误删除生产库上几个表的数据,问能不能恢复。因为truncate table 是ddl操作,没有写日志到redo,基于事物的闪回不太...
  • 概述:北京某国企客户 Oracle 11g R2 数据库误truncate table CM_CHECK_ITEM_HIS,表数据丢失,业务查询到该表时报错,此时发现数据库的备份不能用,表数据无法查询。 客户Oracle 11g R2 数据库误truncate table CM...
  • 大家好,做下自我介绍~“rescureora”是一批Oracle技术的爱好者和实践者,我们热衷于技术原创和技术分享。...在上一篇《TRUNCATE TABLE恢复系列一:深层剖析内部原理》中说道,truncate table ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 36,223
精华内容 14,489
关键字:

truncate使用后恢复数据