分类目录归档：Oracle

通过bbed模拟ORA-00607/ORA-00600 4194 故障

发表于 2012 年 8 月 8 日 由 惜分飞

在数据库恢复的案例中,遇到system rollback异常的故障算是中彩票了.处理起来比较麻烦,有些情况甚至是无法处理.这里通过试验模拟ORA-00607/ORA-00600[4194].类此的错误在一次银联的数据库恢复中也遇到过,不过当时由于功底不深,理解出现部分误差.
通过bbed模拟ORA-00607/ORA-00600[4194]错误

[oracle@xifenfei ~]$ bbed listfile=list mode=edit password=blockedit

BBED: Release 2.0.0.0.0 - Limited Production on Fri Nov 4 22:59:51 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /u01/oracle/oradata/XFF/system01.dbf                                 0
     2  /u01/oracle/oradata/XFF/undotbs01.dbf                                0
     3  /u01/oracle/oradata/XFF/sysaux01.dbf                                 0
     4  /u01/oracle/oradata/XFF/users01.dbf                                  0
     5  /u01/oracle/oradata/XFF/datfttuser.dbf                               0

BBED> set block 9
        BLOCK#          9

BBED> map
 File: /u01/oracle/oradata/XFF/system01.dbf (1)
 Block: 9                                     Dba:0x00400009
------------------------------------------------------------
 Unlimited Undo Segment Header

 struct kcbh, 20 bytes                      @0       

 struct ktech, 72 bytes                     @20      

 struct ktemh, 16 bytes                     @92      

 struct ktetb[6], 48 bytes                  @108     

 struct ktuxc, 104 bytes                    @4148    

 struct ktuxe[255], 10200 bytes             @4252    

 ub4 tailchk                                @8188    


BBED> p ktuxc
struct ktuxc, 104 bytes                     @4148    
   struct ktuxcscn, 8 bytes                 @4148    
      ub4 kscnbas                           @4148     0x0006c75b
      ub2 kscnwrp                           @4152     0x0000
   struct ktuxcuba, 8 bytes                 @4156    
      ub4 kubadba                           @4156     0x00400012
      ub2 kubaseq                           @4160     0x0037
      ub1 kubarec                           @4162     0x1f
   sb2 ktuxcflg                             @4164     1 (KTUXCFSK)
   ub2 ktuxcseq                             @4166     0x0037
   sb2 ktuxcnfb                             @4168     1            <==free undo block num
   ub4 ktuxcinc                             @4172     0x00000000
   sb2 ktuxcchd                             @4176     34
   sb2 ktuxcctl                             @4178     32
   ub2 ktuxcmgc                             @4180     0x8002
   ub4 ktuxcopt                             @4188     0x7ffffffe
   struct ktuxcfbp[0], 12 bytes             @4192    
      struct ktufbuba, 8 bytes              @4192    
         ub4 kubadba                        @4192     0x00400013    <==uba (模拟试验修改为其他uba地址)
         ub2 kubaseq                        @4196     0x0037        <==uba sequence
         ub1 kubarec                        @4198     0x05
      sb2 ktufbext                          @4200     1
      sb2 ktufbspc                          @4202     7200
   struct ktuxcfbp[1], 12 bytes             @4204    
      struct ktufbuba, 8 bytes              @4204    
         ub4 kubadba                        @4204     0x00000000
         ub2 kubaseq                        @4208     0x0035
         ub1 kubarec                        @4210     0x2a
      sb2 ktufbext                          @4212     5
      sb2 ktufbspc                          @4214     3446
   struct ktuxcfbp[2], 12 bytes             @4216    
      struct ktufbuba, 8 bytes              @4216    
         ub4 kubadba                        @4216     0x00000000
         ub2 kubaseq                        @4220     0x0035
         ub1 kubarec                        @4222     0x37
      sb2 ktufbext                          @4224     5
      sb2 ktufbspc                          @4226     1336
   struct ktuxcfbp[3], 12 bytes             @4228    
      struct ktufbuba, 8 bytes              @4228    
         ub4 kubadba                        @4228     0x00000000
         ub2 kubaseq                        @4232     0x0000
         ub1 kubarec                        @4234     0x00
      sb2 ktufbext                          @4236     0
      sb2 ktufbspc                          @4238     0
   struct ktuxcfbp[4], 12 bytes             @4240    
      struct ktufbuba, 8 bytes              @4240    
         ub4 kubadba                        @4240     0x00000000
         ub2 kubaseq                        @4244     0x0000
         ub1 kubarec                        @4246     0x00
      sb2 ktufbext                          @4248     0
      sb2 ktufbspc                          @4250     0

