分类目录归档：Oracle

找出dbv相关dba值在数据文件中对应位置

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

一个朋友数据库因异常断电,数据库不能正常启动,使用dbv检测错误如下:

C:\Users\XIFENFEI\Downloads>dbv file=users01.dbf end=5

DBVERIFY: Release 11.2.0.3.0 - Production on 星期二 6月 5 18:17:27 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - 开始验证: FILE = C:\USERS\XIFENFEI\DOWNLOADS\USERS01.DBF
页 1 标记为损坏
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Bad header found during dbv:
Data in bad block:
 type: 11 format: 2 rdba: 0x00000000
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000b01
 check value in block header: 0x2f1f
 computed block checksum: 0x0

页 2 标记为损坏
Corrupt block relative dba: 0x00000002 (file 0, block 2)
Bad header found during dbv:
Data in bad block:
 type: 29 format: 2 rdba: 0x01000002
 last change scn: 0x0000.0018c7fa seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xc7fa1d02
 check value in block header: 0x82ca
 computed block checksum: 0x0

页 3 标记为损坏
Corrupt block relative dba: 0x00000003 (file 0, block 3)
Bad header found during dbv:
Data in bad block:
 type: 30 format: 2 rdba: 0x01000003
 last change scn: 0x0000.0018c7fa seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xc7fa1e01
 check value in block header: 0x32c9
 computed block checksum: 0x0

页 4 标记为损坏
Corrupt block relative dba: 0x00000004 (file 0, block 4)
Bad header found during dbv:
Data in bad block:
 type: 30 format: 2 rdba: 0x01000004
 last change scn: 0x0000.00004adc seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x4adc1e01
 check value in block header: 0x8199
 computed block checksum: 0x0

页 5 标记为损坏
Corrupt block relative dba: 0x00000005 (file 0, block 5)
Bad header found during dbv:
Data in bad block:
 type: 30 format: 2 rdba: 0x01000005
 last change scn: 0x0000.00004ade seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x4ade1e01
 check value in block header: 0xc190
 computed block checksum: 0x0



DBVERIFY - 验证完成

检查的页总数: 5
处理的页总数 (数据): 0
失败的页总数 (数据): 0
处理的页总数 (索引): 0
失败的页总数 (索引): 0
处理的页总数 (其他): 0
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 0
标记为损坏的总页数: 5
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 0 (0.0)

对于这样的错误,特别是Corrupt block relative dba出现奇怪的提示(file 0),我第一反应就是数据文件header出现了问题.在eygle的耐心帮忙和提示下,使用bbed重现了该错误,并且找出了dbv中两个dba(Corrupt block relative dba和rdba)和bbed中相对应的值.通过实验重现相关结果.

dbv检查无坏块

[oracle@xifenfei tmp]$ dbv file=/u01/oracle/oradata/ora11g/xifenfei02.dbf blocksize=8192

DBVERIFY: Release 11.2.0.3.0 - Production on Fri Jun 1 03:34:46 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/ora11g/xifenfei02.dbf
Block Checking: DBA = 25179275, Block Type = KTB-managed data block
data header at 0xb526707c
kdbchk: row locked by non-existent transaction
        table=0   slot=0
        lockid=2   ktbbhitc=3
Page 13451 failed with check code 6101
Block Checking: DBA = 25179287, Block Type = KTB-managed data block
data header at 0xb527f064
kdbchk: row locked by non-existent transaction
        table=0   slot=0
        lockid=2   ktbbhitc=2
Page 13463 failed with check code 6101


DBVERIFY - Verification complete

Total Pages Examined         : 15360
Total Pages Processed (Data) : 12932
Total Pages Failing   (Data) : 2
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 291
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 2137
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1551567 (0.1551567)

修改数据块rdba_kcbh

BBED> set block 150
        BLOCK#          150

