[20160304]奇怪的回滚段2.txt
--链接：
http://blog.itpub.net/267265/viewspace-2022969/
$ locate Seed_Database.dfb
/u01/app/oracle/product/11.2.0.4/dbhome_1/assistants/dbca/templates/Seed_Database.dfb
--安装的数据文件应该是从这个文件取出的。
$ strings /u01/app/oracle/product/11.2.0.4/dbhome_1/assistants/dbca/templates/Seed_Database.dfb | grep SYSSMU20
--没有显示。估计压缩保存的备份文件。
--如果你安装时保存脚本的话，在类似的目录 /u01/app/oracle11g/admin/test/scripts记录安装的执行脚本：
--自己修改取出system01.dbf文件放入/tmp目录。
set verify off;
set echo off;
set serveroutput on;
select TO_CHAR(systimestamp,'YYYYMMDD HH:MI:SS') from dual;
variable devicename varchar2(255);
declare
omfname varchar2(512) := NULL;
done boolean;
begin
dbms_output.put_line(' ');
dbms_output.put_line(' Allocating device.... ');
dbms_output.put_line(' Specifying datafiles... ');
:devicename := dbms_backup_restore.deviceAllocate;
dbms_output.put_line(' Specifing datafiles... ');
dbms_backup_restore.restoreSetDataFile;
dbms_backup_restore.restoreDataFileTo(1, '/tmp/system01.dbf', 0, 'SYSTEM');
-- dbms_backup_restore.restoreDataFileTo(1, '/u01/app/oracle11g/oradata/test/system01.dbf', 0, 'SYSTEM');
-- dbms_backup_restore.restoreDataFileTo(1, '/u01/app/oracle11g/oradata/test/system01.dbf', 0, 'SYSTEM');
-- dbms_backup_restore.restoreDataFileTo(2, '/u01/app/oracle11g/oradata/test/sysaux01.dbf', 0, 'SYSAUX');
-- dbms_backup_restore.restoreDataFileTo(3, '/u01/app/oracle11g/oradata/test/undotbs01.dbf', 0, 'UNDOTBS1');
-- dbms_backup_restore.restoreDataFileTo(4, '/u01/app/oracle11g/oradata/test/users01.dbf', 0, 'USERS');
dbms_output.put_line(' Restoring ... ');
dbms_backup_restore.restoreBackupPiece('/u01/app/oracle/product/11.2.0.4/dbhome_1/assistants/dbca/templates/Seed_Database.dfb', done);
if done then
dbms_output.put_line(' Restore done.');
else
dbms_output.put_line(' ORA-XXXX: Restore failed ');
end if;
dbms_backup_restore.deviceDeallocate;
end;
/
select TO_CHAR(systimestamp,'YYYYMMDD HH:MI:SS') from dual;
SYS@book> @ rmanRestoreDatafiles.sql
TO_CHAR(SYSTIMEST
-----------------
20160304 10:53:54
Allocating device....
Specifying datafiles...
Specifing datafiles...
Restoring ...
Restore done.
PL/SQL procedure successfully completed.
TO_CHAR(SYSTIMEST
-----------------
20160304 10:54:30
$ ls -l /tmp/system01.dbf
-rw-r----- 1 oracle oinstall 775954432 2016-03-04 10:54:30 /tmp/system01.dbf
--OK，已经取出。
SYS@book> select rowid,us#,name,user#,file#,BLOCK#,SCNBAS,SCNWRP,XACTSQN,UNDOSQN,INST#,STATUS$,TS#,FLAGS,SPARE1 from sys.undo$ where us# in (19,20);
ROWID US# NAME USER# FILE# BLOCK# SCNBAS SCNWRP XACTSQN UNDOSQN INST# STATUS$ TS# FLAGS SPARE1
------------------ ---------- -------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
AAAAAPAABAAAADhAAT 19 _SYSSMU19_2284825117$ 1 5 256 923294 0 2 1 0 1 5 2
AAAAAPAABAAAADhAAU 20 _SYSSMU20_2312497597$ 1 5 272 923262 0 2 1 0 1 5 2
SYS@book> @ &r/rowid AAAAAPAABAAAADhAAU
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
15 1 225 20 1,225 alter system dump datafile 1 block 225 ;
SYS@book> @ &r/bbvi 1 225
BVI_COMMAND
------------------------------------------------------------------------------------------
bvi -b 1843200 -s 8192 /mnt/ramdisk/book/system01.dbf
$ strings /tmp/system01.dbf | grep _SYSSMU20_2312497597
_SYSSMU20_2312497597$
_SYSSMU20_2312497597$
$ strings /tmp/system01.dbf | grep _SYSSMU19_2284825117
_SYSSMU19_2284825117$
_SYSSMU19_2284825117$
--可以发现原始的文件这些信息就存在。
$ bvi -b 1843200 -s 8192 /tmp/system01.dbf
001C3870 09 43 03 C2 02 11 01 80 02 C1 03 02 C1 03 FF FF .C..............
001C3880 FF FF 02 C1 03 2C 00 11 02 C1 15 15 5F 53 59 53 .....,......_SYS
001C3890 53 4D 55 32 30 5F 32 33 31 32 34 39 37 35 39 37 SMU20_2312497597
001C38A0 24 02 C1 02 02 C1 06 03 C2 03 49 01 80 01 80 01 $.........I.....
001C38B0 80 01 80 01 80 02 C1 04 02 C1 06 FF FF FF FF 02 ................
001C38C0 C1 03 2C 00 11 02 C1 14 15 5F 53 59 53 53 4D 55 ..,......_SYSSMU
001C38D0 31 39 5F 32 32 38 34 38 32 35 31 31 37 24 02 C1 19_2284825117$..
001C38E0 02 02 C1 06 03 C2 03 39 01 80 01 80 01 80 01 80 .......9........
001C38F0 01 80 02 C1 04 02 C1 06 FF FF FF FF 02 C1 03 2C ...............,
001C3900 00 11 02 C1 13 15 5F 53 59 53 53 4D 55 31 38 5F ......_SYSSMU18_
001C3910 31 35 35 37 32 32 31 39 30 33 24 02 C1 02 02 C1 1557221903$.....
001C3920 06 03 C2 03 29 01 80 01 80 01 80 01 80 01 80 02 ....)...........
001C3930 C1 04 02 C1 06 FF FF FF FF 02 C1 03 2C 00 11 02 ............,...
001C3940 C1 12 15 5F 53 59 53 53 4D 55 31 37 5F 31 30 34 ..._SYSSMU17_104
001C3950 39 31 35 38 34 38 35 24 02 C1 02 02 C1 06 03 C2 9158485$........
001C3960 03 19 01 80 01 80 01 80 01 80 01 80 02 C1 04 02 ................
001C3970 C1 06 FF FF FF FF 02 C1 03 2C 00 11 02 C1 11 15 .........,......
001C3980 5F 53 59 53 53 4D 55 31 36 5F 31 36 38 39 30 39 _SYSSMU16_168909
001C3990 33 34 36 37 24 02 C1 02 02 C1 06 03 C2 03 09 01 3467$...........
001C39A0 80 01 80 01 80 01 80 01 80 02 C1 04 02 C1 06 FF ................
001C39B0 FF FF FF 02 C1 03 2C 00 11 02 C1 10 15 5F 53 59 ......,......_SY
001C39C0 53 53 4D 55 31 35 5F 31 34 33 36 35 37 37 31 35 SSMU15_143657715
001C39D0 31 24 02 C1 02 02 C1 06 03 C2 02 5D 01 80 01 80 1$.........]....
001C39E0 01 80 01 80 01 80 02 C1 04 02 C1 06 FF FF FF FF ................
001C39F0 02 C1 03 2C 00 11 02 C1 0F 15 5F 53 59 53 53 4D ...,......_SYSSM
001C3A00 55 31 34 5F 33 33 31 39 31 34 30 31 32 31 24 02 U14_3319140121$.
--可以发现oracle原始的Seed_Database.dfb备份集中就含有这些东西，11G的回滚段带有时间戳，不可能正好一致。通过bbed观察看看：
BBED> SET FILENAME '/tmp/system01.dbf'
FILENAME /tmp/system01.dbf
BBED> set block 225
BLOCK# 225
BBED> p *kdbr[20]
rowdata[68]
-----------
ub1 rowdata[68] @5354 0x2c
BBED> x /rncnnnnnnnnnnnnnnnnncct
rowdata[68] @5354
-----------
flag@5354: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5355: 0x00
cols@5356: 17
col 0[2] @5357: 20
col 1[21] @5360: _SYSSMU20_2312497597$
col 2[2] @5382: 1
col 3[2] @5385: 5
col 4[3] @5388: 272
col 5[4] @5392: 923262
col 6[1] @5397: 0
col 7[2] @5399: 2
col 8[2] @5402: 1
col 9[1] @5405: 0
col 10[2] @5407: 1
col 11[2] @5410: 5
col 12[0] @5413: *NULL*
col 13[0] @5414: *NULL*
col 14[0] @5415: *NULL*
col 15[0] @5416: *NULL*
col 16[2] @5417: 2
--对比可以发现,里面的信息都对上，很明显oracle的原始模板就存在问题，oracle有点............
SYS@book> select rowid,us#,name,user#,file#,BLOCK#,SCNBAS,SCNWRP,XACTSQN,UNDOSQN,INST#,STATUS$,TS#,FLAGS,SPARE1 from sys.undo$ where us# in (19,20);
ROWID US# NAME USER# FILE# BLOCK# SCNBAS SCNWRP XACTSQN UNDOSQN INST# STATUS$ TS# FLAGS SPARE1
------------------ ---------- -------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
AAAAAPAABAAAADhAAT 19 _SYSSMU19_2284825117$ 1 5 256 923294 0 2 1 0 1 5 2
AAAAAPAABAAAADhAAU 20 _SYSSMU20_2312497597$ 1 5 272 923262 0 2 1 0 1 5 2
--另外你仔细检查,US#=11,12的status=2,还是offline的。
SYS@book> select rowid,us#,name,user#,file#,BLOCK#,SCNBAS,SCNWRP,XACTSQN,UNDOSQN,INST#,STATUS$,TS#,FLAGS,SPARE1 from sys.undo$ ;
ROWID US# NAME USER# FILE# BLOCK# SCNBAS SCNWRP XACTSQN UNDOSQN INST# STATUS$ TS# FLAGS SPARE1
------------------ ---------- -------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
AAAAAPAABAAAADhAAA 0 SYSTEM 0 1 128 0 0 0 0 0 3 0 0
AAAAAPAABAAAADhAAB 1 _SYSSMU1_3724004606$ 1 3 128 340869022 3 2633 776 0 3 2 2
AAAAAPAABAAAADhAAC 2 _SYSSMU2_2996391332$ 1 3 144 340869012 3 2879 1144 0 3 2 2
AAAAAPAABAAAADhAAD 3 _SYSSMU3_1723003836$ 1 3 160 340868948 3 2920 1506 0 3 2 2
AAAAAPAABAAAADhAAE 4 _SYSSMU4_1254879796$ 1 3 176 340868952 3 2781 1273 0 3 2 2
AAAAAPAABAAAADhAAF 5 _SYSSMU5_898567397$ 1 3 192 340868986 3 3010 1147 0 3 2 2
AAAAAPAABAAAADhAAG 6 _SYSSMU6_1263032392$ 1 3 208 340868908 3 3267 897 0 3 2 2
AAAAAPAABAAAADhAAH 7 _SYSSMU7_2070203016$ 1 3 224 340869000 3 4936 1197 0 3 2 2
AAAAAPAABAAAADhAAI 8 _SYSSMU8_517538920$ 1 3 240 340869036 3 8287 1351 0 3 2 2
AAAAAPAABAAAADhAAJ 9 _SYSSMU9_1650507775$ 1 3 256 340869006 3 11683 1097 0 3 2 2
AAAAAPAABAAAADhAAK 10 _SYSSMU10_1197734989$ 1 3 272 340869042 3 24712 4194 0 3 2 2
AAAAAPAABAAAADhAAL 11 _SYSSMU11_1701842685$ 1 3 248 337617109 3 557 362 0 2 2 2
AAAAAPAABAAAADhAAM 12 _SYSSMU12_3785813942$ 1 3 296 337514803 3 1747 624 0 2 2 2
AAAAAPAABAAAADhAAN 13 _SYSSMU13_3860906822$ 1 5 160 923661 0 2 1 0 1 5 2
AAAAAPAABAAAADhAAO 14 _SYSSMU14_3319140121$ 1 5 176 923323 0 2 1 0 1 5 2
AAAAAPAABAAAADhAAP 15 _SYSSMU15_1436577151$ 1 5 192 923332 0 2 1 0 1 5 2
AAAAAPAABAAAADhAAQ 16 _SYSSMU16_1689093467$ 1 5 208 923314 0 2 1 0 1 5 2
AAAAAPAABAAAADhAAR 17 _SYSSMU17_1049158485$ 1 5 224 923296 0 2 1 0 1 5 2
AAAAAPAABAAAADhAAS 18 _SYSSMU18_1557221903$ 1 5 240 923320 0 2 1 0 1 5 2
AAAAAPAABAAAADhAAT 19 _SYSSMU19_2284825117$ 1 5 256 923294 0 2 1 0 1 5 2
AAAAAPAABAAAADhAAU 20 _SYSSMU20_2312497597$ 1 5 272 923262 0 2 1 0 1 5 2
21 rows selected.
SYS@book> column INSTANCE_NUM noprint
SYS@book> select * from dba_rollback_segs ;
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID FILE_ID BLOCK_ID INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS RELATIVE_FNO
---------------------- ------ ---------------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ -------- ------------
SYSTEM SYS SYSTEM 0 1 128 114688 57344 1 32765 ONLINE 1
_SYSSMU12_3785813942$ PUBLIC UNDOTBS1 12 3 296 131072 65536 2 32765 OFFLINE 3
_SYSSMU11_1701842685$ PUBLIC UNDOTBS1 11 3 248 131072 65536 2 32765 OFFLINE 3
_SYSSMU10_1197734989$ PUBLIC UNDOTBS1 10 3 272 131072 65536 2 32765 ONLINE 3
_SYSSMU9_1650507775$ PUBLIC UNDOTBS1 9 3 256 131072 65536 2 32765 ONLINE 3
_SYSSMU8_517538920$ PUBLIC UNDOTBS1 8 3 240 131072 65536 2 32765 ONLINE 3
_SYSSMU7_2070203016$ PUBLIC UNDOTBS1 7 3 224 131072 65536 2 32765 ONLINE 3
_SYSSMU6_1263032392$ PUBLIC UNDOTBS1 6 3 208 131072 65536 2 32765 ONLINE 3
_SYSSMU5_898567397$ PUBLIC UNDOTBS1 5 3 192 131072 65536 2 32765 ONLINE 3
_SYSSMU4_1254879796$ PUBLIC UNDOTBS1 4 3 176 131072 65536 2 32765 ONLINE 3
_SYSSMU3_1723003836$ PUBLIC UNDOTBS1 3 3 160 131072 65536 2 32765 ONLINE 3
_SYSSMU2_2996391332$ PUBLIC UNDOTBS1 2 3 144 131072 65536 2 32765 ONLINE 3
_SYSSMU1_3724004606$ PUBLIC UNDOTBS1 1 3 128 131072 65536 2 32765 ONLINE 3
13 rows selected.