BBED> set dba 0x00400013
        DBA             0x00400013 (4194323 1,19)

BBED> p ktubh
struct ktubh, 26 bytes                      @20      
   struct ktubhxid, 8 bytes                 @20      
      ub2 kxidusn                           @20       0x0000
      ub2 kxidslt                           @22       0x0020
      ub4 kxidsqn                           @24       0x00000029
   ub2 ktubhseq                             @28       0x0037    <==uba seq
   ub1 ktubhcnt                             @30       0x05
   ub1 ktubhirb                             @31       0x05
   ub1 ktubhicl                             @32       0x00
   ub1 ktubhflg                             @33       0x00
   ub2 ktubhidx[0]                          @34       0x1fe8
   ub2 ktubhidx[1]                          @36       0x1f2c
   ub2 ktubhidx[2]                          @38       0x1e70
   ub2 ktubhidx[3]                          @40       0x1db4
   ub2 ktubhidx[4]                          @42       0x1cf8
   ub2 ktubhidx[5]                          @44       0x1c3c

BBED> set dba 0x00400012
        DBA             0x00400012 (4194322 1,18)

BBED> p ktubh
struct ktubh, 86 bytes                      @20      
   struct ktubhxid, 8 bytes                 @20      
      ub2 kxidusn                           @20       0x0000
      ub2 kxidslt                           @22       0x0020
      ub4 kxidsqn                           @24       0x00000029
   ub2 ktubhseq                             @28       0x0037
   ub1 ktubhcnt                             @30       0x23
   ub1 ktubhirb                             @31       0x23
   ub1 ktubhicl                             @32       0x00
   ub1 ktubhflg                             @33       0x00
   ub2 ktubhidx[0]                          @34       0x1fe8
   …………
   ub2 ktubhidx[35]                         @104      0x00b4

BBED> set block 9
        BLOCK#          9

BBED> set count 16
        COUNT           16

BBED> m /x 12004000 offset 4192
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/XFF/system01.dbf (1)
 Block: 9                Offsets: 4192 to 4207           Dba:0x00400009
------------------------------------------------------------------------
 12004000 37000500 0100201c 00000000 

 <32 bytes per line>

