本文章为网络笔记,看了warehouse老师的视频受益匪浅,更是感觉自己技术太过初级,特写了本笔记,方便以后反复学习!
如有任何不妥,请发邮件至102448567@qq.com删除文章!
关于warehouse:
http://blog.itpub.net/19602/viewspace-1059211/
11gR2视频第四版 4_01_undo的3大用途以及实例恢复
其他数据库没有undo
,oracle
把一个事务修改之前的数据放到了undo
中,rollback
就是利用undo
中的数据,9.2
开始引进undo
表空间,最开始叫回滚段
表空间有三种类型,是根据段分类的
segment:
1.data segment
2.index segment
3.undo segment(rollback segment)
4.temp segment
查看数据文件路径
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oradata/prod/system01.dbf
/u01/app/oradata/prod/sysaux01.dbf
/u01/app/oradata/prod/undotbs01.dbf
/u01/app/oradata/prod/users01.dbf
创建undo
表空间
SQL> create undo tablespace undotbs2 datafile '/u01/app/oradata/prod/undotbs02.dbf' size 10m;
Tablespace created.
表空间和数据库文件都有了
SQL> select tablespace_name,contents from dba_tablespaces;
TABLESPACE_NAME CONTENTS
------------------------------ ---------
SYSTEM PERMANENT
SYSAUX PERMANENT
UNDOTBS1 UNDO
TEMP TEMPORARY
USERS PERMANENT
UNDOTBS2 UNDO
6 rows selected.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oradata/prod/system01.dbf
/u01/app/oradata/prod/sysaux01.dbf
/u01/app/oradata/prod/undotbs01.dbf
/u01/app/oradata/prod/users01.dbf
/u01/app/oradata/prod/undotbs02.dbf
undo
表空间也是segment
组成的,undo segment
是数据库自己创建的
segment
是由extent
组成的,extent
的file_id
是5
查undo segment
还可以查dba_rollback_segs
,owner
是sys
这个undo segment
只能被这个实例使用,public
能被所有实例使用(指的就是RAC
),但是一般情况每个实例使用各自的undo
表空间,因为实例有参数限制只能使用一个undo
表空间,下图中还有一个status
,只有online
才能被事务使用
实例使用哪一个undo
就是由parameter undo_tablespace
控制的
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
这是一个动态参数,修改之后立即生效
SQL> alter system set undo_tablespace=undotbs2;
System altered.
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
数据库在使用的时候是可以直接切换undo
表空间的,但是在删除旧的undo
表空间之前要确定已经没有事务使用原来的undo
表空间了,因为事务修改之前的数据放在了undo
表空间中
每一个事务都会使用一个undo segment
,一个undo segment
会不会被多个事务使用呢,当undo segment
不够用的时候有可能会,之前有个参数控制一个undo segment
最多被几个事务使用,现在这个参数已经淘汰了
SQL> show parameter rollback;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
transactions_per_rollback_segment integer 5
现在没有事务
SQL> grant dba to a;
Grant succeeded.
SQL> conn a/a
Connected.
SQL> create table t1 (id int,name varchar2(50));
Table created.
SQL> insert into t1 values(1,'a');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t1 values(2,'b');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
ID NAME
---------- --------------------------------------------------
1 a
2 b
SQL> update t1 set name='abc' where id=1;
1 row updated.
没有提交
XID:transantion identified
USN:undo segment number
XIDUSN
就是事务使用的undo segment id
事务修改之前的数据在UBAFIL:5
号数据文件上的UBABLK:210
号block
上
事务使用的undo extent
在5
号文件的第208
号block
之后连续的8
个block
和216
号block
之后的连续8
个block
,一共用了16
个block
下面验证事务修改之前的数据是不是在5
号文件的210
号块上
SQL> alter system dump datafile 5 block 210;
System altered.
把这个block
转储到了一个文件里,我们的数据库是专用连接模式,每一个session
都有一个process
为他服务,在oracle
中每一个process
都有它的日志,就放在了这个日志文件中
SQL> show user;
USER is "A"
SQL> select distinct sid from v$mystat;
SID
----------
30
日志文件就在下图路径中
[root@minimal ~]# ps -ef | grep 5205
oracle 5205 5196 0 10:42 ? 00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
11g
之前没有tracefile
这个字段,先找到这个路径,用户进程dump
的路径
SQL> show parameter user;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/diag/rdbms/pro
d/orcl/trace
trm
文件是trace
文件的元数据文件,可以不用管他
[oracle@minimal trace]$ pwd
/u01/app/oracle/diag/rdbms/prod/orcl/trace
[oracle@minimal trace]$ ls | grep 5205
orcl_ora_5205.trc
orcl_ora_5205.trm
[oracle@minimal trace]$ cat orcl_ora_5205.trc
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010000ad hdba: 0x010000aa
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: -2
col 1: [ 1] 61
End dump data blocks tsn: 5 file#: 5 minblk 210 maxblk 210
最后的col 1: [ 1] 61
就是修改之前的数据
col1
是第二个字段,col0
是第一个
[ 1]
是占用一个字节,61
就是修改之前的a
,oracle
中存储的字符是以ASCII
存储的,而且是ASCII
的十六进制数
SQL> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
NAME VARCHAR2(50)
a
的ascii
是97
,但是这是十进制数
SQL> select ascii('a') from dual;
ASCII('A')
----------
97
十六进制数61
转换成十进制数6*16+1=97
,这也证明了事务修改之前的数据放在undo
里
dump
出来的文件内容很多,怎么找到事务对应的信息
UBA:undo block address
在这个块里也是以记录的形式记录的
Rec ....
这样就是一段用------隔开了,0xc
(十六进制数)就是上图中的12
,所以update
的事务对应的就是最下面的一段
*-----------------------------
* Rec #0xb slt: 0x02 objn: 272(0x00000110) objd: 272 tblspc: 1(0x00000001)
* Layer: 10 (Index) opc: 22 rci 0x0a
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
op: L itl: xid: 0x0014.000.00000002 uba: 0x01400112.0000.06
flg: C--- lkc: 0 scn: 0x0000.001e64b0
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x8001a2 block=0x008001a3
(kdxlpu): purge leaf row
key :(6): 05 c4 02 64 13 51
*-----------------------------
* Rec #0xc slt: 0x03 objn: 15957(0x00003e55) objd: 15957 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x014000d2.0000.09 ctl max scn: 0x0000.00000000 prv tx scn: 0x0000.00000000
txn start scn: scn: 0x0000.001e5109 logon user: 44
prev brb: 0 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0012.003.00000002 uba: 0x014000f2.0000.0b
flg: C--- lkc: 0 scn: 0x0000.001e65d4
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x010000ad hdba: 0x010000aa
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: -2
col 1: [ 1] 61
End dump data blocks tsn: 5 file#: 5 minblk 210 maxblk 210
还有元数据的编号和数据的编号可以对应上
0x00003e55
是十六进制数
* Rec #0xc slt: 0x03 objn: 15957(0x00003e55) objd: 15957 tblspc: 4(0x00000004)
还有表空间编号可以对应
以上也就确定了我们找到的确实是update
的事务修改之前的数据
笔记不是一天做的,接上面的,上次关机之前没有commit所以数据没有变,还是修改一条不提交
session 1
SQL> show user
USER is "A"
SQL> update t1 set name = 'abc' where id =1;
1 row updated.
session 2
这个session
看到的有可能是三种结果a,abc,
或者提示有锁,无法查看,Oracle
查看到的是修改之前的数据a
session1
没有commit
如果session2
能看到abc
那就是看到了脏数据,读到了之前的数据是叫一致性读取
[oracle@minimal ~]$ sqlplus a/a
SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 27 16:44:56 2020
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from t1;
ID NAME
---------- --------------------------------------------------
1 a
2 b
undo的三大作用
1.consistent read
Oracle
不支持dirty read
2.transaction rollback
session 1
SQL> rollback;
Rollback complete.
这个时候就没有事务了
重新开启这个事务
SQL> update t1 set name = 'abc' where id =1;
1 row updated.
当一个事务没有完成的时候它在undo
中的数据不能够被覆盖,做很大的事务的时候要考虑undo
的大小,暴力关闭session 1
事务也没有了
3.tracsantion recovery
transaction recovery
是instance recovery
的一部分
recovery:
1.instance recovery
分三个阶段
a.scan redo
,把那些写到了日志文件但是还没有写到数据文件中的数据读到内存中然后写到数据文件达到同步,这个叫前滚(forward roll
)
b.open db
(日志文件和数据文件一致了就可以打开了)
c.transaction recovery
(这时候有很多数据没有提交要回滚)
(数据库要想正常使用要有instance
,所有的数据修改都在内存中进行,然后异步的写到数据文件中,停电或者bug
数据库直接宕了,不管有没有提交,有数据没有写到磁盘上,数据不是完全同步的commit
的要找回来前滚,要是insert
了很多的数据但是没有提交Oracle
也不一定完全没有写到磁盘中,没commit
的要回滚)
2.media recovery(database recovery
数据文件的恢复)
users
表空间是可以自动扩展的,现在是5M
[oracle@minimal prod]$ ll -h
-rw-r----- 1 oracle oinstall 5.1M 10月 27 17:30 users01.dbf
DDL
命令自动提交,过程是commit-执行sql-commit
SQL> create table tt tablespace users as select * from dba_objects;
Table created.
没有提交
SQL> insert into tt select * from tt;
15558 rows created.
数据文件增大了,说明没有commit
也有可能把数据写入磁盘
[oracle@minimal prod]$ ll -h
-rw-r----- 1 oracle oinstall 6.3M 10月 27 17:44 users01.dbf
写了数据文件也一定写了日志文件
SQL> insert into tt select * from tt;
15558 rows created.
SQL> insert into tt select * from tt;
31116 rows created.
SQL> insert into tt select * from tt;
62232 rows created.
SQL> insert into tt select * from tt;
124464 rows created.
SQL> insert into tt select * from tt;
248928 rows created.
日志切换了
commit
只能保证把日志缓存区写入日志文件,不能保证把数据缓存区写入数据文件,日志优先于数据用来保护数据,当用户a
提交的时候把日志缓存区中b
的数据也写入日志文件了,但是宕机重启之后b
看到还是修改之前的数据,写入磁盘和写入数据库是两回事
模拟掉电(新开session
操作,Oracle
用户退出登陆隐式提交)
SQL> shu abort;
ORACLE instance shut down.
启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 622149632 bytes
Fixed Size 2230912 bytes
Variable Size 184550784 bytes
Database Buffers 432013312 bytes
Redo Buffers 3354624 bytes
Database mounted.
Database opened.
分析alert
日志
Thread 1 advanced to log sequence 19 (LGWR switch)
Current log# 1 seq# 19 mem# 0: /u01/app/oradata/prod/redo01.log
Tue Oct 27 18:03:39 2020
上面还是正常的,日志正常切换
Shutting down instance (abort)
License high water mark = 4
USER (ospid: 6625): terminating the instance
实例被pid(进程id)6625
给打断了
Instance terminated by USER, pid = 6625
Tue Oct 27 18:03:40 2020
Instance shutdown complete
实例shutdown
完成了
下面就开始启动了
Wed Oct 28 10:38:22 2020
Starting ORACLE instance (normal)
****************** Large Pages Information *****************
Total Shared Global Region in Large Pages = 0 KB (0%)
Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 0 (0 KB) (alloc incr 4096 KB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 2048 KB
RECOMMENDATION:
Total Shared Global Region size is 598 MB. For optimal performance,
prior to the next instance restart increase the number
of unused Large Pages by atleast 299 2048 KB Large Pages (598 MB)
system wide to get 100% of the Shared
Global Region allocated with Large pages
***********************************************************
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: minimal
Release: 3.10.0-862.el7.x86_64
Version: #1 SMP Fri Apr 20 16:44:24 UTC 2018
Machine: x86_64
VM name: VMWare Version: 6
Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora
System parameters with non-default values:
processes = 150
sga_target = 596M
control_files = "/u01/app/oradata/prod/control01.ctl"
control_files = "/u01/app/oracle/fast_recovery_area/prod/control02.ctl"
db_block_size = 8192
db_cache_size = 40M
compatible = "11.2.0.0.0"
db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"
db_recovery_file_dest_size= 4977M
undo_tablespace = "UNDOTBS2"
remote_login_passwordfile= "EXCLUSIVE"
audit_sys_operations = FALSE
db_domain = ""
dispatchers = ""
shared_servers = 0
audit_file_dest = "/u01/app/oracle/admin/prod/adump"
audit_trail = "NONE"
db_name = "prod"
open_cursors = 300
pga_aggregate_target = 198M
diagnostic_dest = "/u01/app/oracle"
Wed Oct 28 10:38:23 2020
上面读完参数下面就开始启动进程了,PMON
在Oracle
中pid
是2
,OS
中是55410
PMON started with pid=2, OS id=55410
Wed Oct 28 10:38:23 2020
PSP0 started with pid=3, OS id=55412
Wed Oct 28 10:38:23 2020
VKTM started with pid=4, OS id=55414 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Wed Oct 28 10:38:23 2020
GEN0 started with pid=5, OS id=55418
Wed Oct 28 10:38:23 2020
DIAG started with pid=6, OS id=55420
Wed Oct 28 10:38:23 2020
DBRM started with pid=7, OS id=55422
Wed Oct 28 10:38:23 2020
DIA0 started with pid=8, OS id=55424
Wed Oct 28 10:38:23 2020
MMAN started with pid=9, OS id=55426
Wed Oct 28 10:38:23 2020
DBW0 started with pid=10, OS id=55428
Wed Oct 28 10:38:23 2020
LGWR started with pid=11, OS id=55430
Wed Oct 28 10:38:23 2020
CKPT started with pid=12, OS id=55432
Wed Oct 28 10:38:23 2020
SMON started with pid=13, OS id=55434
Wed Oct 28 10:38:23 2020
RECO started with pid=14, OS id=55436
Wed Oct 28 10:38:23 2020
MMON started with pid=15, OS id=55438
ORACLE_BASE from environment = /u01/app/oracle
Wed Oct 28 10:38:23 2020
MMNL started with pid=16, OS id=55440
Wed Oct 28 10:38:23 2020
实例已经启动了
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 466835743
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
成功mount
Wed Oct 28 10:38:27 2020
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
开始扫描ACTIVE
和CURRENT
状态的日志文件INACTIVE
不用扫描,INACTIVE
都已经写到数据文件里了
Started redo scan
Completed redo scan
读了40M
的日志文件,5610
个数据块需要恢复
read 41983 KB redo, 5610 data blocks need recovery
Started redo application at
Thread 1: logseq 18, block 23862
读了第1、3
组日志文件
Recovery of Online Redo Log: Thread 1 Group 3 Seq 18 Reading mem 0
Mem# 0: /u01/app/oradata/prod/redo03.log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 19 Reading mem 0
Mem# 0: /u01/app/oradata/prod/redo01.log
应用日志文件完成了,应用了38.49M
Completed redo application of 38.49MB
恢复完成
Completed crash recovery at
Thread 1: logseq 19, block 10086, scn 2049893
5610 data blocks read, 5610 data blocks written, 41983 redo k-bytes read
Thread 1 advanced to log sequence 20 (thread open)
Thread 1 opened at log sequence 20
Current log# 2 seq# 20 mem# 0: /u01/app/oradata/prod/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
[55450] Successfully onlined Undo Tablespace 5.
Undo initialization finished serial:0 start:65335344 end:65335384 diff:40 (0 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
启动事务恢复,这里就会用到undo
,没有提交的就会回滚
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Oct 28 10:38:29 2020
QMNC started with pid=18, OS id=55453
Completed: ALTER DATABASE OPEN
Wed Oct 28 10:38:29 2020
db_recovery_file_dest_size of 4977 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Starting background process CJQ0
Wed Oct 28 10:38:29 2020
CJQ0 started with pid=21, OS id=55467
最后a.tt
的数据只有insert
之前的数据