[20150409]只读表空间与延迟块清除.txt

[20150409]只读表空间与延迟块清除.txt

--昨天测试只读表空间的数据库恢复问题,突然想到一种情况,如果只读表空间存在延迟块清除情况,这样在下次访问是会更新块的信息吗?
--自己还是做1个测试:

1.首先在测试前,说明1点,设置表空间只读,仅仅阻止dml操作,并不能阻止ddl操作,ddl操作的是数据字典.

SCOTT@test> @ &r/ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

CREATE TABLESPACE MSSM DATAFILE
  '/mnt/ramdisk/test/mssm01.dbf' SIZE 16M AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;

create table scott.deptx tablespace mssm as select * from scott.dept ;
create table scott.empy  tablespace mssm as select * from scott.emp  ;

SCOTT@test> update deptx set loc=loc ;
update deptx set loc=loc
       *
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
--无法修改表中数据.

SCOTT@test> delete from empy ;
delete from empy
            *
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '/mnt/ramdisk/test/mssm01.dbf'
--无法删除表中数据.

SCOTT@test> drop table empy ;
Table dropped.

SCOTT@test> select * from empy ;
select * from empy
              *
ERROR at line 1:
ORA-00942: table or view does not exist

--可以发现表依旧可以drop掉.
SCOTT@test> column SEGMENT_NAME format a30
SCOTT@test> select segment_name,header_file,header_block from dba_segments where tablespace_name='MSSM';
SEGMENT_NAME                    HEADER_FILE HEADER_BLOCK
------------------------------ ------------ ------------
BIN$E0GFElhsudLgUKjAWWRv7w==$0            6           17
DEPTX                                     6            9

SCOTT@test> flashback table empy to before drop;
Flashback complete.

SCOTT@test> drop table empy purge ;
Table dropped.

SCOTT@test> select segment_name,header_file,header_block from dba_segments where tablespace_name='MSSM';
SEGMENT_NAME                    HEADER_FILE HEADER_BLOCK
------------------------------ ------------ ------------
DEPTX                                     6            9
6.17                                      6           17

--SEGMENT_NAME以HEADER_FILE.HEADER_BLOCK表示,如果设置读写后.才会清除.

SCOTT@test> ALTER TABLESPACE MSSM READ write;
Tablespace altered.

SCOTT@test> select segment_name,header_file,header_block from dba_segments where tablespace_name='MSSM';
SEGMENT_NAME                    HEADER_FILE HEADER_BLOCK
------------------------------ ------------ ------------
DEPTX                                     6            9

2.延迟块清除测试:

SCOTT@test> select rowid,ora_rowscn,deptx.* from deptx ;
ROWID                ORA_ROWSCN       DEPTNO DNAME          LOC
------------------ ------------ ------------ -------------- -------------
AAAO1FAAGAAAAAKAAA  12688068469           10 ACCOUNTING     new york
AAAO1FAAGAAAAAKAAB  12688068469           20 RESEARCH       dallas
AAAO1FAAGAAAAAKAAC  12688068469           30 SALES          chicago
AAAO1FAAGAAAAAKAAD  12688068469           40 OPERATIONS     boston

SCOTT@test> @ &r/lookup_rowid AAAO1FAAGAAAAAKAAA
      OBJECT         FILE        BLOCK          ROW DBA                  TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
       60741            6           10            0 6,10                 alter system dump datafile 6 block 10 ;

SCOTT@test> update deptx set loc=lower(loc) ;
4 rows updated.

