7.1 - oracle管理undo数据

第七课

7.1 管理undo数据

还原撤销undo数据:

oerr ora 01555 快照过旧
实例自动恢复：
1、前滚—应用redo
2、回滚—应用undo，回滚未提交事务
redo不能丢，undo也不能丢，否则数据库无法open

select * from v$transaction;

create table t_undo(id int,name varchar2(20));
insert into t_undo values(100,'oracle');
insert into t_undo values(200,'mysql');
insert into t_undo values(300,'postgresql');
commit;
update t_undo set name='oracleB' where id=100;
 
alter system flush buffer_cache;
alter system dump datafile 4 block 1423;（dump指定的块）
cd /u01/app/oracle/oradata/prod/（定位到数据文件位置）
dd if=undotbs01.dbf of=a.dbf bs=8192 count=1 skip=1423 conv=notrunc

查看dump块位置：select * from v$diag_info;
select * from v$transaction;

事务分配的回滚段:
select * from v$rollname

undo与redo:

管理undo:

参数:undo_management=AUTO（undo自动管理参数）
undo_retention，默认900s（undo默认有效时间15分钟）
undo分类:

未提交的还原信息(活动)
提交的还原信息(未过期)
提交的还原信息(过期)

配置还原保留时间:
参数:undo_retention

保证还原保留时间:

alter tablespace undotbs1 retention guarantee;（设置为还原保证）
alter tablespace undotbs1 retention noguarantee;
dba_tablespaces（视图）

默认还原保留时间为15分钟

临时undo:
12c新特性

在12c 之前，临时表生成的 Undo 记录是存储在 Undo 表空间 和 Redo 日志文件中的，非临时表的 undo 记录也是存储在 Undo 表空间中的，从12c开始 ，临时 Undo 记录可以存储在 临时表空间中，且不再记录到 Redo 日志文件中。
减少Undo表空间的使用，且减少了Redo数据的生成。

使用临时表作为暂存区来存放中间结果。临时表不会产生redo，但临时表产生的undo数据会记录在redo中。

临时 Undo优点:
临时 Undo 可减少 Undo 表空间中存储的 Undo 数据量。
临时 Undo 可减小重做日志的大小。
临时 Undo 支持在物理备库dataguard对临时表进行DML操作。

参数: temp_undo_enabled
alter system|session set temp_undo_enabled=true;
select * from v$tempundostat;

EMDE:

还原指导:

题库解析:
Q1. You are administering a database that supports an OLTP workload. The default UNDO tablespace is a fixed size tablespace with the RETENTION NOGUARATNEE clause and undo retention set to 12 minutes.
The user SCOTT queries a large table during peak activity. The query runs for more than 15 minutes and then SCOTT receives the following error:
ORA-01555: snapshot too old
Which is possible reason for this?
A. The Oracle server is unable to generate a read-consistent image for a block containing updates.committed after the query began.
B. The query is unable to place data blocks in the UNDO tablespace.
C. The flashback data archive is not enabled for the table on which the query is issued.
D. There is not enough space in Fast Recovery Area.
E. The Oracle server is unable to generate a read-consistent image for a block containing uncommitted updates.
Answer: A

Q2. When is the UNDO_RETENTION parameter value ignored by a transaction?
A. when there are multiple undo tablespaces available in a database
B. when the data file of the undo tablespace is autoextensible
C. when the undo tablespace is of a fixed size and retention guarantee is not enabled
D. when Flashback Database is enabled
Answer: C

Q3. In your database instance, the UNDO_RETENTION parameter is set to 1000 and undo retention is not guaranteed for the fixed size undo tablespace.
Which statement is true about undo retention?
A. Undo is retained in the UNDO tablespace for 1000 seconds, and then moved to the SYSTEM tablespace to provide read consistency.
B. Inactive undo is retained for at least 1000 seconds if free undo space is available.
C. Inactive undo is retained for 1000 seconds even if new transactions fall due to lack of space in the undo tablespace.
D. Undo becomes expired obsolete after 1000 seconds.
Answer: B

Q4. Examine the parameters for a database instance:

Your database has three undo tablespaces and the default undo tablespace is not autoextensible. Resumable space allocation is not enabled for any sessions in the database instance. What is the effect on new transactions when all undo space in the default undo tablespace is in use by active transactions?
A. Transactions write their undo in the SYSTEM undo segment.
B. Transactions fail.
C. Transactions wait until space becomes available in UNDOTBS1.
D. Transactions write their undo in a temporary tablespace.
Answer: B