BBED>  p ktuxc
struct ktuxc, 104 bytes                     @4148    
   struct ktuxcscn, 8 bytes                 @4148    
      ub4 kscnbas                           @4148     0x0006c75b
      ub2 kscnwrp                           @4152     0x0000
   struct ktuxcuba, 8 bytes                 @4156    
      ub4 kubadba                           @4156     0x00400012
      ub2 kubaseq                           @4160     0x0037
      ub1 kubarec                           @4162     0x1f
   sb2 ktuxcflg                             @4164     1 (KTUXCFSK)
   ub2 ktuxcseq                             @4166     0x0037
   sb2 ktuxcnfb                             @4168     1
   ub4 ktuxcinc                             @4172     0x00000000
   sb2 ktuxcchd                             @4176     34
   sb2 ktuxcctl                             @4178     32
   ub2 ktuxcmgc                             @4180     0x8002
   ub4 ktuxcopt                             @4188     0x7ffffffe
   struct ktuxcfbp[0], 12 bytes             @4192    
      struct ktufbuba, 8 bytes              @4192    
         ub4 kubadba                        @4192     0x00400012  <==uba已经被修改
         ub2 kubaseq                        @4196     0x0037
         ub1 kubarec                        @4198     0x05
      sb2 ktufbext                          @4200     1
      sb2 ktufbspc                          @4202     7200
   struct ktuxcfbp[1], 12 bytes             @4204    
      struct ktufbuba, 8 bytes              @4204    
         ub4 kubadba                        @4204     0x00000000
         ub2 kubaseq                        @4208     0x0035
         ub1 kubarec                        @4210     0x2a
      sb2 ktufbext                          @4212     5
      sb2 ktufbspc                          @4214     3446
   struct ktuxcfbp[2], 12 bytes             @4216    
      struct ktufbuba, 8 bytes              @4216    
         ub4 kubadba                        @4216     0x00000000
         ub2 kubaseq                        @4220     0x0035
         ub1 kubarec                        @4222     0x37
      sb2 ktufbext                          @4224     5
      sb2 ktufbspc                          @4226     1336
   struct ktuxcfbp[3], 12 bytes             @4228    
      struct ktufbuba, 8 bytes              @4228    
         ub4 kubadba                        @4228     0x00000000
         ub2 kubaseq                        @4232     0x0000
         ub1 kubarec                        @4234     0x00
      sb2 ktufbext                          @4236     0
      sb2 ktufbspc                          @4238     0
   struct ktuxcfbp[4], 12 bytes             @4240    
      struct ktufbuba, 8 bytes              @4240    
         ub4 kubadba                        @4240     0x00000000
         ub2 kubaseq                        @4244     0x0000
         ub1 kubarec                        @4246     0x00
      sb2 ktufbext                          @4248     0
      sb2 ktufbspc                          @4250     0

BBED> sum apply
Check value for File 1, Block 9:
current = 0xe686, required = 0xe686

启动数据库

SQL> startup
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1219160 bytes
Variable Size              96470440 bytes
Database Buffers          213909504 bytes
Redo Buffers                7168000 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

alert日志

Fri Nov  4 23:10:37 2011
SMON: enabling cache recovery
Fri Nov  4 23:10:37 2011
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
ARC2 started with pid=18, OS id=21535
Fri Nov  4 23:10:38 2011
Errors in file /u01/oracle/admin/XFF/udump/xff_ora_21529.trc:
ORA-00600: internal error code, arguments: [4194], [35], [6], [], [], [], [], []
Fri Nov  4 23:10:41 2011
Doing block recovery for file 1 block 18
Block recovery from logseq 2, block 48668 to scn 458453
Fri Nov  4 23:10:41 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
  Mem# 0 errs 0: /u01/oracle/oradata/XFF/redo01.log
Block recovery stopped at EOT rba 2.48670.16
Block recovery completed at rba 2.48670.16, scn 0.458451
Doing block recovery for file 1 block 9
Block recovery from logseq 2, block 48668 to scn 458450
Fri Nov  4 23:10:41 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
  Mem# 0 errs 0: /u01/oracle/oradata/XFF/redo01.log
Block recovery completed at rba 2.48670.16, scn 0.458451
Fri Nov  4 23:10:41 2011
Errors in file /u01/oracle/admin/XFF/udump/xff_ora_21529.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [35], [6], [], [], [], [], []
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Instance terminated by USER, pid = 21529
ORA-1092 signalled during: ALTER DATABASE OPEN...
发表在 非常规恢复 | 标签为 , , | 评论关闭

创建控制文件遭遇ORA-00600[3753]故障解决

发表于 2012 年 8 月 7 日 由 惜分飞

一位网友的数据库正常关闭,然后控制文件意外丢失,需要通过trace中的信息重建控制文件,但是在重建的过程中,出现ORA-00600[3753]错误,远程帮忙处理,记录处理过程如下
1.启动数据库至nomount状态,然后尝试noresetlogs模式重建控制文件

SQL>@XFF_NORESETLOGS_CTL.sql
CREATE CONTROLFILE REUSE DATABASE "ORA10G" NORESETLOGS  ARCHIVELOG
*
第 1 行出现错误:
ORA-01503: CREATE CONTROLFILE 失败
ORA-00600: 内部错误代码, 参数: [3753], [3], [2], [], [], [], [], []

2.检查alert日志