BBED> map
 File: /u01/oracle/oradata/ora11g/xifenfei02.dbf (1)
 Block: 150                                   Dba:0x00400096
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 96 bytes                     @20      

 struct kdbh, 14 bytes                      @124     

 struct kdbt[1], 4 bytes                    @138     

 sb2 kdbr[71]                               @142     

 ub1 freespace[910]                         @284     

 ub1 rowdata[6994]                          @1194    

 ub4 tailchk                                @8188    


BBED> p kcbh
struct kcbh, 20 bytes                       @0       
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x01800096
   ub4 bas_kcbh                             @8        0x00131e6f
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x4aae
   ub2 spare3_kcbh                          @18       0x0000

BBED> m /x 00000000 offset 4
 File: /u01/oracle/oradata/ora11g/xifenfei02.dbf (1)
 Block: 150              Offsets:    4 to  515           Dba:0x00400096
------------------------------------------------------------------------
 00000000 6f1e1300 00000104 ae4a0000 01000000 01280100 6f1e1300 00000000 
 03003200 90008001 ffff0000 00000000 00000000 00000000 00800000 6c781200 
 09001d00 05030000 8c02c000 b4000500 00800000 b0191300 02000900 fc020000 
 b011c000 0b011400 00800000 6c1e1300 00000000 00000000 00014700 ffffa000 
 2e048e03 8e030000 4700e90b 540cb30c 120d710d d00d2f0e 8e0eed0e 4c0fab0f 
 0a106910 c8102711 8611e711 4812aa12 0c136713 c2131f14 7c14df14 4115a015 
 ff156416 c9162517 8117de17 3b18a118 07196119 bb19191a 771ad61a 351b941b 
 f31b521c b11c101d 6f1dd71d 3f1eaa1e 151f2e04 99040405 6f05da05 4506b006 
 1b078607 f1075c08 c7082c09 9109f609 5b0ac00a 250b870b 48004900 4a004b00 
 4c004d00 4e004f00 5000ffff 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 2c000e03 53595308 565f245f 4c4f434b 
 ff03c20f 5eff0456 49455707 786f0912 12230407 786f0912 12230413 32303131 
 2d30392d 31383a31 373a3334 3a303305 56414c49 44014e01 4e014e02 c1022c00 
 0e065055 424c4943 0756245f 4c4f434b ff03c20f 5fff0753 594e4f4e 594d0778 
 6f091212 23040778 6f091212 23041332 3031312d 30392d31 383a3137 3a33343a 
 30330556 414c4944 014e014e 014e02c1 022c000e 03535953 07565f24 4c4f434b 

 <32 bytes per line>

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

dbv检测数据文件
发现提示坏块文件的rdba就是我们刚刚修改的rdba_kcbh值

[oracle@xifenfei tmp]$ dbv file=/u01/oracle/oradata/ora11g/xifenfei02.dbf blocksize=8192

DBVERIFY: Release 11.2.0.3.0 - Production on Fri Jun 1 03:40:44 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/ora11g/xifenfei02.dbf
Page 150 is marked corrupt
Corrupt block relative dba: 0x01800096 (file 6, block 150)
Bad header found during dbv: 
Data in bad block:
 type: 6 format: 2 rdba: 0x00000000
 last change scn: 0x0000.00131e6f seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x1e6f0601
 check value in block header: 0x4bb8
 computed block checksum: 0x0

Block Checking: DBA = 25179275, Block Type = KTB-managed data block
data header at 0xb52a807c
kdbchk: row locked by non-existent transaction
        table=0   slot=0
        lockid=2   ktbbhitc=3
Page 13451 failed with check code 6101
Block Checking: DBA = 25179287, Block Type = KTB-managed data block
data header at 0xb52c0064
kdbchk: row locked by non-existent transaction
        table=0   slot=0
        lockid=2   ktbbhitc=2
Page 13463 failed with check code 6101


DBVERIFY - Verification complete

Total Pages Examined         : 15360
Total Pages Processed (Data) : 12931
Total Pages Failing   (Data) : 2
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 291
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 2137
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1551567 (0.1551567)