--打开另外会话执行.
SCOTT@test> alter system checkpoint ;
System altered.
SCOTT@test> alter system flush buffer_cache ;
System altered.
SYS@test> @ &r/bh 6 10
old  23:   dbarfil = &1 and
new  23:   dbarfil = 6 and
old  24:   dbablk = &2
new  24:   dbablk = 10
HLADDR           DBARFIL      DBABLK CLASS CLASS_TYPE         STATE               TCH   CR_SCN_BAS   CR_SCN_WRP   CR_UBA_FIL   CR_UBA_BLK   CR_UBA_SEQ BA               OBJECT_NAME
---------------- ------- ----------- ----- ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- --------------------
000000007B6FDFD8       6          10     1 data block         free                  0            0            0            0            0            0 000000006DC1A000 DEPTX
000000007B6FDFD8       6          10     1 data block         free                  0            0            0            0            0            0 000000006E12E000 DEPTX
000000007B6FDFD8       6          10     1 data block         free                  0            0            0            0            0            0 000000006B074000 DEPTX
000000007B6FDFD8       6          10     1 data block         free                  0            0            0            0            0            0 000000006ACA8000 DEPTX
000000007B6FDFD8       6          10     1 data block         free                  0            0            0            0            0            0 000000006ACF4000 DEPTX

SCOTT@test> select current_scn from v$database ;
CURRENT_SCN
------------
12688068873

SCOTT@test> commit ;

Commit complete.

SCOTT@test> select current_scn from v$database ;
CURRENT_SCN
------------
12688068877

SCOTT@test> alter tablespace mssm read only ;
Tablespace altered.

SCOTT@test> alter system dump datafile 6 block 10 ;
System altered.

Block header dump:  0x0180000a
Object id on Block? Y
seg/obj: 0xed45  csc: 0x02.f44490bc  itc: 2  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.016.00001ff2  0x00800038.0ed0.0f  ----    4  fsc 0x0000.00000000 0x02   0x000a.022.00001ff2  0x00800037.0ed0.0b  C---    0  scn 0x0002.f4448f75

data_block_dump,data header at 0x8420c5c
===============
tsiz: 0x1fa0
hsiz: 0x1a
pbl: 0x08420c5c
bdba: 0x0180000a
     76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1f44
avsp=0x1f2a
tosp=0x1f2a
0xe:pti[0]  nrow=4  offs=0
0x12:pri[0] offs=0x1f44
0x14:pri[1] offs=0x1f5e
0x16:pri[2] offs=0x1f74
0x18:pri[3] offs=0x1f88
block_row_dump:
tab 0, row 0, @0x1f44
tl: 26 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 0b
col  1: [10]  41 43 43 4f 55 4e 54 49 4e 47
col  2: [ 8]  6e 65 77 20 79 6f 72 6b
tab 0, row 1, @0x1f5e
tl: 22 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 15
col  1: [ 8]  52 45 53 45 41 52 43 48
col  2: [ 6]  64 61 6c 6c 61 73
tab 0, row 2, @0x1f74
tl: 20 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 1f
col  1: [ 5]  53 41 4c 45 53
col  2: [ 7]  63 68 69 63 61 67 6f
tab 0, row 3, @0x1f88
tl: 24 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 29
col  1: [10]  4f 50 45 52 41 54 49 4f 4e 53
col  2: [ 6]  62 6f 73 74 6f 6e
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 10 maxblk 10

SCOTT@test> select rowid,ora_rowscn,deptx.* from deptx ;
ROWID                ORA_ROWSCN       DEPTNO DNAME          LOC
------------------ ------------ ------------ -------------- -------------
AAAO1FAAGAAAAAKAAA  12688068875           10 ACCOUNTING     new york
AAAO1FAAGAAAAAKAAB  12688068875           20 RESEARCH       dallas
AAAO1FAAGAAAAAKAAC  12688068875           30 SALES          chicago
AAAO1FAAGAAAAAKAAD  12688068875           40 OPERATIONS     boston

SCOTT@test> alter system checkpoint ;
System altered.

SCOTT@test> alter system dump datafile 6 block 10 ;
System altered.

Block header dump:  0x0180000a
Object id on Block? Y
seg/obj: 0xed45  csc: 0x02.f44490bc  itc: 2  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.016.00001ff2  0x00800038.0ed0.0f  ----    4  fsc 0x0000.00000000
0x02   0x000a.022.00001ff2  0x00800037.0ed0.0b  C---    0  scn 0x0002.f4448f75

