##########################################
redo:用来保障在故障时事务可以被恢复。
undo:保障事务可以被回滚或者撤销。
redo
redo的功能主要有3个组件来实现:redo log buffer,lgwr后台进程,redo log file(在归档模式下,redo log file最终会由ARCn进程写为归档日志文件)。
redo log buffer位于SGA中,是一块循环使用的内存区域。oracle会通过LGWR进程不断的把redo log buffer的内容写出到redo log file中。
查看默认日志组:
日志视图:
desc v$log;
Name Null? Type
----------------- -------- ------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
BLOCKSIZE NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
NEXT_CHANGE# NUMBER
NEXT_TIME DATE
select group#,members from v$log;
select a.group#,a.bytes,a.blocksize,a.status,b.member from v$log a join v$logfile b on a.group#=b.group#;
默认创建三个日志组
1.current:活动状态,当前正在被使用。
2.active:日志是活动的非当前日志,该日志可能已经完成归档也可能没有完成归档。
3.inactive:是非活动日志,该日志在实例恢复时不需要,在介质恢复时可能用到。INACTIVE状态的日志可能已经完成归档也可能没有完成归档。在归档模式下,未完成归档之前,日志文件不允许被覆盖。
4.unused:该日志未被写入,这类日志可能刚添加到数据库或者resetlog之后被重置。
查看是否有log file switch等待时间
select sid,event,state from v$session_wait where event like '%log%';
log file switch的多个原因:
(1)日志文件过小,导致日志切换过于频繁。
(2)日志组太少,不能满足正常事务量的需求
(3)日志文件所在磁盘I/O存在瓶颈,导致写出缓慢,阻塞数据库正常运行。
(4)由于数据文件磁盘I/O瓶颈,DBWR写出过于缓慢。
(5)由于事务量大,DBWR负荷过高,不堪重负。
解决方法:
(1)适当增加日志大小
(2)适当增加日志组数
(3)使用快速磁盘存储日志文件
(4)改善I/O性能
(5)使用多个DBWn进程或使用异步I/O等
//日志块的大小:
redo block sizes是源码固定的,与操作系统无关,从X$kccle
select max(lebsz) from x$kccle;
MAX(LEBSZ)
----------
512
//获取表结构
set long 9999;
select dbms_metadata.get_ddl('TABLE','EMP') from dual;
表创建基于tablespace创建
创建表前先创建表空间
查看redo日志量
select a.name,b.value from v$statname a join v$mystat b on a.statistic#=b.statistic# where a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 0
create table s01(id int,name varchar(50),addr varchar(100),primary key(id));
select a.name,b.value from v$statname a join v$mystat b on a.statistic#=b.statistic# where a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 22024
###############################
LISN@orcl>select a.name,b.value from v$statname a join v$mystat b on a.statistic#=b.statistic# where a.name='redo size';
select a.name,b.value from v$statname a join v$mystat b on a.statistic#=b.statistic# where a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 712
LISN@orcl>create table s01(id int,name varchar(50),addr varchar(100),primary key(id));
create table s01(id int,name varchar(50),addr varchar(100),primary key(id));
Table created.
LISN@orcl>select a.name,b.value from v$statname a join v$mystat b on a.statistic#=b.statistic# where a.name='redo size';
select a.name,b.value from v$statname a join v$mystat b on a.statistic#=b.statistic# where a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 16976
LISN@orcl>insert into s01(id) select rownum from dual connect by level < 1000;
insert into s01(id) select rownum from dual connect by level < 1000;
999 rows created.
LISN@orcl>select a.name,b.value from v$statname a join v$mystat b on a.statistic#=b.statistic# where a.name='redo size';
select a.name,b.value from v$statname a join v$mystat b on a.statistic#=b.statistic# where a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 89088
LISN@orcl>delete from s01;
delete from s01;
999 rows deleted.
LISN@orcl>select a.name,b.value from v$statname a join v$mystat b on a.statistic#=b.statistic# where a.name='redo size';
select a.name,b.value from v$statname a join v$mystat b on a.statistic#=b.statistic# where a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 546636
insert into s01 select rownum from dual connect by level < 100000;
//创建即增加redo日志logfile
alter database add logfile group 4 '/u01/app/oracle/oradata/orcl/redo04.log' size 50m;
select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 INACTIVE
3 INACTIVE
4 CURRENT
//手动强制切换日志文件
alter system switch logfile;
查看
select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 CURRENT
3 INACTIVE
4 ACTIVE
可以删除inactive redo log
LISN@orcl>alter database drop logfile group 3;
//手动删除日志文件,释放存储空间:
[root@nan86 orcl]# pwd
/u01/app/oracle/oradata/orcl
[root@nan86 orcl]# rm -f redo03.log
//归档日志的查询:
系统管理员可查
desc v$archived_log
查询归档日志名 归档时间 大小 blocks为块数
select name,completion_time,blocks*block_size/1024/1024 size_mb from v$archived_log;
###undo
undo详解:认为undo用于数据库物理恢复到执行语句或事务之前的样子,其实数据库只是逻辑地恢复到原来的样子,所有修改都是逻辑的取消。但是数据结构以及数据库本身在回滚后可能大不相同。
set autotrace on explain;
set autotrace on;
create table t as select * from all_objects where 1=0;
select * from t;
insert into t select * from all_objects;
select count(*) from t;
COUNT(*)
----------
71498
rollback;
事务的混滚是逻辑回滚
select count(*) from t;
COUNT(*)
----------
0
set autotrace on statistics;
//查看alter trace日志
SYS@orcl>show parameter user_dump_dest;
show parameter user_dump_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/diag/rdbms/orcl/orcl/trace
alter日志命名方式:alter_$ORACLE_SID.log
转载于:https://my.oschina.net/u/1458120/blog/225668