Tue Aug 07 20:40:47 2012
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Tue Aug 07 20:40:48 2012
Errors in file d:\oracle\product\10.2.0\db_1\admin\ora10g\udump\ora10g_ora_11596.trc:
ORA-00600: 内部错误代码, 参数: [3753], [3], [2], [], [], [], [], []

Tue Aug 07 20:40:53 2012
Errors in file d:\oracle\product\10.2.0\db_1\admin\ora10g\udump\ora10g_ora_11596.trc:
ORA-00600: 内部错误代码, 参数: [3753], [3], [2], [], [], [], [], []

ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "ORA10G" NORESETLOGS  ARCHIVELOG

3.分析trace文件

Tue Aug 07 20:40:48 2012
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows NT Version V6.1 Service Pack 1
CPU                 : 2 - type 586, 2 Physical Cores
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:166M/1901M, Ph+PgF:619M/5536M, VA:812M/2047M
Instance name: ora10g

Redo thread mounted by this instance: 0 <none>

Oracle process number: 16

Windows thread id: 11596, image: ORACLE.EXE (SHAD)


*** SERVICE NAME:() 2012-08-07 20:40:48.413
*** SESSION ID:(158.7) 2012-08-07 20:40:48.413
*** 2012-08-07 20:40:48.413
ksedmp: internal or fatal error
ORA-00600: 内部错误代码, 参数: [3753], [3], [2], [], [], [], [], []
Current SQL statement for this session:
CREATE CONTROLFILE REUSE DATABASE "ORA10G" NORESETLOGS  ARCHIVELOG
…………
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
_ksedst+38           CALLrel  _ksedst1+0           0 1
_ksedmp+898          CALLrel  _ksedst+0            0
_ksfdmp+14           CALLrel  _ksedmp+0            3
603A816A             CALLreg  00000000             87CF110 3
603A83FF             CALLrel  603A80D8             87CF110 8191090 EA9 2 8CCC438
_ksesic2+59          CALLrel  _kgesiv+0            87CF110 8191090 EA9 2 8CCC438
                                                   EA9 2 8CCC438
__VInfreq__kctbce+1  CALLrel  _ksesic2+0           EA9 0 3 0 0 2 0
63                                                 
_kcfccfl+356         CALLrel  _kctbce+0            543414C 81DB8A8
_cdbdrv+1037         CALLrel  _kcfccfl+0           543414C 1 8CCD060 8CCD04C
                                                   19000 3
_opiexe+11999        CALLrel  _cdbdrv+0            1
_opiosq0+6088        CALLrel  _opiexe+0            4 0 8CCD894
_kpooprx+232         CALLrel  _opiosq0+0           3 E 8CCD9AC A4
_kpoal8+775          CALLrel  _kpooprx+0           8CCF6CC 8196414 A16 1 0 A4
_opiodr+1099         CALLreg  00000000             5E 17 8CCF6C8
60FEFF8D             CALLreg  00000000             5E 17 8CCF6C8 0
_opitsk+1017         CALL???  00000000             
_opiino+1087         CALLrel  _opitsk+0            0 0
_opiodr+1099         CALLreg  00000000             3C 4 8CCFC60
_opidrv+819          CALLrel  _opiodr+0            3C 4 8CCFC60 0
_sou2o+45            CALLrel  _opidrv+0            3C 4 8CCFC60
_opimai_real+112     CALLrel  _sou2o+0             8CCFC54 3C 4 8CCFC60
_opimai+92           CALLrel  _opimai_real+0       2 8CCFC8C
_OracleThreadStart@  CALLrel  _opimai+0            
4+708                                              
__pRawDllMain+10931  CALLptr  00000000             
2903                                               
__pRawDllMain+12925  CALLreg  00000000             
4809                                               
__pRawDllMain+12925  CALLrel  __pRawDllMain+12925  
4761                          4772                 
 