data_block_dump,data header at 0x8420c5c
===============
tsiz: 0x1fa0
hsiz: 0x1a
pbl: 0x08420c5c
bdba: 0x0180000a
     76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1f44
avsp=0x1f2a
tosp=0x1f2a
0xe:pti[0]  nrow=4  offs=0
0x12:pri[0] offs=0x1f44
0x14:pri[1] offs=0x1f5e
0x16:pri[2] offs=0x1f74
0x18:pri[3] offs=0x1f88
block_row_dump:
tab 0, row 0, @0x1f44
tl: 26 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 0b
col  1: [10]  41 43 43 4f 55 4e 54 49 4e 47
col  2: [ 8]  6e 65 77 20 79 6f 72 6b
tab 0, row 1, @0x1f5e
tl: 22 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 15
col  1: [ 8]  52 45 53 45 41 52 43 48
col  2: [ 6]  64 61 6c 6c 61 73
tab 0, row 2, @0x1f74
tl: 20 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 1f
col  1: [ 5]  53 41 4c 45 53
col  2: [ 7]  63 68 69 63 61 67 6f
tab 0, row 3, @0x1f88
tl: 24 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 29
col  1: [10]  4f 50 45 52 41 54 49 4f 4e 53
col  2: [ 6]  62 6f 73 74 6f 6e
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 10 maxblk 10

--可以发现一个奇特的情况,块没有变化,scn是从undo段来的.这样每次的逻辑读都要读undo段.

SYS@test> alter system checkpoint ;
System altered.

SYS@test> alter system flush buffer_cache ;
System altered.

SYS@test> @ &r/bh 6 10
old  23:   dbarfil = &1 and
new  23:   dbarfil = 6 and
old  24:   dbablk = &2
new  24:   dbablk = 10
HLADDR                DBARFIL       DBABLK        CLASS CLASS_TYPE         STATE               TCH   CR_SCN_BAS   CR_SCN_WRP   CR_UBA_FIL   CR_UBA_BLK   CR_UBA_SEQ BA               OBJECT_NAME
---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- --------------------
000000007B6FDFD8            6           10            1 data block         free                  0            0            0            0            0            0 000000006D9AC000 DEPTX
000000007B6FDFD8            6           10            1 data block         free                  0            0            0            0            0            0 000000006CF0C000 DEPTX
000000007B6FDFD8            6           10            1 data block         free                  0            0            0            0            0            0 000000006CCA0000 DEPTX
000000007B6FDFD8            6           10            1 data block         free                  0            0            0            0            0            0 000000006E01A000 DEPTX
000000007B6FDFD8            6           10            1 data block         free                  0            0            0            0            0            0 000000006D25C000 DEPTX
000000007B6FDFD8            6           10            1 data block         free                  0            0            0            0            0            0 000000006B074000 DEPTX
000000007B6FDFD8            6           10            1 data block         free                  0            0            0            0            0            0 000000006ACA8000 DEPTX
000000007B6FDFD8            6           10            1 data block         free                  0            0            0            0            0            0 000000006ACF4000 DEPTX
8 rows selected.

SYS@test> @ &r/bh 6 10
old  23:   dbarfil = &1 and
new  23:   dbarfil = 6 and
old  24:   dbablk = &2
new  24:   dbablk = 10
HLADDR                DBARFIL       DBABLK        CLASS CLASS_TYPE         STATE               TCH   CR_SCN_BAS   CR_SCN_WRP   CR_UBA_FIL   CR_UBA_BLK   CR_UBA_SEQ BA               OBJECT_NAME
---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- --------------------
000000007B6FDFD8            6           10            1 data block         cr                    1   4294967295        65535            0            0            0 000000006EB40000 DEPTX
000000007B6FDFD8            6           10            1 data block         free                  0            0            0            0            0            0 000000006D9AC000 DEPTX
000000007B6FDFD8            6           10            1 data block         free                  0            0            0            0            0            0 000000006CF0C000 DEPTX
000000007B6FDFD8            6           10            1 data block         free                  0            0            0            0            0            0 000000006CCA0000 DEPTX
000000007B6FDFD8            6           10            1 data block         free                  0            0            0            0            0            0 000000006E01A000 DEPTX
000000007B6FDFD8            6           10            1 data block         free                  0            0            0            0            0            0 000000006D25C000 DEPTX
000000007B6FDFD8            6           10            1 data block         free                  0            0            0            0            0            0 000000006B074000 DEPTX
000000007B6FDFD8            6           10            1 data block         free                  0            0            0            0            0            0 000000006ACA8000 DEPTX
000000007B6FDFD8            6           10            1 data block         free                  0            0            0            0            0            0 000000006ACF4000 DEPTX
9 rows selected.

