[20160302]奇怪的回滚段.txt
--昨天在探究oracle的启动时，无意中发现我安装这个版本，在安装的测试样例表空间example出现回滚段。
--自己今天仔细看看：
1.环境：
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@book> column name format a22
SYS@book> select us#,name,user#,file#,BLOCK#,SCNBAS,SCNWRP,XACTSQN,UNDOSQN,INST#,STATUS$,TS#,FLAGS,SPARE1 from undo$;
US# NAME USER# FILE# BLOCK# SCNBAS SCNWRP XACTSQN UNDOSQN INST# STATUS$ TS# FLAGS SPARE1
---- ---------------------- ----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0 SYSTEM 0 1 128 0 0 0 0 0 3 0 0
1 _SYSSMU1_3724004606$ 1 3 128 340871758 3 2634 776 0 3 2 2
2 _SYSSMU2_2996391332$ 1 3 144 340871760 3 2880 1144 0 3 2 2
3 _SYSSMU3_1723003836$ 1 3 160 340871979 3 2921 1506 0 3 2 2
4 _SYSSMU4_1254879796$ 1 3 176 340871764 3 2782 1273 0 3 2 2
5 _SYSSMU5_898567397$ 1 3 192 340871766 3 3011 1147 0 3 2 2
6 _SYSSMU6_1263032392$ 1 3 208 340872002 3 3268 897 0 3 2 2
7 _SYSSMU7_2070203016$ 1 3 224 340871998 3 4937 1197 0 3 2 2
8 _SYSSMU8_517538920$ 1 3 240 340871772 3 8287 1351 0 3 2 2
9 _SYSSMU9_1650507775$ 1 3 256 340871992 3 11684 1097 0 3 2 2
10 _SYSSMU10_1197734989$ 1 3 272 340872006 3 24725 4194 0 3 2 2
11 _SYSSMU11_1701842685$ 1 3 248 337617109 3 557 362 0 2 2 2
12 _SYSSMU12_3785813942$ 1 3 296 337514803 3 1747 624 0 2 2 2
13 _SYSSMU13_3860906822$ 1 5 160 923661 0 2 1 0 1 5 2
14 _SYSSMU14_3319140121$ 1 5 176 923323 0 2 1 0 1 5 2
15 _SYSSMU15_1436577151$ 1 5 192 923332 0 2 1 0 1 5 2
16 _SYSSMU16_1689093467$ 1 5 208 923314 0 2 1 0 1 5 2
17 _SYSSMU17_1049158485$ 1 5 224 923296 0 2 1 0 1 5 2
18 _SYSSMU18_1557221903$ 1 5 240 923320 0 2 1 0 1 5 2
19 _SYSSMU19_2284825117$ 1 5 256 923294 0 2 1 0 1 5 2
20 _SYSSMU20_2312497597$ 1 5 272 923262 0 2 1 0 1 5 2
21 rows selected.
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ----------------
1 13225773903 2016-03-02 09:02:37 7 925702 ONLINE 744 YES /mnt/ramdisk/book/system01.dbf SYSTEM
2 13225773903 2016-03-02 09:02:37 1834 925702 ONLINE 744 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13225773903 2016-03-02 09:02:37 923328 925702 ONLINE 665 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13225773903 2016-03-02 09:02:37 16143 925702 ONLINE 744 YES /mnt/ramdisk/book/users01.dbf USERS
5 13225773903 2016-03-02 09:02:37 952916 925702 ONLINE 662 YES /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13225773903 2016-03-02 09:02:37 1314508 925702 ONLINE 678 YES /mnt/ramdisk/book/sugar01.dbf SUGAR
6 rows selected.
SYS@book> show parameter undo
NAME TYPE VALUE
---------------- -------- ----------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
--很明显我个人不会在file#=5的数据文件上建立回滚段。实际上在8i dba要手工建立回滚段。8i下还有一个参数rollback_segments，这个参数在11g也存在
SYS@book> show parameter rollback_segments
NAME TYPE VALUE
------------------ -------- ---------------
rollback_segments string
--当使用undo_management=manual 时，可以手工指定。但是当我查询：
SYS@book> select * from dba_extents where segment_name like '_SYSSMU%' and file_id=5 ;
no rows selected
--没有结果。
SYS@book> column SEGMENT_NAME format a30
SYS@book> select * from dba_extents where file_id=5 and block_id between 160 and 272+16 order by block_id;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ ------------------------ ------------ --------------- --------- ------------ ------------ ------------ ------------ ------------
HR LOC_ID_PK INDEX EXAMPLE 0 5 160 65536 8 5
HR DEPARTMENTS TABLE EXAMPLE 0 5 168 65536 8 5
HR DEPT_ID_PK INDEX EXAMPLE 0 5 176 65536 8 5
HR JOBS TABLE EXAMPLE 0 5 184 65536 8 5
HR JOB_ID_PK INDEX EXAMPLE 0 5 192 65536 8 5
HR EMPLOYEES TABLE EXAMPLE 0 5 200 65536 8 5
HR EMP_EMAIL_UK INDEX EXAMPLE 0 5 208 65536 8 5
HR EMP_EMP_ID_PK INDEX EXAMPLE 0 5 216 65536 8 5
HR JOB_HISTORY TABLE EXAMPLE 0 5 224 65536 8 5
HR JHIST_EMP_ID_ST_DATE_PK INDEX EXAMPLE 0 5 232 65536 8 5
HR EMP_DEPARTMENT_IX INDEX EXAMPLE 0 5 240 65536 8 5
HR EMP_JOB_IX INDEX EXAMPLE 0 5 248 65536 8 5
HR EMP_MANAGER_IX INDEX EXAMPLE 0 5 256 65536 8 5
HR EMP_NAME_IX INDEX EXAMPLE 0 5 264 65536 8 5
HR DEPT_LOCATION_IX INDEX EXAMPLE 0 5 272 65536 8 5
HR JHIST_JOB_IX INDEX EXAMPLE 0 5 280 65536 8 5
HR JHIST_EMPLOYEE_IX INDEX EXAMPLE 0 5 288 65536 8 5
17 rows selected.
--很明显不是这些对象。拿那些回滚段从那里产生的呢？
--我安装的oracle测试数据库基本按照如下链接http://blog.itpub.net/267265/viewspace-1845062/。我当时做了一个冷备份。
--检查数据文件system01.dbf可以发现：
001C3460 00 00 00 2C 01 11 02 C1 08 14 5F 53 59 53 53 4D ...,......_SYSSM
001C3470 55 37 5F 32 30 37 30 32 30 33 30 31 36 24 02 C1 U7_2070203016$..
001C3480 02 02 C1 04 03 C2 03 19 04 C3 64 44 0E 01 80 03 ..........dD....
001C3490 C2 07 3D 03 C2 02 0C 01 80 02 C1 03 02 C1 03 FF ..=.............
001C34A0 FF FF FF 02 C1 03 2C 01 11 02 C1 0B 15 5F 53 59 ......,......_SY
001C34B0 53 53 4D 55 31 30 5F 31 31 39 37 37 33 34 39 38 SSMU10_119773498
001C34C0 39 24 02 C1 02 02 C1 04 03 C2 03 49 04 C3 64 46 9$.........I..dF
001C34D0 06 01 80 03 C2 09 30 03 C2 02 55 01 80 02 C1 03 ......0...U.....
001C34E0 02 C1 03 FF FF FF FF 02 C1 03 2C 00 11 02 C1 15 ..........,.....
001C34F0 15 5F 53 59 53 53 4D 55 32 30 5F 32 33 31 32 34 ._SYSSMU20_23124
001C3500 39 37 35 39 37 24 02 C1 02 02 C1 06 03 C2 03 49 97597$.........I
001C3510 04 C3 5D 21 3F 01 80 02 C1 03 02 C1 02 01 80 02 ..]!?...........
001C3520 C1 02 02 C1 06 FF FF FF FF 02 C1 03 2C 00 11 02 ............,...
001C3530 C1 14 15 5F 53 59 53 53 4D 55 31 39 5F 32 32 38 ..._SYSSMU19_228
001C3540 34 38 32 35 31 31 37 24 02 C1 02 02 C1 06 03 C2 4825117$........
001C3550 03 39 04 C3 5D 21 5F 01 80 02 C1 03 02 C1 02 01 .9..]!_.........
001C3560 80 02 C1 02 02 C1 06 FF FF FF FF 02 C1 03 2C 00 ..............,.
001C3570 11 02 C1 13 15 5F 53 59 53 53 4D 55 31 38 5F 31 ....._SYSSMU18_1
001C3580 35 35 37 32 32 31 39 30 33 24 02 C1 02 02 C1 06 557221903$......
001C3590 03 C2 03 29 04 C3 5D 22 15 01 80 02 C1 03 02 C1 ...)..]"........
001C35A0 02 01 80 02 C1 02 02 C1 06 FF FF FF FF 02 C1 03 ................
001C35B0 2C 00 11 02 C1 12 15 5F 53 59 53 53 4D 55 31 37 ,......_SYSSMU17
001C35C0 5F 31 30 34 39 31 35 38 34 38 35 24 02 C1 02 02 _1049158485$....
001C35D0 C1 06 03 C2 03 19 04 C3 5D 21 61 01 80 02 C1 03 ........]!a.....
001C35E0 02 C1 02 01 80 02 C1 02 02 C1 06 FF FF FF FF 02 ................
001C35F0 C1 03 2C 00 11 02 C1 11 15 5F 53 59 53 53 4D 55 ..,......_SYSSMU
001C3600 31 36 5F 31 36 38 39 30 39 33 34 36 37 24 02 C1 16_1689093467$..
001C3610 02 02 C1 06 03 C2 03 09 04 C3 5D 22 0F 01 80 02 ..........]"....
001C3620 C1 03 02 C1 02 01 80 02 C1 02 02 C1 06 FF FF FF ................
001C3630 FF 02 C1 03 2C 00 11 02 C1 10 15 5F 53 59 53 53 ....,......_SYSS
001C3640 4D 55 31 35 5F 31 34 33 36 35 37 37 31 35 31 24 MU15_1436577151$
001C3650 02 C1 02 02 C1 06 03 C2 02 5D 04 C3 5D 22 21 01 .........]..]"!.
001C3660 80 02 C1 03 02 C1 02 01 80 02 C1 02 02 C1 06 FF ................
001C3670 FF FF FF 02 C1 03 2C 00 11 02 C1 0F 15 5F 53 59 ......,......_SY
001C3680 53 53 4D 55 31 34 5F 33 33 31 39 31 34 30 31 32 SSMU14_331914012
001C3690 31 24 02 C1 02 02 C1 06 03 C2 02 4D 04 C3 5D 22 1$.........M..]"
001C36A0 18 01 80 02 C1 03 02 C1 02 01 80 02 C1 02 02 C1 ................
001C36B0 06 FF FF FF FF 02 C1 03 2C 00 11 02 C1 0E 15 5F ........,......_
001C36C0 53 59 53 53 4D 55 31 33 5F 33 38 36 30 39 30 36 SYSSMU13_3860906
--这些回滚段_SYSSMU20_2312497597$在undo中已经存在，我那种安装相当于copy过来的，估计以前曾经有数据文件5做过undo表空间。
--不知道为什么没有清除，目前不影响测试，先暂时不管它。