修改datafile header的kcvfhrfn值

BBED> set block 1
        BLOCK#          1

BBED> map
 File: /u01/oracle/oradata/ora11g/xifenfei02.dbf (1)
 Block: 1                                     Dba:0x00400001
------------------------------------------------------------
 Data File Header

 struct kcvfh, 860 bytes                    @0       

 ub4 tailchk                                @8188    


BBED> p kcvfhrfn
ub4 kcvfhrfn                                @368      0x00000006

BBED> m /x 00000000
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/ora11g/xifenfei02.dbf (1)
 Block: 1                Offsets:  368 to  879           Dba:0x00400001
------------------------------------------------------------------------
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 e3b38c2e 04a91100 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 797c1900 00000000 3e3bba2e 
 01000000 11000000 b40e0000 1000ba8a 02000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 0a000a00 0a000100 00000000 
 00000000 00000000 02008001 bb050c00 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

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

dbv验证数据文件
这里如果验证所有数据文件快,会发现所有类此Corrupt block relative dba: 0×00000001 (file 0, block 1)提示.这里证明datafile header 的kcvfhrfn 影响dbv检查数据文件坏块的一个标准之一

[oracle@xifenfei tmp]$ dbv file=/u01/oracle/oradata/ora11g/xifenfei02.dbf blocksize=8192 start=1 end=4

DBVERIFY: Release 11.2.0.3.0 - Production on Fri Jun 1 03:43:27 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/ora11g/xifenfei02.dbf
Page 1 is marked corrupt
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Bad header found during dbv: 
Data in bad block:
 type: 11 format: 2 rdba: 0x01800001
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000b01
 check value in block header: 0x1daf
 computed block checksum: 0x0

Page 2 is marked corrupt
Corrupt block relative dba: 0x00000002 (file 0, block 2)
Bad header found during dbv: 
Data in bad block:
 type: 29 format: 2 rdba: 0x01800002
 last change scn: 0x0000.0017accf seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xaccf1d01
 check value in block header: 0x2626
 computed block checksum: 0x0

Page 3 is marked corrupt
Corrupt block relative dba: 0x00000003 (file 0, block 3)
Bad header found during dbv: 
Data in bad block:
 type: 30 format: 2 rdba: 0x01800003
 last change scn: 0x0000.0017accf seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xaccf1e01
 check value in block header: 0x4ef4
 computed block checksum: 0x0

Page 4 is marked corrupt
Corrupt block relative dba: 0x00000004 (file 0, block 4)
Bad header found during dbv: 
Data in bad block:
 type: 30 format: 2 rdba: 0x01800004
 last change scn: 0x0000.00119bf4 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x9bf41e01
 check value in block header: 0x810a
 computed block checksum: 0x0



DBVERIFY - Verification complete

Total Pages Examined         : 4
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 0
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 0
Total Pages Marked Corrupt   : 4
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)

总结说明
1.dbv检测数据文件坏块的时候会读取数据文件头的kcvfhrfn值,如果这个值出现问题,可能导致数据文件中的所有数据块都异常,具体表现就是Corrupt block relative dba项异常
2.dbv检查数据文件坏块中显示的rdba对应于数据块的rdba_kcbh值

发表在 Oracle | 评论关闭

忘记执行end backup命令数据库恢复

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

遇到两次begin backup忘记end backup导致的悲剧.虽然不是自己亲身经历,但是感触很深,这里做了一个小实验,说明在begin backup后忘记end backup,而又丢失了备份归档日志,且数据库异常重启的事故恢复(这里为了加大实验难道,并且使用begin backup命令后的热备文件恢复)
模拟begin end

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/xifenfei/archive
Oldest online log sequence     37
Next log sequence to archive   39
Current log sequence           39
SQL> alter tablespace bbed begin backup;

Tablespace altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/xifenfei/archive
Oldest online log sequence     37
Next log sequence to archive   39
Current log sequence           39
SQL> drop table chf.t_xff;