--出现的STATE=CR块. 而不是XCUR.

SYS@test> alter system flush buffer_cache ;
System altered.

SCOTT@test> @ &r/10046on 12
Session altered.

SCOTT@test> select rowid,ora_rowscn,deptx.* from deptx ;
ROWID                ORA_ROWSCN       DEPTNO DNAME          LOC
------------------ ------------ ------------ -------------- -------------
AAAO1FAAGAAAAAKAAA  12688068875           10 ACCOUNTING     new york
AAAO1FAAGAAAAAKAAB  12688068875           20 RESEARCH       dallas
AAAO1FAAGAAAAAKAAC  12688068875           30 SALES          chicago
AAAO1FAAGAAAAAKAAD  12688068875           40 OPERATIONS     boston

SCOTT@test> @ &r/10046off
Session altered.

=====================
PARSING IN CURSOR #9 len=43 dep=0 uid=57 oct=3 lid=57 tim=1395066357333567 hv=1599918056 ad='75289110'
select rowid,ora_rowscn,deptx.* from deptx
END OF STMT
PARSE #9:c=1000,e=740,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1395066357333561
BINDS #9:
EXEC #9:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1395066357333671
WAIT #9: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1395066357333711
WAIT #9: nam='db file sequential read' ela= 9 file#=6 block#=9 blocks=1 obj#=60741 tim=1395066357333776
WAIT #9: nam='db file scattered read' ela= 32 file#=6 block#=10 blocks=7 obj#=60741 tim=1395066357333990
WAIT #9: nam='db file sequential read' ela= 8 file#=2 block#=153 blocks=1 obj#=0 tim=1395066357334040
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FETCH #9:c=0,e=350,p=9,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=1395066357334092
WAIT #9: nam='SQL*Net message from client' ela= 387 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1395066357334518
WAIT #9: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1395066357334573
FETCH #9:c=0,e=45,p=0,cr=1,cu=0,mis=0,r=3,dep=0,og=1,tim=1395066357334603
*** 2015-04-09 10:52:37.005
WAIT #9: nam='SQL*Net message from client' ela= 6928105 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1395066364262753
STAT #9 id=1 cnt=4 pid=0 pos=1 obj=60741 op='TABLE ACCESS FULL DEPTX (cr=5 pr=9 pw=0 time=337 us)'
=====================

--注意看~,可以发现要读file#=2 block#=153 blocks=1.而这个正是undo文件.

SYS@test> column name format a35
SYS@test> show parameter undo_tablespace
NAME             TYPE     VALUE
---------------- -------- ---------
undo_tablespace  string   UNDOTBS1

SYS@test> SELECT name,file#, CHECKPOINT_CHANGE#, UNRECOVERABLE_CHANGE# ,LAST_CHANGE#, OFFLINE_CHANGE#, ONLINE_CHANGE#,status FROM v$datafile where file#=2;
NAME                                       FILE# CHECKPOINT_CHANGE# UNRECOVERABLE_CHANGE# LAST_CHANGE# OFFLINE_CHANGE# ONLINE_CHANGE# STATUS
----------------------------------- ------------ ------------------ --------------------- ------------ --------------- -------------- -------
/mnt/ramdisk/test/undotbs01.dbf                2        12688069220                     0                            0              0 ONLINE

--另外你可以看到一个奇怪的现象:ORA_ROWSCN会变(顺着时间的推移,原来undo slot的信息会被覆盖).我关闭数据库再打开.