--------------------- Binary Stack Dump ---------------------

    ----------------------------------------
    SO: 4FB3DF5C, type: 4, owner: 4FA4CBFC, flag: INIT/-/-/0x00
    (session) sid: 158 trans: 4EBB8954, creator: 4FA4CBFC, flag: (41) USR/- BSY/-/-/-/-/-
              DID: 0000-0010-0000000A, short-term DID: 0000-0000-00000000
              txn branch: 00000000
              oct: 0, prv: 0, sql: 00000000, psql: 4F707298, user: 0/SYS
    O/S info: user: superv06-PC\superv06, term: SUPERV06-PC, ospid: 7788:11636, machine: WORKGROUP\SUPERV06-PC
              program: sqlplus.exe
    application name: sqlplus.exe, hash value=0
    last wait for 'log file sequential read' blocking sess=0x00000000 seq=31 
    wait_time=159 seconds since wait started=0
                log#=0, block#=1, blocks=1
    Dumping Session Wait History
     for 'log file sequential read' count=1 wait_time=159
                log#=0, block#=1, blocks=1
     for 'log file sequential read' count=1 wait_time=502
                log#=0, block#=1, blocks=1
     for 'log file sequential read' count=1 wait_time=163
                log#=0, block#=1, blocks=1
     for 'db file sequential read' count=1 wait_time=18840
                file#=ffffffff, block#=1, blocks=1
     for 'db file sequential read' count=1 wait_time=254
                file#=ffffffff, block#=1, blocks=1
     for 'db file sequential read' count=1 wait_time=7654
                file#=ffffffff, block#=1, blocks=1
     for 'db file sequential read' count=1 wait_time=150
                file#=ffffffff, block#=1, blocks=1
     for 'db file sequential read' count=1 wait_time=102
                file#=ffffffff, block#=1, blocks=1
     for 'db file sequential read' count=1 wait_time=123
                file#=ffffffff, block#=1, blocks=1
     for 'db file sequential read' count=1 wait_time=14010
                file#=ffffffff, block#=1, blocks=1

通过这里我们发现创建控制文件的进程在读取redo log的时候出现了等待比较多而且时间比较长,而对于ORA-00600[3753]错误互联网上没有任何更多的信息.通过对于创建控制文件时候因为使用noresetlogs的分析:这种模式下需要读取redo log,所以导致等待较多,从而出现ORA-00600[3753]错误使得创建控制文件失败.因为本库是shutdown immediate关闭,所以我们完全可以通过resetlogs模式来创建控制文件,从而避免读取redo log.

4.创建resetlogs控制文件

SQL>@XFF_RESETLOGS_CTL.sql

Control file created.

5.然后不完全恢复使用resetlogs open数据库

这次的处理我也没有什么经验可以借鉴,MOS和互联网上没有该错误的任何信息,解决这个问题关键凭的是自己对于noresetlogs和resetlogs的理解.对于数据库原理的理解,对解决一些陌生问题帮助很大;在学习ORACLE过程中注重对原理的理解和消化

发表在 ORA-xxxxx, Oracle备份恢复 | 标签为 | 一条评论

v$datafile.enabled相关值说明

发表于 2012 年 8 月 6 日 由 惜分飞

自认为对v$datafile视图算比较了解,但是今天看到一网友的v$datafile.enabled=DISABLED,我一时没有想出来是为什么,这里通过试验对v$datafile.enabled各种情况进行了试验并简单说明scn的变化情况
模拟环境(READ WRITE)

SQL> alter tablespace users add datafile '/u01/oracle/oradata/XFF/users02.dbf' size 10m;

Tablespace altered.

SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- ---------------------------------------
         1          0             456727 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             456727 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             456727 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             456727 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458322 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf

QL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              456727
         2 ONLINE              456727
         3 ONLINE              456727
         4 ONLINE              456727
         5 ONLINE              458322

加入数据文件scn不一样是因为:加入数据文件的时候,使用是当前scn,而数据库未做checkpoint,所以出现数据文件scn不一致现象

datafile offline(READ WRITE)

SQL> alter database datafile 5 offline;

Database altered.

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458392
         2 ONLINE              458392
         3 ONLINE              458392
         4 ONLINE              458392
         5 OFFLINE             458322

SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- ----------------------------------------
         1          0             458392 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458322 READ WRITE RECOVER /u01/oracle/oradata/XFF/users02.dbf