Table dropped.

SQL> create table chf.t_xff
  2    as
  3  select * from dba_objects;

Table created.

SQL> alter system switch logfile;

System altered.

SQL>   delete from chf.t_XFF;

30811 rows deleted.

SQL>   commit;

Commit complete.

SQL>   alter system  switch logfile;

System altered.

SQL> /

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/xifenfei/archive
Oldest online log sequence     40
Next log sequence to archive   42
Current log sequence           42

cp备份文件

[oracle@xifenfei xifenfei]$ cp bbed01.dbf bbed01.dbf_05
[oracle@xifenfei xifenfei]$ cp bbed02.dbf bbed02.dbf_05

继续操作数据库

SQL> alter system switch logfile;

System altered.

SQL>   insert into chf.t_xff
  2    select * from dba_objects;

30811 rows created.

SQL> commit;

Commit complete.

SQL>  archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/xifenfei/archive
Oldest online log sequence     41
Next log sequence to archive   43
Current log sequence           43
SQL> alter system switch logfile;

System altered.

模拟异常关闭数据库

SQL> shutdown immediate;
ORA-01149: cannot shutdown - file 11 has online backup set
ORA-01110: data file 11: '/u01/oracle/oradata/xifenfei/bbed01.dbf'
SQL> shutdown abort;
ORACLE instance shut down.

删除部分归档日志(模拟归档日志丢失)

[oracle@xifenfei archive]$ mv 1_39.dbf 1_39.dbf_bak
[oracle@xifenfei archive]$ mv 1_40.dbf 1_40.dbf_bak

启动数据库

[oracle@xifenfei xifenfei]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jun 5 03:02:56 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  353441008 bytes
Fixed Size                   451824 bytes
Variable Size             184549376 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01113: file 11 needs media recovery
ORA-01110: data file 11: '/u01/oracle/oradata/xifenfei/bbed01.dbf'

分析相关SCN

SQL> select file#,online_status "STATUS",to_char(change#,'9999999999999999') "SCN",
  2  To_char(time,'yyyy-mm-dd hh24:mi:ss')"TIME" from v$recover_file;

     FILE# STATUS  SCN               TIME
---------- ------- ----------------- -------------------
        11 ONLINE     12286828683164 2012-06-05 02:55:43
        12 ONLINE     12286828683164 2012-06-05 02:55:43