SCOTT@test> select rowid,ora_rowscn,deptx.* from deptx ;

ROWID                ORA_ROWSCN       DEPTNO DNAME          LOC
------------------ ------------ ------------ -------------- -------------
AAAO1FAAGAAAAAKAAA  12688069729           10 ACCOUNTING     new york
AAAO1FAAGAAAAAKAAB  12688069729           20 RESEARCH       dallas
AAAO1FAAGAAAAAKAAC  12688069729           30 SALES          chicago
AAAO1FAAGAAAAAKAAD  12688069729           40 OPERATIONS     boston

--再关闭数据库再打开.
SCOTT@test> select rowid,ora_rowscn,deptx.* from deptx ;

ROWID                ORA_ROWSCN       DEPTNO DNAME          LOC
------------------ ------------ ------------ -------------- -------------
AAAO1FAAGAAAAAKAAA  12688070201           10 ACCOUNTING     new york
AAAO1FAAGAAAAAKAAB  12688070201           20 RESEARCH       dallas
AAAO1FAAGAAAAAKAAC  12688070201           30 SALES          chicago
AAAO1FAAGAAAAAKAAD  12688070201           40 OPERATIONS     boston

SYS@test> @&r/bh 6 10

HLADDR                DBARFIL       DBABLK        CLASS CLASS_TYPE         STATE               TCH   CR_SCN_BAS   CR_SCN_WRP   CR_UBA_FIL   CR_UBA_BLK   CR_UBA_SEQ BA               OBJECT_NAME
---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- --------------------
000000007B6FDFD8            6           10            1 data block         cr                    1   4294967295        65535            0            0            0 000000006C8FA000 DEPTX

总结:
1.对于只读表空间数据文件,出现延迟块清除,在下次访问块时是通过undo构造块,但是由于是只读,不会更改块内信息.
2.这样每次访问都会访问undo段,如果大量出现,也许会影响性能.

--补充一些测试:
SCOTT@test> alter tablespace mssm read write  ;
Tablespace altered.

SCOTT@test> set autot traceonly
SCOTT@test> select rowid,ora_rowscn,deptx.* from deptx ;
Execution Plan
----------------------------------------------------------
Plan hash value: 428208148
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     4 |    80 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DEPTX |     4 |    80 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          8  physical reads
        116  redo size
        983  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed
--可以发现产生redo.

SCOTT@test> set autot off

SYS@test> alter system checkpoint ;
System altered.

SYS@test> alter system dump datafile 6 block 10;
System altered.

Block header dump:  0x0180000a
Object id on Block? Y
seg/obj: 0xed45  csc: 0x02.f44497e6  itc: 2  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.016.00001ff2  0x00800038.0ed0.0f  C-U-    0  scn 0x0002.f4449645 0x02   0x000a.022.00001ff2  0x00800037.0ed0.0b  C---    0  scn 0x0002.f4448f75

SCOTT@test> select power(2,33)+to_number('f4449645','xxxxxxxxxxxxxx') from dual;
POWER(2,33)+TO_NUMBER('F4449645','XXXXXXXXXXXXXX')
--------------------------------------------------
                                       12688070213

SCOTT@test> select rowid,ora_rowscn,deptx.* from deptx ;
ROWID                ORA_ROWSCN       DEPTNO DNAME          LOC
------------------ ------------ ------------ -------------- -------------
AAAO1FAAGAAAAAKAAA  12688070213           10 ACCOUNTING     new york
AAAO1FAAGAAAAAKAAB  12688070213           20 RESEARCH       dallas
AAAO1FAAGAAAAAKAAC  12688070213           30 SALES          chicago
AAAO1FAAGAAAAAKAAD  12688070213           40 OPERATIONS     boston

时间： 2024-09-23 23:39:03

[20150409]只读表空间与延迟块清除.txt的相关文章

[20150408]只读表空间以及数据库恢复4.txt