这里可以看出来数据文件offline之后,v$datafile.enabled依然是READ WRITE,但是该数据文件的scn不再变化

tablespace offline(DISABLED)

SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;

Database altered.

SQL>  select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------
         1          0             458392 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458430 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf

SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458392
         2 ONLINE              458392
         3 ONLINE              458392
         4 ONLINE              458392
         5 ONLINE              458430

SQL> alter system checkpoint;

System altered.

SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- ---------------------------------------
         1          0             458443 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458443 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458443 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458443 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458443 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf

SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458443
         2 ONLINE              458443
         3 ONLINE              458443
         4 ONLINE              458443
         5 ONLINE              458443

SQL> alter tablespace users offline;

Tablespace altered.

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------
         1          0             458497 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458497 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458497 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458457 DISABLED   OFFLINE /u01/oracle/oradata/XFF/users01.dbf
         5          4             458457 DISABLED   OFFLINE /u01/oracle/oradata/XFF/users02.dbf

SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458497
         2 ONLINE              458497
         3 ONLINE              458497
         4 OFFLINE                  0
         5 OFFLINE                  0

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458512 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458512 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458512 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458457 DISABLED   OFFLINE /u01/oracle/oradata/XFF/users01.dbf
         5          4             458457 DISABLED   OFFLINE /u01/oracle/oradata/XFF/users02.dbf
 
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458512
         2 ONLINE              458512
         3 ONLINE              458512
         4 OFFLINE                  0
         5 OFFLINE                  0

SQL> alter tablespace users online;

Tablespace altered.

SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458512
         2 ONLINE              458512
         3 ONLINE              458512
         4 ONLINE              458526
         5 ONLINE              458526

SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458512 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458512 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458512 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458526 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458526 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf

SQL> alter system checkpoint;

System altered.

SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458551 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf

SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458551
         2 ONLINE              458551
         3 ONLINE              458551
         4 ONLINE              458551
         5 ONLINE              458551

以上部分证明：
1.online datafile也不触发database checkpoint
2.tablespace offline后v$datafile.enabled为DISABLED
3.控制文件对应表空间scn不再变化,datafile header scn变为0
4.tablespace online不需要recover

tablespace read only(READ ONLY)

SQL> alter tablespace users read only;

Tablespace altered.

SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458551 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458567 READ ONLY  ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458567 READ ONLY  ONLINE  /u01/oracle/oradata/XFF/users02.dbf

SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458551
         2 ONLINE              458551
         3 ONLINE              458551
         4 ONLINE              458567
         5 ONLINE              458567

SQL> alter system checkpoint;

System altered.

SQL>  select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458581 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458581 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458581 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458567 READ ONLY  ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458567 READ ONLY  ONLINE  /u01/oracle/oradata/XFF/users02.dbf

SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458581
         2 ONLINE              458581
         3 ONLINE              458581
         4 ONLINE              458567
         5 ONLINE              458567

SQL> alter tablespace users read write;

Tablespace altered.

SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458581 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458581 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458581 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458635 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458635 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf

SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458581
         2 ONLINE              458581
         3 ONLINE              458581
         4 ONLINE              458635
         5 ONLINE              458635

SQL> alter system checkpoint;

System altered.

SQL>  select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;

     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458649 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458649 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458649 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458649 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458649 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf

SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458649
         2 ONLINE              458649
         3 ONLINE              458649
         4 ONLINE              458649
         5 ONLINE              458649

以上部分证明：
1.tablespace read only 对应的v$datafile.enabled为READ ONLY
2.tablespace read only与tablespace read write之间的转换也不会触发database checkpoint

补充说明
官方文档给出来的v$datafile.enabled有以下几种
DISABLED – No SQL access allowed
READ ONLY – No SQL updates allowed
READ WRITE – Full access allowed
UNKNOWN – should not occur unless the control file is corrupted
但是对于UNKNOWN我通过模拟重建控制文件,缺少数据文件(为READ WRITE),通过在线删除数据文件(为READ WRITE),都不能出现UNKNOWN状态,如果知道的朋友请告知我.

发表在 Oracle | 评论关闭