SQL> select file#,to_char(checkpoint_change#,'999999999999999') "SCN",
  2  to_char(last_change#,'999999999999999')"STOP_SCN" from v$datafile;

     FILE# SCN              STOP_SCN
---------- ---------------- ----------------
         1   12286828684636
         2   12286828684636
         3   12286828684636
         4   12286828684636
         5   12286828684636
         6   12286828684636
         7   12286828684636
         8   12286828684636
         9   12286828684636
        10   12286828684636
        11   12286828683164
        12   12286828683164

12 rows selected.

SQL> select file#,to_char(checkpoint_change#,'9999999999999999') "SCN",
  2  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN"
  3  from v$datafile_header;

     FILE# SCN               RESETLOGS SCN
---------- ----------------- -----------------
         1    12286828684636            174968
         2    12286828684636            174968
         3    12286828684636            174968
         4    12286828684636            174968
         5    12286828684636            174968
         6    12286828684636            174968
         7    12286828684636            174968
         8    12286828684636            174968
         9    12286828684636            174968
        10    12286828684636            174968
        11    12286828683164            174968
        12    12286828683164            174968

12 rows selected.

SQL> select file#,to_char(CHANGE#,'9999999999999999') "SCN",
  2  to_char(TIME,'yyyy-mm-dd hh24:mi:ss') "TIME" from v$backup;

     FILE# SCN               TIME
---------- ----------------- -------------------
         1                 0
         2                 0
         3                 0
         4                 0
         5                 0
         6                 0
         7                 0
         8                 0
         9                 0 
        10                 0
        11    12286828683164 2012-06-05 02:55:43
        12    12286828683164 2012-06-05 02:55:43

12 rows selected.

发现数据库未end backup

Tue Jun  5 02:55:43 2012
alter tablespace bbed begin backup
Tue Jun  5 02:55:43 2012
Completed: alter tablespace bbed begin backup

尝试end backup
出现这个错误是正常的,因为我替换回来的bbed表空间数据文件的版本信息可能和控制文件的不一致,解决方法是重建控制文件

SQL> alter tablespace bbed end backup;
alter tablespace bbed end backup
*
ERROR at line 1:
ORA-01235: END BACKUP failed for 2 file(s) and succeeded for 0
ORA-01122: database file 12 failed verification check
ORA-01110: data file 12: '/u01/oracle/oradata/xifenfei/bbed02.dbf'
ORA-01208: data file is an old version - not accessing current version
ORA-01122: database file 11 failed verification check
ORA-01110: data file 11: '/u01/oracle/oradata/xifenfei/bbed01.dbf'
ORA-01208: data file is an old version - not accessing current version

重建控制文件

SQL> shutdown abort;
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
Total System Global Area  353441008 bytes
Fixed Size                   451824 bytes
Variable Size             184549376 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes

SQL>@ctl.sql
Control file created.

尝试恢复数据库

SQL> recover database;
ORA-00279: change 12286828683164 generated at 06/05/2012 02:55:43 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/xifenfei/archive/1_39.dbf
ORA-00280: change 12286828683164 for thread 1 is in sequence #39


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u01/oracle/oradata/xifenfei/archive/1_39.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/u01/oracle/oradata/xifenfei/archive/1_39.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

执行end backup

SQL> alter tablespace bbed end backup;

Tablespace altered.

再次查看相关SCN
可以发现end backup之后,datafile header 的scn发生了改变,说明begin backup主要是冻住了datafile header scn

SQL> select file#,online_status "STATUS",to_char(change#,'9999999999999999') "SCN",
  2  To_char(time,'yyyy-mm-dd hh24:mi:ss')"TIME" from v$recover_file;

     FILE# STATUS  SCN               TIME
---------- ------- ----------------- -------------------
         1 ONLINE     12286828684636 2012-06-05 03:00:46
         2 ONLINE     12286828684636 2012-06-05 03:00:46
         3 ONLINE     12286828684636 2012-06-05 03:00:46
         4 ONLINE     12286828684636 2012-06-05 03:00:46
         5 ONLINE     12286828684636 2012-06-05 03:00:46
         6 ONLINE     12286828684636 2012-06-05 03:00:46
         7 ONLINE     12286828684636 2012-06-05 03:00:46
         8 ONLINE     12286828684636 2012-06-05 03:00:46
         9 ONLINE     12286828684636 2012-06-05 03:00:46
        10 ONLINE     12286828684636 2012-06-05 03:00:46
        11 ONLINE     12286828683821 2012-06-05 02:56:26
        12 ONLINE     12286828683821 2012-06-05 02:56:26

12 rows selected.

SQL> select file#,to_char(checkpoint_change#,'999999999999999') "SCN",
  2  to_char(last_change#,'999999999999999')"STOP_SCN" from v$datafile;

     FILE# SCN              STOP_SCN
---------- ---------------- ----------------
         1   12286828684636
         2   12286828684636
         3   12286828684636
         4   12286828684636
         5   12286828684636
         6   12286828684636
         7   12286828684636
         8   12286828684636
         9   12286828684636
        10   12286828684636
        11   12286828684636
        12   12286828684636

12 rows selected.

SQL> select file#,to_char(checkpoint_change#,'9999999999999999') "SCN",
  2  to_char(RESETLOGS_CHANGE#,'9999999999999999') "RESETLOGS SCN"
  3  from v$datafile_header;

     FILE# SCN               RESETLOGS SCN
---------- ----------------- -----------------
         1    12286828684636            174968
         2    12286828684636            174968
         3    12286828684636            174968
         4    12286828684636            174968
         5    12286828684636            174968
         6    12286828684636            174968
         7    12286828684636            174968
         8    12286828684636            174968
         9    12286828684636            174968
        10    12286828684636            174968
        11    12286828683821            174968
        12    12286828683821            174968

12 rows selected.

再次尝试恢复数据库

SQL> recover database;
ORA-00279: change 12286828683821 generated at 06/05/2012 02:56:26 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/xifenfei/archive/1_41.dbf
ORA-00280: change 12286828683821 for thread 1 is in sequence #41


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> alter database open;

Database altered.

总结说明
在数据库忘记end backup,而又被异常重启数据库时候,会提示你需要恢复.这个时候如果你有所有的归档日志,那没有任何问题,直接recover就可以了.如果因为begin backup命令执行比较久,部分归档日志丢失,这个时候不能直接recover,可以先尝试end backup,然后在recover.如果在这个时候还发现有部分日志不存在,那只能考虑bbed修改datafile header的scn.
温馨提醒:各位dba在执行begin backup之后一定要记得end backup

发表在 Oracle备份恢复 | 评论关闭

ORACLE最大可以存储多少数据量

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

今天有朋友在群里面讨论oracle数据库最大可以存储的数据大小,下面根据官方文档提供的相关限制,大概估算出来oracle数据库最多可以存储的数据量

Physical Database Limits(11.2)

Item

Type of Limit

Limit Value

Database Block Size

Minimum

2048 bytes; must be a multiple of operating system physical block size

Database Block Size

Maximum

Operating system dependent; never more than 32 KB

Database Blocks

Minimum in initial extent of a segment

2 blocks

Database Blocks

Maximum per datafile

Platform dependent; typically 222 - 1 blocks

Controlfiles

Number of control files

1 minimum; 2 or more (on separate devices) strongly recommended

Controlfiles

Size of a control file

Dependent on operating system and database creation options; maximum of25,000 x (database block size)

Database files

Maximum per tablespace

Operating system dependent; usually 1022

Database files

Maximum per database

65533

May be less on some operating systems

Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance

Database extents

Maximum per dictionary managed tablespace

4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)

Database extents

Maximum per locally managed (uniform) tablespace

2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)

Database file size

Maximum

Operating system dependent. Limited by maximum operating system file size; typically 222 or 4 MB blocks

MAXEXTENTS

Default value

Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter

MAXEXTENTS

Maximum

Unlimited

Redo Log Files

Maximum number of logfiles

Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement

Control file can be resized to allow more entries; ultimately an operating system limit

Redo Log Files

Maximum number of logfiles per group

Unlimited

Redo Log File Size

Minimum size

4 MB

Redo Log File Size

Maximum Size

Operating system limit; typically 2 GB

Tablespaces

Maximum number per database

64 K

Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file

Bigfile Tablespaces

Number of blocks

A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks.

Smallfile (traditional) Tablespaces

Number of blocks

A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks.

External Tables file

Maximum size

Dependent on the operating system.

An external table can be composed of multiple files.

通过这里的相关限制可以大概的技术出来oracle数据库在传统数据文件和大数据文件情况下最大大小分别是:

传统数据文件(Smallfile)

32*1024(数据块大小)* (222–1)(一个数据文件的数据块数)*65533

(数据库中最多数据文件个数)= 9006784790495232(byte)/1024/1024/1024/1024=8191.6P

如果按照我们常用的block_size=8k,那么我们的数据库可以存储大小为2047.9P

大数据文件(Bigfile)

32*1024(数据块大小)* (232– 1)(一个数据文件的数据块数)*65533

(数据库中最多数据文件个数) = 9222949822242324480 (byte)/1024/1024/1024/1024 =8589541374P

如果按照我们常用的block_size=8k,那么我们的数据库可以存储大小为2147385343.5P

参考:http://docs.oracle.com/cd/E11882_01/server.112/e25513/limits002.htm

发表在 Oracle | 一条评论