Home Database Mysql Tutorial oracle闪回表详解

oracle闪回表详解

Jun 07, 2016 pm 04:20 PM
oracle Detailed explanation

--- 说明闪回数据库 --- 使用闪回表将表内容还原到过去的特定时间点 --- 从删除表中进行恢复 --- 使用闪回查询查看截止到任一时间点的数据库内容 --- 使用闪回版本查询查看某一行在一段时间内的各个版本 --- 使用闪回事务查询查看事务处理历史记录 或行 优点

   --- 说明闪回数据库

  --- 使用闪回表将表内容还原到过去的特定时间点

  --- 从删除表中进行恢复

  --- 使用闪回查询查看截止到任一时间点的数据库内容

  --- 使用闪回版本查询查看某一行在一段时间内的各个版本

  --- 使用闪回事务查询查看事务处理历史记录

oracle闪回表详解   三联

或行

  优点:

  闪回技术由于只能处理更改数据,所以从根本上改变了恢复技术。使用这个技术时,从错误中恢复花费的时间等于制造错误所花费的时间。当闪回技术使用时,它与介质恢复相比,在易用性、可用性和还原时间方面有明显的优势。

  闪回数据库使用闪回日志执行闪回。闪回删除使用回收站。其他所有功能都使用还原数据。


  闪回数据库 -----这个功能可撤销导致逻辑数据损坏的更改

  就是当出现逻辑错误时,能够将整个数据库回退到出错前的那个时间点上,

  闪回数据库的日志文件不是由传统的Log Writer (LGWR)进程写入,而是由一个称为RecoVery WRiter (RVWR)的新进程写入,闪回日志文件由RCWR进程在恢复区中自动创建和维护。

  [Z喎�"" target="_blank" class="keylink">vcmFjbGVAdHlnZXIgfl0kIHBzIC1lZg=="grep rvwr|grep -v grep

  oracle 8414 1 0 14:33 ? 00:00:00 ora_rvwr_ORCL

  可以使用闪回数据库的场景包括

  1. 用户截断了表(trucate)

  2. 系统管理员误删除了用户

  3. 用户错误的执行了某个批处理任务,或者该批处理任务的脚本编写错误,使得多个表的数据发生混乱,我们无法采用闪回表的方式进行恢复

  实现闪回数据库的基础是闪回日志,只要我们配置了闪回数据库,就会自动创建闪回日志。这时,只要数据库里的数据发生变化,oracle就会将数据被修改前的旧值保存在闪回日志里,当我们需要闪回数据库时,oracle就会读取闪回日志里的记录,并应用到数据库上,从而将数据库回退到历史的某个时间点上。

oracle闪回表详解

  闪回数据库:减少还原时间

  闪回数据库比使用还原文件和重做日志文件的传统时间点恢复的速度要更快。随着数据库规模的增加,通过还原所有数据文件来执行传统的时间点恢复所需的时间长度变得不太现实。使用闪回数据库时,因为不要需要还原数据文件,所以恢复数据库的时间与需要回退的更改数目(而不是数据库大小)成比例。

  闪回数据库是通过使用一类被称为闪回数据库日志的日志文件来实施的。oracle数据库会定期将数据块的“前像”记录在闪回数据库日志中。为了快速将数据文件更改回退到捕获闪回日志的时间(就在所需目标时间之前),可以重用块前像。然后,应用重做日志文件中的更改来填充间隔。在快速恢复区中会自动创建和管理闪回数据库日志。

  在不能使用闪回数据库功能的情况下,必须使用不完全恢复操作将数据库返回到特定时间。闪回数据库操作完成后,可在只读模式下打开数据库,验证是否使用了正确的目标时间或系统更改号(SCN)。如果没有,可以再次闪回数据库,或者通过执行恢复操作来前滚数据库。因此,要撤销闪回数据库操作,必须向前恢复数据库。

  注:闪回保留目标并不能绝对保证闪回可用。如果闪回恢复区中必须存在的文件需要占用空间,可能会自动删除闪回日志。

  闪回数据库操作完成后,必须使用以下一种方法打开数据库:

  · 在只读模式下验证是否使用了正确的目标时间或SCN

  · 使用RESETLOGS 参数才允许进行更新

  闪回数据库限制:(不能使用闪回数据库)

  · 已还原或重新创建了控制文件

  · 已删除了表空间

  · 已收缩了数据文件

  不能使用闪回数据库恢复自闪回目标时间以来已删除的数据文件。

  删除的数据文件会添加到控制文件中且标记为脱机,但是不会闪回。

  闪回数据库不能闪回自闪回目标时间以来已收缩的数据文件,这种数据文件必须先脱机,才能执行闪回操作。

  实验1:启用闪回数据库

  1. 设置闪回恢复区保留时间3天

  SYS@ORCL>show parameter flashback

  NAME TYPE VALUE

  ------------------------------------ -------------------------------- ------------------------------

  db_flashback_retention_target integer 1440 //db_flashback_retention_target 用于定义一个时间上限 单位是分钟

  SYS@ORCL>alter system set db_flashback_retention_target=4320;

  SYS@ORCL>show parameter flashback

  NAME TYPE VALUE

  ------------------------------------ -------------------------------- ------------------------------

  db_flashback_retention_target integer 4320

  2. 查看当前闪回状态,未开启闪回,开启闪回(mount状态开启闪回)

  SYS@ORCL>select flashback_on from v$database;

  FLASHBACK_ON

  ------------------

  NO

  SYS@ORCL>alter database flashback on;

  alter database flashback on

  *

  ERROR at line 1:

  ORA-38759: Database must be mounted by only one instance and not open.

  SYS@ORCL>shutdown immediate

  Database closed.

  Database dismounted.

  ORACLE instance shut down.

  SYS@ORCL>startup mount;

  ORACLE instance started.

  Total System Global Area 314572800 bytes

  Fixed Size 1219160 bytes

  Variable Size 121636264 bytes

  Database Buffers 188743680 bytes

  Redo Buffers 2973696 bytes

  Database mounted.

  3. 开启闪回,必须在归档模式下开启

  SYS@ORCL>archive log list;

  Database log mode Archive Mode

  Automatic archival Enabled

  Archive destination USE_DB_RECOVERY_FILE_DEST

  Oldest online log sequence 29

  Next log sequence to archive 31

  Current log sequence 31

  SYS@ORCL>alter database flashback on;

  Database altered.

  4. 打开数据库,,查看闪回状态

  SYS@ORCL>alter database open;

  Database altered.

  SYS@ORCL>select flashback_on from v$database;

  FLASHBACK_ON

  ------------------

  YES

  SYS@ORCL>

  实验2:闪回数据库用途 -----实验参考eygle 的循序渐进oracle

  SYS@ORCL>conn tyger/tyger

  Connected.

  TYGER@ORCL>select count(*) from tyger;

  COUNT(*)

  ----------

  1

  TYGER@ORCL>select count(*) from test;

  COUNT(*)

  ----------

  14

  TYGER@ORCL>alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss';

  Session altered.

  TYGER@ORCL>select sysdate from dual;

  SYSDATE

  -------------------

  2014-03-11 15:47:14

  TYGER@ORCL>truncate table tyger;

  Table truncated.

  TYGER@ORCL>select sysdate from dual;

  SYSDATE

  -------------------

  2014-03-11 15:47:38

  TYGER@ORCL>truncate table test;

  Table truncated.

  TYGER@ORCL>select sysdate from dual;

  SYSDATE

  -------------------

  2014-03-11 15:48:03

  闪回需要在Mount状态下进行,可以指定Timestamp/SCN/Sequence进行闪回。

  首先将数据库闪回到第一时间点,以Redo only的方式打开数据库:

  SYS@ORCL>startup mount;

  ORACLE instance started.

  Total System Global Area 314572800 bytes

  Fixed Size 1219160 bytes

  Variable Size 130024872 bytes

  Database Buffers 180355072 bytes

  Redo Buffers 2973696 bytes

  Database mounted.

  SYS@ORCL>flashback database to timestamp

  2 to_timestamp('2014-03-11 15:47:14','yyyy-mm-dd hh24:mi:ss');

  Flashback complete.

  SYS@ORCL>alter database open read only; //以read only原因:如果数据恢复的不够理想,可以关闭数据库继续进行恢复

  Database altered.

  SYS@ORCL>select count(*) from tyger.tyger;

  COUNT(*)

  ----------

  1

  SYS@ORCL>select count(*) from tyger.test;

  COUNT(*)

  ----------

  14

  数据恢复不理想,继续进行恢复 ---前提:以redo only打开数据库

  SYS@ORCL>startup mount;

  ORACLE instance started.

  Total System Global Area 314572800 bytes

  Fixed Size 1219160 bytes

  Variable Size 130024872 bytes

  Database Buffers 180355072 bytes

  Redo Buffers 2973696 bytes

  Database mounted.

  SYS@ORCL>flashback database to timestamp

  2 to_timestamp('2014-03-11 15:47:38','yyyy-mm-dd hh24:mi:ss');

  Flashback complete.

  SYS@ORCL>alter database open read only;

  Database altered.

  SYS@ORCL>select count(*) from tyger.tyger;

  COUNT(*)

  ----------

  0

  SYS@ORCL>select count(*) from tyger.test;

  COUNT(*)

  ----------

  14

  如果数据恢复确认完成,就可以以resetlogs打开数据库,恢复工作 ------重置日志,不能再flashback至resetlogs之前的时间点

  SYS@ORCL>alter database open resetlogs;

  Database altered.

  oracle闪回表详解:

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How long will Oracle database logs be kept? How long will Oracle database logs be kept? May 10, 2024 am 03:27 AM

The retention period of Oracle database logs depends on the log type and configuration, including: Redo logs: determined by the maximum size configured with the "LOG_ARCHIVE_DEST" parameter. Archived redo logs: Determined by the maximum size configured by the "DB_RECOVERY_FILE_DEST_SIZE" parameter. Online redo logs: not archived, lost when the database is restarted, and the retention period is consistent with the instance running time. Audit log: Configured by the "AUDIT_TRAIL" parameter, retained for 30 days by default.

Function to calculate the number of days between two dates in oracle Function to calculate the number of days between two dates in oracle May 08, 2024 pm 07:45 PM

The function in Oracle to calculate the number of days between two dates is DATEDIFF(). The specific usage is as follows: Specify the time interval unit: interval (such as day, month, year) Specify two date values: date1 and date2DATEDIFF(interval, date1, date2) Return the difference in days

The order of the oracle database startup steps is The order of the oracle database startup steps is May 10, 2024 am 01:48 AM

The Oracle database startup sequence is: 1. Check the preconditions; 2. Start the listener; 3. Start the database instance; 4. Wait for the database to open; 5. Connect to the database; 6. Verify the database status; 7. Enable the service (if necessary ); 8. Test the connection.

How to use interval in oracle How to use interval in oracle May 08, 2024 pm 07:54 PM

The INTERVAL data type in Oracle is used to represent time intervals. The syntax is INTERVAL <precision> <unit>. You can use addition, subtraction, multiplication and division operations to operate INTERVAL, which is suitable for scenarios such as storing time data and calculating date differences.

How to determine whether two strings are contained in oracle How to determine whether two strings are contained in oracle May 08, 2024 pm 07:00 PM

In Oracle, you can use the nested INSTR function to determine whether a string contains two substrings at the same time: when INSTR(string1, string2a) is greater than 0 and INSTR(string1, string2b) is greater than 0, it is included; otherwise, it is not included.

How to see the number of occurrences of a certain character in Oracle How to see the number of occurrences of a certain character in Oracle May 09, 2024 pm 09:33 PM

To find the number of occurrences of a character in Oracle, perform the following steps: Get the total length of a string; Get the length of the substring in which a character occurs; Count the number of occurrences of a character by subtracting the substring length from the total length.

Oracle database server hardware configuration requirements Oracle database server hardware configuration requirements May 10, 2024 am 04:00 AM

Oracle database server hardware configuration requirements: Processor: multi-core, with a main frequency of at least 2.5 GHz. For large databases, 32 cores or more are recommended. Memory: At least 8GB for small databases, 16-64GB for medium sizes, up to 512GB or more for large databases or heavy workloads. Storage: SSD or NVMe disks, RAID arrays for redundancy and performance. Network: High-speed network (10GbE or higher), dedicated network card, low-latency network. Others: Stable power supply, redundant components, compatible operating system and software, heat dissipation and cooling system.

How much memory does oracle require? How much memory does oracle require? May 10, 2024 am 04:12 AM

The amount of memory required by Oracle depends on database size, activity level, and required performance level: for storing data buffers, index buffers, executing SQL statements, and managing the data dictionary cache. The exact amount is affected by database size, activity level, and required performance level. Best practices include setting the appropriate SGA size, sizing SGA components, using AMM, and monitoring memory usage.

See all articles