网站建设企业响应式网站模板,广西建设部投诉网站,广州网站优,平台类网站营销方案1. 概述
1.1. Undo 数据应用
undo数据是#xff1a;
原始的、修改之前的数据副本
是针对更改数据的每个事务处理所捕获的
至少保留到事务处理结束 用于支持#xff1a;
回退操作
读取一致性查询
闪回查询、闪回事务处理和闪回表
从失败的事务处理中进行恢复 1.2. 事…1. 概述
1.1. Undo 数据应用
undo数据是
原始的、修改之前的数据副本
是针对更改数据的每个事务处理所捕获的
至少保留到事务处理结束 用于支持
回退操作
读取一致性查询
闪回查询、闪回事务处理和闪回表
从失败的事务处理中进行恢复 1.2. 事务和 undo 数据 1.3. undo管理方式与新特性 1.4. undo的状态--EXPIRE过期
Undo段里数据三种状态
ACTIVE未提交的数据UNEXPIRE已提交未超过undo retention保留时间EXPIRE已提交超过undo_retention保留时间 2. 设置 CDB 使用 Shared Undo Mode
2.1. 案例需求
12cR2 版本中DBCA 建库时默认勾选 Use Local Undo tablespace for PDBsCDB 中每个容器使用本地的 UNDO 表空间。
查看当前 undo 模式设置 CDB 使用 shared undo mode即 CDB 中所有容器共享使用一个 UNDO 表空间。 2.2. 启动数据实例
[oracleenmoedu1 ~]$ export ORACLE_SIDcdb1
[oracleenmoedu1 ~]$ sqlplus / as sysdba
SYScdb1 startup
SYScdb1 show pdbs 2.3. 启动所有 PDB
SYScdb1 show pdbs
SYScdb1 alter pluggable database all open;
SYScdb1 show pdbs 2.4. 查看 CDB 数据库中所有 undo 表空间
SYScdb1 select con_id,TABLESPACE_NAME,CONTENTS
from cdb_tablespaces
where contentsUNDO;查看 undo 表空间对应数据文件
SYScdb1 col FILE_NAME for a60
select con_id,tablespace_name,file_name
from cdb_data_files
where tablespace_nameUNDOTBS1; 2.5. 查看 undo 相关参数
SYScdb1 col name for a20
col value for a20
select con_id,NAME,VALUE,ISINSTANCE_MODIFIABLE, ISPDB_MODIFIABLE
from v$parameter
where name
like undo%; 2.6. 查看 CDB 当前 undo 模式
SYScdb1
col property_name for a20
col PROPERTY_VALUE for a20
select PROPERTY_NAME,PROPERTY_VALUE
from database_properties
where ROPERTY_NAMELOCAL_UNDO_ENABLED; 当前环境使用 12cR2 版本DBCA 创建 cdb1 时默认勾选了 Use Local Undo tablespace for PDBs 2.7. 设置 CDB 使用 Shared Undo Mode
关闭实例startup upgrade 启动实例
SYScdb1 shutdown immediate;
SYScdb1 startup upgrade;
SYScdb1 alter pluggable database all open;
SYScdb1 show pdbs; 2.8. 查看 cdb1 当前 undo 模式
SYScdb1
col property_name for a20
col PROPERTY_VALUE for a20
select PROPERTY_NAME,PROPERTY_VALUE
from database_properties
where ROPERTY_NAMELOCAL_UNDO_ENABLED; 当前使用 Shared Undo ModeCDB 中所有容器共享使用一个 Undo 表空间 2.9. 删除 pdb1、pdb2 本地 undo 表空间
共享 undo 模式下oracle 忽略 PDB 本地的 undo 表空间。
可以删除 PDB 本地的 undo 表空间及数据文件。
[oracleenmoedu1 ~]$ sqlplus sys/oraclepdb1 as sysdba
col file_name for a60
select tablespace_name,file_name from dba_data_files;
SYSpdb1 drop tablespace undotbs1 including contents and datafiles;
SYSpdb1 select tablespace_name,file_name from dba_data_files;
[oracleenmoedu1 ~]$ sqlplus sys/oraclepdb2 as sysdba
SYSpdb2 drop tablespace undotbs1 including contents and datafiles;
SYSpdb2
col file_name for a60
select tablespace_name,file_name from dba_data_files; 2.10. 查看 CDB 数据库中所有 undo 表空间
查看所有容器中 undo 表空间
SYScdb1 select con_id,TABLESPACE_NAME,CONTENTS
from cdb_tablespaces
where contentsUNDO; 查看 undo 表空间对应数据文件
SYScdb1 col FILE_NAME for a60
select con_id,tablespace_name,file_name
from cdb_data_files
where tablespace_nameUNDOTBS1; 2.11. 关闭 pdb2
后续实验在根容器和 pdb1 中进行操作
SYScdb1 alter pluggable database pdb2 close immediate;
show pdbs
3. Undo 数据的应用
3.1. pdb1 创建测试表
[oracleenmoedu1 ~]$ sqlplus sys/oraclepdb1 as sysdba
SYSpdb1
create table test1 (id number,name char(20));
insert into test1 values (1,Oracle);
insert into test1 values (2,DBA);
commit;SYSpdb1 select current_scn from v$database; 3.2. pdb1 session 1 执行查询
SYSpdb1
variable rfc refcursor;
execute open :rfc for select * from test1;
模拟开始查询记录查询开始时的 SCN,但未访问具体数据行 3.3. pdb1 新建会话 session2 更新 test1 表数据
新开会话 session2 登录连接 pdb1
[oracleenmoedu1 ~]$ sqlplus sys/oraclepdb1 as sysdba
SYSpdb1
select * from test1;
update test1 set nameOCM where id2;
select * from test1; 3.4. pdb1 新建会话 session3 查看
[oracleenmoedu1 ~]$ sqlplus sys/oraclepdb1 as sysdba
SYSpdb1 select * from test1; session2 未提交其他会话看不到 session2 做出的修改session3 查询显示的是修改之前
的状态id2 的 NAME 字段值“DBA”来自 undo 数据。
未提交的事务对应的 active 状态的 undo 数据用于支持 rollback 回滚操作、避免脏读。 3.5. session2 commit 提交
SYSpdb1 commit; 3.6. session 3 再次查看
session2 提交以后session3 查看显示是更新之后的结果。 3.7. session 1 查看查询结果
SYSpdb1 print :rfc
尽管 session2 事务已经提交session1 查询返回结果与查询开始时状态保持一致id2 的name 列值 DBA 来自于 undo 数据。 3.8. session1 闪回查询
SYSpdb1 select * from test1;
SYSpdb1 select * from test1 as of scn 1605491; 已经提交的事务对应的 undo 数据仍然保留一段时间未超出 undo_retention 时间的 unexpired 状态的 undo 数据用于支持长时间执行的查询语句的一致性读以及部分闪回操作
闪回查询、闪回表等 4. ORA-01555 错误
4.1. 案例需求
通过设置比较小的固定大小的 undo 表空间模拟长时间执行的 SQL 查询语句执行过程中其他会话更新表中数据并提交由于大量并发事务造成 undo 数据被覆盖导致当前查询语句无法正常返回一致性读的结果发生 ORA-01555 错误。 4.2. CDB 查看当前 undo 设置
SYScdb1 show parameter undo SYScdb1
select con_id,tablespace_name,file_name,bytes/1024/1024 MB,AUTOEXTENSIBLE
from cdb_data_files
where tablespace_nameUNDOTBS1; 4.3. cdb 创建新的 undo 表空间
创建一个非常小的固定大小的 undo 表空间数据文件不自动扩展
SYScdb1 create undo tablespace smallundo datafile /u01/app/oracle/oradata/cdb1/smallundo01.dbf size 2m autoextend off;
SYScdb1 select con_id,tablespace_name,file_name, bytes/1024/1024 MB,AUTOEXTENSIBLE
from cdb_data_files
where tablespace_name
like %UNDO%; 4.4. cdb 切换 undo 表空间
SYScdb1 alter system set undo_tablespaceSMALLUNDO;
SYScdb1 show parameter undo 4.5. pdb1 session1 执行查询
session1 查看当前 test1 表中数据
SYSpdb1 select * from test1;
session1 查看当前 scn
SYSpdb1 select current_scn from v$database;
session1 模拟开始查询
SYSpdb1
var rfc refcursor
exec open :rfc for select * from test1 4.6. pdb1 session2 执行循环更新操作
session2 循环更新 test1 表中数据模拟 undo 数据被覆盖
SYSpdb1
begin
for i in 1..20000 loop
update test1 set nameOracle;
commit;
end loop;
end;
/ 4.7. pdb1 session3 查看查询结果
session2 事务在循环更新过程中已经提交session3 查看到的是提交后的结果。 4.8. pdb1 session1 查看查询结果
SYSpdb1 print :rfc 4.9. pdb1 session1 闪回查询
SYSpdb1 select * from test1 as of scn 1608613;
session1 再次查询
SYSpdb1 select * from test1; 4.10. ORA-01555 4.11. 查看 undo 表空间属性
SYScdb1 select tablespace_name,RETENTION
from dba_tablespaces
where tablespace_nameSMALLUNDO; SYScdb1 show parameter undo 4.12. undo_retention
undo_retention 参数指定了提交后的 undo 数据unexpired保留多少秒。
当前环境中设置了 undo_retention900默认 undo 表空间未设置 guarantee。 当使用固定大小的 undo 表空间时undo_retention 值被忽略数据库根据 undo 表空间
大小和系统负载情况动态调整优化 retention 值。v$undostat 视图可以查看动态调整后的
retention 值 。 v$undostat 视图每 10 分 钟 生 成 一 行 记 录 当 前 时 间 对 应 行 的
tuned_undoretention 值会随事务繁忙程度变化而更新事务越繁忙优化后的 retention 值越小。 SYScdb1 select to_char(begin_time, yyyy-mm-dd hh24:mi) begin_time,
to_char(end_time, yyyy-mm-dd hh24:mi) end_time, tuned_undoretention
from v$undostat order by end_time; 上图查询结果中两行数据第一行显示结果是切换使用固定大小的 undo 表空间后没有
事务操作时tuned_undoretention 值为 1336第二行显示结果是 session2 进行循环更新操作
后tuned_undoretention 值为 41。 undo 表空间太小且不支持数据文件自动扩展时大量并发事务操作会使 unexpired 状态
的 undo 数据被覆盖进而导致长时间执行的查询语句报错 snapshot too old(ORA-01555). 5. ORA-30036 错误
5.1. 设置 retention guarantee
SYScdb1 select tablespace_name,contents,RETENTION
from cdb_tablespaces
where tablespace_nameSMALLUNDO; 设置当前使用的 undo 表空间 retention guarantee 属性
SYScdb1 alter tablespace smallundo retention guarantee;
SYScdb1 select tablespace_name,contents,RETENTION
from cdb_tablespaces
where tablespace_nameSMALLUNDO; 5.2. pdb1 session1 执行查询
SYSpdb1 select * from test1;
SYSpdb1 select current_scn from v$database;
SYSpdb1
var rfc refcursor
exec open :rfc for select * from test1 5.3. pdb1 session2 执行循环更新操作
SYSpdb1
begin
for i in 1..20000 loop
update test1 set name12C OCM;
commit;
end loop;
end;
/ 5.4. pdb1 session3 查看结果
SYSpdb1 select * from test1; 5.5. pdb1 session1 查看查询结果
SYSpdb1 print :rfc 5.6. pdb1 session1 闪回查询
session1 没有报 ORA-01555 错误查询结果与查询开始时状态保持一致。
session1 闪回查询
SYSpdb1 select * from test1 as of scn 1644658;
session1 再次查询
SYSpdb1 select * from test1; 5.7. ORA-30036
SYSpdb1 ! oerr ora 30036 当前设置了 undo_retention900 并且设置了 undo 表空间 guarantee 属性。强制保证
unexpired 状态的 undo 数据在 900s 保留期内不被覆盖。session1 最终能够正常返回结果满足 一致性读。session2 并发事务操作则由于 undo 空间不足而报错 ORA-30036。 设置 undo 表空间的 guarantee 属性后查看 tuned_undoretention 值
SYScdb1 select to_char(begin_time, yyyy-mm-dd hh24:mi) begin_time, to_char(end_time, yyyy-mm-dd hh24:mi) end_time, tuned_undoretention
from v$undostat
order by end_time; 5.8. 取消 guarantee 属性
SYScdb1 alter tablespace smallundo retention noguarantee;
SYScdb1 select tablespace_name,contents,RETENTION
from cdb_tablespaces
where tablespace_nameSMALLUNDO; 5.9. resize 调整 undo 表空间大小
SYScdb1 alter database datafile 26 resize 10m;
SYScdb1 select con_id,tablespace_name,file_id,file_name, bytes/1024/1024 MB,AUTOEXTENSIBLE
from cdb_data_files
where tablespace_nameSMALLUNDO; 5.10. pdb1 创建测试表模拟事务操作
SYSpdb1
create table test2 as select * from dba_objects;
insert into test2 select * from test2;
insert into test2 select * from test2;
insert into test2 select * from test2;
insert into test2 select * from test2;SYSpdb1 select count(*) from test2;
delete from test2;
commit; test2 表中插入 100 多万行数据未提交尝试删除所有数据时 undo 空间不足报错。 当前已经取消 undo 表空间 guarantee 属性但由于使用了固定大小的 undo 表空间大
事务操作也可能会因undo空间不足而报错 ORA-30036。同时由于事务越繁忙优化后的 undo
保留期值越小仍然有可能报 ORA-01555 错误。 SYScdb1 select to_char(begin_time, yyyy-mm-dd hh24:mi) begin_time, to_char(end_time, yyyy-mm-dd hh24:mi) end_time, tuned_undoretention
from v$undostat
order by end_time; 6. Undo 表空间数据文件自动扩展
6.1. 开启 undo 表空间自动扩展
SYScdb1 select con_id,tablespace_name,file_id,file_name, bytes/1024/1024 MB,AUTOEXTENSIBLE from cdb_data_files where tablespace_nameSMALLUNDO;开启 undo 表空间数据文件自动扩展
SYScdb1 alter database datafile 26 autoextend on;
SYScdb1 select con_id,tablespace_name,file_id,file_name, bytes/1024/1024 MB,AUTOEXTENSIBLE from cdb_data_files where tablespace_nameSMALLUNDO;6.2. pdb1 session1 执行查询
SYSpdb1 select * from test1;
SYSpdb1 select current_scn from v$database;
SYSpdb1
var rfc refcursor
exec open :rfc for select * from test1 6.3. pdb1 session2 执行循环更新操作
SYSpdb1
begin
for i in 1..20000 loop
update test1 set nameOracle DBA;
commit;
end loop;
end;
/ 更新成功没有报 ORA-30036 错误。 6.4. pdb1 session3 查看结果
SYSpdb1 select * from test1; 6.5. pdb1 session1 查看查询结果
SYSpdb1 print :rfc
查询没有报 ORA-01555 错误session1 查询结果与开始查询时状态保持一致。 6.6. pdb1 session1 闪回查询
session1 闪回查询
SYSpdb1 select * from test1 as of scn 1650833;
session1 再次查看
SYSpdb1 select * from test1; 6.7. 查看当前 undo 表空间数据文件大小
SYScdb1 select con_id,tablespace_name,file_id,file_name, bytes/1024/1024 MB,AUTOEXTENSIBLE
from cdb_data_files
here tablespace_nameSMALLUNDO; 当前设置了 undo_retention900, undo 表空间使用默认的 noguarantee 属性。 undo 表空间数据文件设置为 autoextend on 时数据库以当前设置的 undo_retention 参
数值作为下限值并会动态调整 retention 值来满足长时间执行查询语句的一致性读当 undo
空间不足时数据库会扩展 undo 表空间对应数据文件。 SYScdb1 select to_char(begin_time, yyyy-mm-dd hh24:mi) begin_time, to_char(end_time, yyyy-mm-dd hh24:mi) end_time, tuned_undoretention
from v$undostat
order by end_time; 如果 undo 表空间数据文件自动扩展达到 maxsize 最大值,数据库仍然会开始覆盖unexpired 状态的 undo 数据。
7. 设置 CDB 使用 Local Undo Mode
7.1. 案例需求
设置当前 CDB 使用 12.2 版本默认的 Local Undo Mode 即 CDB 中每个容器使用本地
的 undo 表空间。 7.2. 查看所有容器中 undo 表空间
SYScdb1 select con_id,TABLESPACE_NAME,CONTENTS from cdb_tablespaces where contentsUNDO; 7.3. 查看 CDB 当前 undo 模式
SYScdb1
col property_name for a20
col PROPERTY_VALUE for a20
select PROPERTY_NAME,PROPERTY_VALUE from database_properties where ROPERTY_NAMELOCAL_UNDO_ENABLED; 7.4. 设置 Local Undo Mode
7.4.1.1. 关闭数据库实例
SYScdb1 shutdown immediate;
7.4.1. startup upgrade 启动实例
SYScdb1 startup upgrade;
7.4.2. 设置 undo 模式
SYScdb1 ALTER DATABASE LOCAL UNDO ON;
7.4.3. 重启实例
SYScdb1 shutdown immediate;
SYScdb1 startup 7.5. 查看 CDB 当前 undo 模式
SYScdb1
col property_name for a20
col PROPERTY_VALUE for a20
select PROPERTY_NAME,PROPERTY_VALUE from database_properties where ROPERTY_NAMELOCAL_UNDO_ENABLED; 7.6. 启动 PDB
SYScdb1 alter pluggable database all open;
SYScdb1 show pdbs 7.7. 查看所有容器中 undo 参数
SYScdb1 show parameter undo SYSpdb1 show parameter undo 7.8. 查看 CDB 数据库中所有 undo 表空间
查看所有容器中 undo 表空间
SYScdb1 select con_id,TABLESPACE_NAME,CONTENTS from cdb_tablespaces where contentsUNDO; 查看 undo 表空间对应数据文件
SYScdb1
col FILE_NAME for a60
select con_id,tablespace_name,file_name from cdb_data_files where tablespace_name like %UNDO%;
设置 CDB 使用 Local Undo Mode 后数据库在每个 PDB 中创建了本地的 undo 表空间。
8. 12cR1 Temporary Undo
8.1. redo 与 undo 8.2. 临时表 8.3. 12cR1 Temporary Undo 文章主要内容摘抄墨天轮恩墨大讲堂《重现ORA-01555 细说Oracle Undo 数据管理》。