[20150408]只读表空间以及数据库恢复4.txt 参考链接: http://blog.itpub.net/267265/viewspace-1544583/ http://blog.itpub.net/267265/viewspace-1548059/ http://blog.itpub.net/267265/viewspace-1548967/ --上午做了测试,通过新建控制文件的方式来恢复,实际上更常用的方式使用bbed,修改数据文件块1,保持与控制文件的记录一致. --通过对比来看看

[20150408]只读表空间以及数据库恢复2.txt

[20150408]只读表空间以及数据库恢复2.txt --昨天检查时发现1个小问题,就是有1个表空间设置只读,也许某个时间打开变成读写,又设置会只读,而备份仅仅在第1次设置只读时做过1次, --按照道理如果使用原来的备份恢复会存在问题的,自己测试1下,顺便看看有什么变通的方法解决这个问题. 1.建立测试环境: SCOTT@test> @ &r/ver1 PORT_STRING                    VERSION        BANNER ----------------

[20150408]只读表空间以及数据库恢复3.txt

[20150408]只读表空间以及数据库恢复3.txt --昨天检查时发现1个小问题,就是有1个表空间设置只读,也许某个时间打开变成读写,又设置会只读,而备份仅仅在第1次设置只读时做过1次, --按照道理如果使用原来的备份恢复会存在问题的,自己测试1下,我上午已经测试了可以使用以前的做了来恢复,但是这里存在一些小问题, --我在第2次打开读写时,没有任何ddl,dml操作对这个表空间,如果存在这些操作会出现什么情况呢? --继续上午的测试. 1.建立测试环境: SCOTT@test> @ &

[20170420]关于延迟块清除3.txt

[20170420]关于延迟块清除3.txt --昨天做延迟块清除测试时,有一个问题我自己一直没搞明白,就是把表空间设置为只读的情况下,当访问块时, --由于没法更新对应块,不知道为什么每次重启数据库,正常undo的事务槽不可能这么块覆盖,为什么ora_rowscn --总是变化,而且取的是control scn,要认真探究一下问题在那里. --上午测试没有测试出来,链接http://blog.itpub.net/267265/viewspace-2137714/ => [20170420]关于

[20170420]关于延迟块清除2.txt

[20170420]关于延迟块清除2.txt --昨天做延迟块清除测试时,有一个问题我自己一直没搞明白,就是把表空间设置为只读的情况下,当访问块时, --由于没法更新对应块,不知道为什么每次重启数据库,正常undo的事务槽不可能这么块覆盖,为什么ora_rowscn --总是变化,而且取的是control scn,要认真探究一下问题在那里. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BA

[20150414]只读表空间与没有提交事务.txt

[20150414]只读表空间与没有提交事务.txt --如果1个表空间在设置只读前,存在没有提交的事务,会出现什么情况呢?自己做1个测试: 1.建立测试环境: SCOTT@test> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ----------------------------------------------

备份和恢复Oracle只读表空间的方法

--====================== -- 只读表空间的备份与恢复 --====================== 一.只读表空间的特性 使用只读表空间避免对静态数据的频繁备份 当使用alter tablespace tbs read only时,数据文件会执行检查点进程(将所有脏缓冲区的内容写至磁盘), 当前的SCN号会被标注,同时存储了SCN的数据文件头部被冻结.控制文件内也会记录该数据文件的冻结信息. 可以清除只读表空间的对象 二.只读表空间的备份 一般情况下,只读表空间只需

Oracle中如何设置EXCLUDE后STANDBY数据库只读表空间的恢复

在STANDBY数据库利用RMAN恢复主库上EXCLUDE的只读表空间,碰到了问题. 数据库恢复完成,但是恢复被主库EXCLUDE的只读表空间时,发现无法进行恢复: RMAN> restore tablespace clubstat2_bak; Starting restore at 14-FEB-11 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=1094 devtype=DISK file 22 is excluded from

[20170428]延迟块清除测试.txt

[20170428]延迟块清除测试.txt --//做一个延迟块清除测试,前面的测试太乱了,思路非常不清楚. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ------------------------------------------------------------------