手动递增SCN号的几种方法:How to increase System Change Number by manual

 

 

手动递增SCN号的几种方法

 

除去下面几种,还有一种方法直接修改 实例的Global Lamport SCN,在SGA中由kcsgscn变量存储,对于一个实例来说这是唯一的源SCN,所有其他的SCN均由这个source scn所驱动。 这种递增方式是直接用oradebug 修改该Global Lamport SCN kcsgscn

 

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

 

 

SQL> oradebug setmypid
Statement processed.

 

SQL> select to_char(current_scn,’XXXXXXXXXXXX’) from v$database;

TO_CHAR(CURRE
————-
D3E1E

SQL>
SQL> oradebug DUMPvar SGA kcsgscn
kcslf kcsgscn_ [060012658, 060012688) = 000D3E1E 00000000 00000000 00000000 0000162D 00000000 00000000 00000000 00000000 00000000 60012338 00000000

 

ORADEBUG POKE 0x060012658 4 0xfffff

poke 命令的语法

<address> <length> <value>” allows you to modify a given region of memory (length of memory is limited to size of scalar C types)

 

SQL> select current_scn from v$database;

CURRENT_SCN
———–
1048583

 

SQL> select to_char(current_scn,’XXXXXXXXXXXX’) from v$database;

TO_CHAR(CURRE
————-
10000B

SQL> oradebug DUMPvar SGA kcsgscn
kcslf kcsgscn_ [060012658, 060012688) = 00100010 00000000 00000000 00000000 0000004E 00000000 00000000 00000000 00000000 00000000 60012338 00000000

 

 

 

 

 

How to jump SCN  by manual ,  this could be a problem:

1. We can bump up the SCN by using the procedure from Note: 386830.1

 

Bump the system SCN on the primary database to fix any metadata index corruptions for both
primary and physical standby databases. Set the following parameters in the init.ora and restart
the database in restricted mode to bump the system SCN of the primary database.
For Real Application Clusters, perform the steps on only one node of the cluster. 

Init.ora syntax:

*._allow_error_simulation = TRUE
*._smu_debug_mode = 268435456

If using an spfile, Oracle recommends creating a temporary init.ora using the CREATE PFILE SQL command.

SQL> create pfile='/tmp/initTMP.ora' from spfile='';

Then add the parameters to this temporary file.

To use the temporary init.ora file when starting the instance, include the 'PFILE' clause with the STARTUP SQL command i.e.

SQL> startup restrict pfile=

WARNING: These parameters should only be used for this fix and must be removed immediately afterwards in step 2.
If the above parameters are used through multiple database restarts, a complete database rebuild will be required.

To know the system SCN has been bumped, monitor the instance's alert.log for the following message:
advance SCN to wrap base xxxx

Where xxx represents the new wrap SCN.

 

 

 

 

2.EVENT: ADJUST_SCN – Quick Reference (Doc ID 30681.1)

 

 

 

WORKAROUND:
-----------
Searched in webiv (ora-1555, ora-604 see note:1063408.6 ) suggests workaround
is to adjust serial number using event

Will try : (30681.1)

alter session set events 'IMMEDIATE trace name ADJUST_SCN level 1';

ora-600 [2256][0][1073741824][1][293672646]

ERROR:
  ORA-600 [2256][a][b][c][d][e]

VERSIONS:
  versions 7.3.X, 8.0.X, 8.1.X

DESCRIPTION:
  This exception indicates that you attempted to ADJUST_SCN but the level
  supplied would be less that the current SCN.

ARGUMENTS:
  a.  Requested SCN WRAP
  b.  Requested SCN BASE
  c.  Current SCN WRAP
  d.  Current SCN BASE = [293672646]

*4 = 1174690584

will try level 2

ora-600 2256[0][2147483648][1][293672646]

level 3

ora-600 2256[0][3221225472][1][293672646]

Now, we increase the SCN on DST7 by using the ADJUST_SCN event -
(Note: do not use this event outside of your test environment)

set the following hidden parameter in init.ora on DST7 database and bounce
the database.
  _allow_error_simulation=true

Now, the SCN is increased by doing -
  alter system set events 'immediate trace name adjust_scn level ';

where  translates to (n*0x40000000) as the target SCN value. ie. n = 1
will set the SCN as 0x0000.40000000. For the testcase pick a value that is
larger than the current SCN on both databases by atleast 100000 SCNs (our
earlier SCN adjust threshold value).

 alter system set events 'immediate trace name adjust_scn level 2';
  select to_char(current_scn, 'xxxxxxxxxxxx') from v$database;

 

 

 

3.Note 552438.1 How To Adjust the SCN using parameter _MINIMUM_GIGA_SCN

 

 

 

 

Parameter: MINIMUM_GIGA_SCN
~~~~~~~~~~~~~~~~~~~~~~~~~~~

@Oracle8i:	HIDDEN
@Identifier:	kcmmsn
@Versions:	See <IVERS.MINIMUM_GIGA_SCN>

 Values:
 Related:

Description:	Minimum SCN to start with in 2^30 units
~~~~~~~~~~~~

Articles:	
       <Event:ADJUST_SCN>
     	Overview of Init.Ora Parameter Reference notes

终极方案:
使用bbed修改datafile header实现修改scn

主要是修改system01.dbf datafile header kcvfh.kcvfhckp.kcvcpscn

helpbkup_us@oracle.com
helpkern_us@oracle.com
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
1* select name,CHECKPOINT_CHANGE# from v$datafile
SQL> /
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf                  3905523
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_undotbs1_9f2flf52_.dbf                3905523
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_sysaux_9f2flf2v_.dbf                  3905523
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_users_9f2flf5g_.dbf                   3905523
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_example_9f2fmfto_.dbf                 3905523
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_youchuan_9f2fqwr4_.dbf                3905523
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_guobao_9f2fwkkt_.dbf                  3905523
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_tsoa_d_bjx6j57v_.dbf                  3905523
SQL>  select to_char(3905523,'XXXXXXXXXXXXX') from dual;
TO_CHAR(390552
--------------
3B97F3
SQL> oradebug tracefile_name
/s01/oracle/product/10.2.0/db_1/admin/MACLEAN1/udump/maclean1_ora_9468.trc
[oracle@vrh8 ~]$ cp /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf  /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf.bak
[oracle@vrh8 ~]$ bbed filename=/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf password=blockedit
BBED: Release 2.0.0.0.0 - Limited Production on Tue Mar 24 16:50:39 2015
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set mode edit
MODE            Edit
BBED>  set blocksize 8192
BLOCKSIZE       8192
BBED> set block 1
BLOCK#          1
BBED> map
File: /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system (0)
Block: 1                                     Dba:0x00000000
------------------------------------------------------------
Data File Header
struct kcvfh, 676 bytes                    @0       
ub4 tailchk                                @8188    
BBED> p kcvfh
struct kcvfh, 676 bytes                     @0       
struct kcvfhbfh, 20 bytes                @0       
ub1 type_kcbh                         @0        0x0b
ub1 frmt_kcbh                         @1        0xa2
ub1 spare1_kcbh                       @2        0x00
ub1 spare2_kcbh                       @3        0x00
ub4 rdba_kcbh                         @4        0x00400001
ub4 bas_kcbh                          @8        0x00000000
ub2 wrp_kcbh                          @12       0x0000
ub1 seq_kcbh                          @14       0x01
ub1 flg_kcbh                          @15       0x04 (KCBHFCKV)
ub2 chkval_kcbh                       @16       0x4fc6
ub2 spare3_kcbh                       @18       0x0000
struct kcvfhhdr, 76 bytes                @20      
ub4 kccfhswv                          @20       0x00000000
ub4 kccfhcvn                          @24       0x0a200100
ub4 kccfhdbi                          @28       0x157f2927
text kccfhdbn[0]                      @32      M
text kccfhdbn[1]                      @33      A
text kccfhdbn[2]                      @34      C
text kccfhdbn[3]                      @35      L
text kccfhdbn[4]                      @36      E
text kccfhdbn[5]                      @37      A
text kccfhdbn[6]                      @38      N
text kccfhdbn[7]                      @39      1
ub4 kccfhcsq                          @40       0x00000675
ub4 kccfhfsz                          @44       0x00010900
s_blkz kccfhbsz                       @48       0x00
ub2 kccfhfno                          @52       0x0001
ub2 kccfhtyp                          @54       0x0003
ub4 kccfhacid                         @56       0x00000000
ub4 kccfhcks                          @60       0x00000000
text kccfhtag[0]                      @64       
text kccfhtag[1]                      @65       
text kccfhtag[2]                      @66       
text kccfhtag[3]                      @67       
text kccfhtag[4]                      @68       
text kccfhtag[5]                      @69       
text kccfhtag[6]                      @70       
text kccfhtag[7]                      @71       
text kccfhtag[8]                      @72       
text kccfhtag[9]                      @73       
text kccfhtag[10]                     @74       
text kccfhtag[11]                     @75       
text kccfhtag[12]                     @76       
text kccfhtag[13]                     @77       
text kccfhtag[14]                     @78       
text kccfhtag[15]                     @79       
text kccfhtag[16]                     @80       
text kccfhtag[17]                     @81       
text kccfhtag[18]                     @82       
text kccfhtag[19]                     @83       
text kccfhtag[20]                     @84       
text kccfhtag[21]                     @85       
text kccfhtag[22]                     @86       
text kccfhtag[23]                     @87       
text kccfhtag[24]                     @88       
text kccfhtag[25]                     @89       
text kccfhtag[26]                     @90       
text kccfhtag[27]                     @91       
text kccfhtag[28]                     @92       
text kccfhtag[29]                     @93       
text kccfhtag[30]                     @94       
text kccfhtag[31]                     @95       
ub4 kcvfhrdb                             @96       0x00400179
struct kcvfhcrs, 8 bytes                 @100     
ub4 kscnbas                           @100      0x00000008
ub2 kscnwrp                           @104      0x0000
ub4 kcvfhcrt                             @108      0x221e01bf
ub4 kcvfhrlc                             @112      0x31dd7868
struct kcvfhrls, 8 bytes                 @116     
ub4 kscnbas                           @116      0x00080634
ub2 kscnwrp                           @120      0x0000
ub4 kcvfhbti                             @124      0x00000000
struct kcvfhbsc, 8 bytes                 @128     
ub4 kscnbas                           @128      0x00000000
ub2 kscnwrp                           @132      0x0000
ub2 kcvfhbth                             @136      0x0000
ub2 kcvfhsta                             @138      0x2000 (NONE)
struct kcvfhckp, 36 bytes                @484     
struct kcvcpscn, 8 bytes              @484     
ub4 kscnbas                        @484      0x003b97f3
ub2 kscnwrp                        @488      0x0000
ub4 kcvcptim                          @492      0x342a9561
ub2 kcvcpthr                          @496      0x0001
union u, 12 bytes                     @500     
struct kcvcprba, 12 bytes          @500     
ub4 kcrbaseq                    @500      0x00000052
ub4 kcrbabno                    @504      0x00013fc0
ub2 kcrbabof                    @508      0x0010
ub1 kcvcpetb[0]                       @512      0x02
ub1 kcvcpetb[1]                       @513      0x00
ub1 kcvcpetb[2]                       @514      0x00
ub1 kcvcpetb[3]                       @515      0x00
ub1 kcvcpetb[4]                       @516      0x00
ub1 kcvcpetb[5]                       @517      0x00
ub1 kcvcpetb[6]                       @518      0x00
ub1 kcvcpetb[7]                       @519      0x00
ub4 kcvfhcpc                             @140      0x00000087
ub4 kcvfhrts                             @144      0x3427f1a8
ub4 kcvfhccc                             @148      0x00000086
struct kcvfhbcp, 36 bytes                @152     
struct kcvcpscn, 8 bytes              @152     
ub4 kscnbas                        @152      0x00000000
ub2 kscnwrp                        @156      0x0000
ub4 kcvcptim                          @160      0x00000000
ub2 kcvcpthr                          @164      0x0000
union u, 12 bytes                     @168     
struct kcvcprba, 12 bytes          @168     
ub4 kcrbaseq                    @168      0x00000000
ub4 kcrbabno                    @172      0x00000000
ub2 kcrbabof                    @176      0x0000
ub1 kcvcpetb[0]                       @180      0x00
ub1 kcvcpetb[1]                       @181      0x00
ub1 kcvcpetb[2]                       @182      0x00
ub1 kcvcpetb[3]                       @183      0x00
ub1 kcvcpetb[4]                       @184      0x00
ub1 kcvcpetb[5]                       @185      0x00
ub1 kcvcpetb[6]                       @186      0x00
ub1 kcvcpetb[7]                       @187      0x00
ub4 kcvfhbhz                             @312      0x00000000
struct kcvfhxcd, 16 bytes                @316     
ub4 space_kcvmxcd[0]                  @316      0x00000000
ub4 space_kcvmxcd[1]                  @320      0x00000000
ub4 space_kcvmxcd[2]                  @324      0x00000000
ub4 space_kcvmxcd[3]                  @328      0x00000000
word kcvfhtsn                            @332      0
ub2 kcvfhtln                             @336      0x0006
text kcvfhtnm[0]                         @338     S
text kcvfhtnm[1]                         @339     Y
text kcvfhtnm[2]                         @340     S
text kcvfhtnm[3]                         @341     T
text kcvfhtnm[4]                         @342     E
text kcvfhtnm[5]                         @343     M
text kcvfhtnm[6]                         @344      
text kcvfhtnm[7]                         @345      
text kcvfhtnm[8]                         @346      
text kcvfhtnm[9]                         @347      
text kcvfhtnm[10]                        @348      
text kcvfhtnm[11]                        @349      
text kcvfhtnm[12]                        @350      
text kcvfhtnm[13]                        @351      
text kcvfhtnm[14]                        @352      
text kcvfhtnm[15]                        @353      
text kcvfhtnm[16]                        @354      
text kcvfhtnm[17]                        @355      
text kcvfhtnm[18]                        @356      
text kcvfhtnm[19]                        @357      
text kcvfhtnm[20]                        @358      
text kcvfhtnm[21]                        @359      
text kcvfhtnm[22]                        @360      
text kcvfhtnm[23]                        @361      
text kcvfhtnm[24]                        @362      
text kcvfhtnm[25]                        @363      
text kcvfhtnm[26]                        @364      
text kcvfhtnm[27]                        @365      
text kcvfhtnm[28]                        @366      
text kcvfhtnm[29]                        @367      
ub4 kcvfhrfn                             @368      0x00000001
struct kcvfhrfs, 8 bytes                 @372     
ub4 kscnbas                           @372      0x00000000
ub2 kscnwrp                           @376      0x0000
ub4 kcvfhrft                             @380      0x00000000
struct kcvfhafs, 8 bytes                 @384     
ub4 kscnbas                           @384      0x00000000
ub2 kscnwrp                           @388      0x0000
ub4 kcvfhbbc                             @392      0x00000000
ub4 kcvfhncb                             @396      0x00000000
ub4 kcvfhmcb                             @400      0x00000000
ub4 kcvfhlcb                             @404      0x00000000
ub4 kcvfhbcs                             @408      0x00000000
ub2 kcvfhofb                             @412      0x000a
ub2 kcvfhnfb                             @414      0x000a
ub4 kcvfhprc                             @416      0x221e01a8
struct kcvfhprs, 8 bytes                 @420     
ub4 kscnbas                           @420      0x00000001
ub2 kscnwrp                           @424      0x0000
struct kcvfhprfs, 8 bytes                @428     
ub4 kscnbas                           @428      0x00000000
ub2 kscnwrp                           @432      0x0000
ub4 kcvfhtrt                             @444      0x00000000
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
struct kcvcpscn, 8 bytes                 @484     
ub4 kscnbas                           @484      0x003b97f3
ub2 kscnwrp                           @488      0x0000
ub4 kcvcptim                             @492      0x342a9561
ub2 kcvcpthr                             @496      0x0001
union u, 12 bytes                        @500     
struct kcvcprba, 12 bytes             @500     
ub4 kcrbaseq                       @500      0x00000052
ub4 kcrbabno                       @504      0x00013fc0
ub2 kcrbabof                       @508      0x0010
ub1 kcvcpetb[0]                          @512      0x02
ub1 kcvcpetb[1]                          @513      0x00
ub1 kcvcpetb[2]                          @514      0x00
ub1 kcvcpetb[3]                          @515      0x00
ub1 kcvcpetb[4]                          @516      0x00
ub1 kcvcpetb[5]                          @517      0x00
ub1 kcvcpetb[6]                          @518      0x00
ub1 kcvcpetb[7]                          @519      0x00
BBED> set offset 484
OFFSET          484
BBED> modify /x 0xF397
File: /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system (0)
Block: 1                Offsets:  484 to  995           Dba:0x00000000
------------------------------------------------------------------------
f397f4f3 00000000 61952a34 01000000 52000000 c03f0100 1000a865 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 
0d000d00 0d000100 00000000 00000000 00000000 02004000 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 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> set offset 486
OFFSET          486
BBED> modify /x 0x4B00
File: /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system (0)
Block: 1                Offsets:  486 to  997           Dba:0x00000000
------------------------------------------------------------------------
4b000000 00006195 2a340100 00005200 0000c03f 01001000 a8650200 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 00000d00 
0d000d00 01000000 00000000 00000000 00000200 40000000 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 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> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
struct kcvcpscn, 8 bytes                 @484     
ub4 kscnbas                           @484      0x004b97f3
ub2 kscnwrp                           @488      0x0000
ub4 kcvcptim                             @492      0x342a9561
ub2 kcvcpthr                             @496      0x0001
union u, 12 bytes                        @500     
struct kcvcprba, 12 bytes             @500     
ub4 kcrbaseq                       @500      0x00000052
ub4 kcrbabno                       @504      0x00013fc0
ub2 kcrbabof                       @508      0x0010
ub1 kcvcpetb[0]                          @512      0x02
ub1 kcvcpetb[1]                          @513      0x00
ub1 kcvcpetb[2]                          @514      0x00
ub1 kcvcpetb[3]                          @515      0x00
ub1 kcvcpetb[4]                          @516      0x00
ub1 kcvcpetb[5]                          @517      0x00
ub1 kcvcpetb[6]                          @518      0x00
ub1 kcvcpetb[7]                          @519      0x00
BBED> sum
Check value for File 0, Block 1:
current = 0x4fc6, required = 0x4fb6
BBED> sum apply
Check value for File 0, Block 1:
current = 0x4fb6, required = 0x4fb6
BBED> verify
DBVERIFY - Verification starting
FILE = /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf
BLOCK = 1
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
SQL> select file#,name,checkpoint_change# from v$datafile;
1 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf                  3905523
2 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_undotbs1_9f2flf52_.dbf                3905523
3 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_sysaux_9f2flf2v_.dbf                  3905523
4 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_users_9f2flf5g_.dbf                   3905523
5 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_example_9f2fmfto_.dbf                 3905523
6 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_youchuan_9f2fqwr4_.dbf                3905523
7 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_guobao_9f2fwkkt_.dbf                  3905523
8 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_tsoa_d_bjx6j57v_.dbf                  3905523
8 rows selected.
SQL> select file#,FUZZY,CHECKPOINT_CHANGE#        from v$datafile_header;
1 NO             4954099
2 NO             3905523
3 NO             3905523
4 NO             3905523
5 NO             3905523
6 NO             3905523
7 NO             3905523
8 NO             3905523
8 rows selected.
SQL> alter database open;
Database altered.
SQL> select file#,FUZZY,CHECKPOINT_CHANGE#        from v$datafile_header;
1 YES            4954100
2 YES            4954100
3 YES            4954100
4 YES            4954100
5 YES            4954100
6 YES            4954100
7 YES            4954100
8 YES            4954100
8 rows selected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area  591396864 bytes
Fixed Size                  2022536 bytes
Variable Size             180355960 bytes
Database Buffers          402653184 bytes
Redo Buffers                6365184 bytes
Database mounted.
Database opened.

Oracle 8i 备份/恢复(Recovery Manager)

本文永久地址:https://www.askmaclean.com/archives/oracle-8i-备份恢复(recovery-manager).html

说明

Recovery Manager(RMAN)是从Oracle8 R8.0之后追加的管理oracle数据库备份/储存/恢复的工具。在此,我将解说oracle8i的RMAN中扩展的新功能。

 

  • RMAN基本功能的扩展
  • 媒介管理软件相关功能的扩展
  • OPS相关功能的扩展

在此对于RMAN命令的详细语法方面的内容,请参考《Oracle8i备份、恢复指南》

 

RMAN基本功能的扩展

1-1.复制备份set

考虑到Oracle的备份时,类似于归档日志以及控制文件的案例,某个备份受损可能造成毁灭性的故障。Oracle8i的RMAN中,可以一次制成多个类似的的备份。因此,在存储时某个备份无法使用的话,用于其他备份就OK了。

为了制成多个备份,需要通过RMAN命令的SET DUPLEX命令来指定复制数,执行备份。SET DUPLEX命令如下所示。

 

SET DUPLEX = {ON|OFF|1|2|3|4};

 

关键词ON意味着指定为2 。DUPLEX最多可以制成4个拷贝。以下是使用SET DUPLEX命令的范例。

RUN {

SET DUPLEX =2;

ALLOCATE CHANNEL ch1 TYPE ‘sbt_tape’;

BACKUP

FORMAT ‘arc_%s_%p_%c’ FILESPERSET 1

ARCHIVELOG ALL DELETE INPUT;

RELEASE CHANNEL ch1;

}

SET DUPLEX命令需要比分配channel(ALLOCATE CHANNEL命令)更早执行。另外,执行SET DUPLEX命令后,在分配到的所有channel中执行备份复制。
在制成tape装置的备份是,需要指定初始化参数BACKUP_TAPE_IO_SLAVES = TRUE。
执行复制备份时,用指定备份名的FORMAT句,可以用替换字符串“%”来指定。在上述例子中,可以用替换字符串“%”来指定。在上述例子中,备份set编号为10,备份piece为1时制成 ‘arc_10_1_1’、‘arc_10_1_2’两个备份。

通过RMAN制成克隆数据库

Oracle8i的RMAN中,使用RMAN的备份,在远程主机或本地主机中,制成数据库的拷贝(克隆数据库)。因为克隆数据库与真正的数据库结构完全相同,所以并不会对正式环境的数据库产生影响。可以作为测试专用数据库。

左:正式数据库、克隆数据库、制成克隆数据库 本地主机

右:克隆数据库、远程主机

通过RMAN制成克隆数据库是,以下操作可以使用RMAN完成。

  • 克隆数据库中所使用的数据库文件的还原。在远程主机中制成克隆数据库时,是经过Net8来传送文件,还原文件的。
  • 直到执行命令时,需要执行应用了REDO的不完整恢复
  • 启动指定不完整恢复之后的RESETLOGS选项的数据库
  • 对新数据库重命名
  • 制成控制文件

通过RMAN制成克隆数据库时,伴随着指定了RESETLOGS的数据库的启动,就无法在制成stand by数据库时使用。

 

以下命令是远程主机制成克隆数据库的例子。为了完成这个操作,需要实现在远程主机中进行以下操作。

 

  • 收集远程主机中的ora参数文件拷贝
  • 设定需要连接远程主机的net8
  • 编辑ora、tnsnames.ora
  • 启动listener

 

完成这些设定之后,请使用以下命令来制成克隆数据库。下例是通过Net8连接描述符来指定在本地设备中的克隆数据库,制成远程主机中的克隆数据库。

 

 

# 启动RMAN,这时请指定克隆数据库的连接描述符。
%rman connect target / auxiliary sys/passwd@clonedb catalog rman/rman/@rcat
# 以下是制成克隆数据库的RMAN命令。
RMAN>STARTUP NOMOUNT;
RMAN> RUN{
2>      ALLOCATE AUXILIARY CHANNEL ch1 TYPE DISK;
3>      DUPLICATE TARGET DATABASE TO CLONEDB 
4>         LOGFILE ‘/Oracle/dbs/log1.f’ SIZE 200K,
5>                 ‘/Oracle/dbs/log2.f’ SIZE 200K 
6>         NOFILECHECK;
7>    }

DUPLICATE命令中,需要原数据库与克隆数据库中所使用的数据库文件名不重复。因此,在同样的目录结构中,在远程主机中制成克隆数据库时,需要设置为指定NOFILECHECK选项而不执行这个检查。
具体的克隆数据库制作顺序请参考《执行例与结果》。

通过RMAN来制成克隆数据库的详细内容请参考「Oracle8i备份、恢复指南」。

启动/关闭数据库

Oracle8 R8.0中为了对数据库进行冷备份以及恢复,需要启动、关闭数据库时,需要使用Server Manager执行这些操作。因此通过RMAN来执行修复时,就会组合SVRMGR与RMAN来一起使用。一些脚本由此就会变得复杂。

在Oracle8i的RMAN中,作为RMAN的组合命令支撑数据库的STARTUP、SHUTDOWN、ALTER DATABASE命令。由此就可以仅凭RMAN启动、关闭数据库。

 

追加命令 功能
STARTUP … 启动数据库。可以指定SQL*Plus的STARTUP命令以及同样的选项。
SHUTDOWN … 关闭数据库。可以指定SQL*Plus的SHUTDOWN命令以及相关选项。
ALTER DATABASE … 将数据库从MOUNT状态变更为OPEN状况。

 

在以下RMAN命令中,获得启动中终止的数据库的冷备份。重启数据库。

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
RUN{
ALLOCATE CHANNEL ch1 TYPE 'SBT_TAPE';
BACKUP
FORMAT 'db_%s_%p'
(DATABASE);
}
ALTER DATABASE OPEN;

 

扩展LIST命令

LIST命令是展示至今为止获得的备份的信息的命令。Oracle8i的RMAN中,LIST命令已经被扩展为执行时会展示各自的备份片的名称、所有的数据文件的名称,控制文件信息等。以下是展示表区域bo1的备份set的一览的例子。

 

 

RMAN> LIST BACKUPSET OF TABLESPACE bol;
RMAN-03022: compiling command: list
List of Backup Sets
Key     Recid      Stamp      LV Set Stamp  Set Count  Completion Time
------- ---------- ---------- -- ---------- ---------- ----------------------
1       1          365975325  0  365975324  2          20-APR-99
List of Backup Pieces
Key     Pc# Cp# Status      Completion Time        Piece Name
------- --- --- ----------- ---------------------- -----------------------
2       1   2   AVAILABLE   20-APR-99              /bkup/bol_02at0los_1_2
1       1   1   AVAILABLE   20-APR-99              /bkup/bol_02at0los_1_1
List of Datafiles Included
File Name                                  LV Type Ckp SCN    Ckp Time
---- ------------------------------------- -- ---- ---------- ------------
4    /oradata/je815/dbs/bolje815.dbf       0  Full 578102     20-APR-99

 

LIST命令中,仅会展示备份的统计数据的平均值。展示deleted、unavailable状态的备份信息时,请使用LIST ALL … 命令。

通过RMAN制成、更新恢复目录

Oracle8 R8.0中,需要使用SQL*Plus来制成、更新、删除、储存RMAN使用的信息的恢复目录的脚本。

Oracle8i中,变更为将这些操作作为RMAN的命令来执行了。这些命令是用RMAN启动时的选项RCVCAT来指定的,对于用户的架构来执行的。

 

追加命令 功能
CREATE CATALOG 制成现在连接的架构中的恢复目录。可以用TABLESPACE语句来指定制成目录的表区域。
UPDATE CATALOG 连接到旧版本的目录时,执行目录的更新。UPDATE CATALOG命令为了确认,连续执行两次,于是就会进行更新。
DROP CATALOG 删除现在连接的架构中的目录。

DROP CATALOG命令连续执行两次时,就会删除目录。

执行对于目录的操作的用户,需要重新赋予RECOVERY_CATALOG_OWNER权限。

另外,Oracle8i中为了制成目录,而附属为SQL的脚本catrman.sql。必须要用到RMAN的CREATE CATALOG命令。

 

以下的命令是执行目录更新的例子。

 

RMAN> UPGRADE CATALOG
RMAN-06435: recovery catalog owner is rman
RMAN-06442: enter UPGRADE CATALOG command again to confirm catalog upgrade
RMAN> UPGRADE CATALOG
RMAN-06408: recovery catalog upgraded to version 08.01.05

 

媒介管理软件相关功能的扩展

2-1. 恢复目录以及媒介管理软件目录的项目检查

使用RMAN中的tape装置,执行备份时,oracle并不需要媒介管理软件。因为一般媒介管理软件中,是通过目录来管理tape的,作为系统来考虑时,就会存在RMAN以及媒介管理软件两个目录。

Oracle8 R8.0的RMAN需要用户管理两个目录的同步。比如重新使用(覆盖)媒介管理软件中的tape时,RMAN的目录中包含这些tape的备份信息就会被留下。因此,用户使用RMAN的目录管理命令,需要从恢复目录中删除这些备份信息。

Oracle8i的RMAN,就会执行恢复目录以及媒介管理软件中的目录同步命令。

 

 

追加命令 功能
ALLOCATE CHANNEL …

FOR MAINTENANCE ;

为了执行目录维护,需要分配将要使用的channel。以下展示的命令在执行前,需要用这个命令来分配channel。
CROSSCHECK … ;

CHANGE… CROSSCHECK ;

这些命令用于确认指定的备份是否存在(可以从RMAN进行访问)。执行命令时,RMAN无法访问备份的话,那个备份就会被标记以及EXPIRED ,被判断为无效。这个备份可以用LIST命令以及REPORT命令来确认。

以下是使用CROSSCHECK命令的脚本例。

 

ALLOCATE CHANNEL FOR MAINTENANCE TYPE ‘sbt_tape’;

CROSSCHECK BACKUPSET OF “/Oracle/dbs/tbs_8.f”;

没有连接恢复目录时,无法使用CROSSCHECK命令。这时,请使用CHANGE … CROSSCHECK命令。CHANGE … CROSSCHECK命令没有连接恢复目录时,也可以通过控制文件中的信息以及每集管理软件的信息来进行交互检测。

EXPIRED以及被标记的备份,可以在执行下次的CHANGE … CROSSCHECK命令时,访问备份时,会被标记为AVAILABLE。类似的功能就不会再用CROSSCHECK命令来执行。这是为了确认CROSSCHECK命令是否将状态变为AVAILABE。

CROSSCHECK命令,CHANGE … CROSSCHECK命令中,需要检测是否可以访问。因此,检测备份破损时,请使用VALIDATE命令。

2-2. 使用了媒介管理软件的固有的功能的数据库备份
(PROXY COPY)

使用Oracle8 R8.0的RMAN获得的备份,对数据文件的I/O,是由oracle的服务器进程来执行。因此媒介管理软件会执行oracle的服务器进程以及数据流的对策。

Oracle8i中媒介管理软件以及接口中,媒介管理软件自身对于数据库的文件追加了执行I/O的API。通过使用API,使用媒介管理软件的固有功能,就可以高速执行备份以及还原。Oracle中这个功能称为PROXY COPY。

 

通过这个API执行备份时,oracle将对象变更为备份模式之后,将包含在对象中的文件列表移交给媒介管理软件。媒介管理软件根据被移交的列表,使用各个公司自带的功能来启动数据库。

媒介管理软件对应PROXY COPY功能时,执行备份命令时,通过指定PRXY语句,可以执行使用了媒介管理软件固有功能的备份。

 

 

RUN{
ALLOCATE CHANNEL ch1 TYPE ‘SBT_TAPE’;
BACKUP
PROXY
(TABLESPACE SYSTEM FORMAT ‘system_%s_%p’); 
} 

为了使用PROXY COPY功能需要对应PROXY COPY的媒介管理软件。不对应PROXY COPY时,指定PROXY语句时也可以通过API来执行备份。但是PROXY COPY功能无法使用时就会报错。

指定备份池

在大部分的内存管理软件中,可以在tape装置以及那个装置中使用的tape组作为“池”的概念来管理。换言之,各个tape装置以及tape装置都属于某个池,对其备份时进行选择时,可以选择想使用的tape装置以及tape。

Oracle8 R8.0中,因为可以在备份时选择池,媒介管理软件所参考的环境变量中,采取重新指定池的方法以及指定RMAN的ALLOCATE CHANNEL命令PARMS语句中的环境变量的方法。

Oracle8i中通过RMAN的BACKUP命令,可以直接指定池。通过BACKUP命令的POOL语句可以指定对应数字进行设定。

 

RUN{
ALLOCATE CHANNEL ch1 TYPE ‘SBT_TAPE’;
BACKUP 
POOL 2
(TABLESPACE SYSTEM FORMAT ‘system_%S_%P’);
} 

能否使用POOL语句来指定依赖于媒介管理软件。详细内容请参考媒介管理软件手册。

3. Oracle Prallel Server(OPS)相关的功能扩展

 

备份时的磁盘亲和性(Disk Affinity)以及分散负荷

MPP结构的OPS中,因为会连接到各个设备的各个磁盘中,访问速度并不是相等的。因此,为了使得各个设备中的磁盘I/O最优化,需要尽可能对连接到本地的设备执行I/O。

Oracle8 R8.0的OPS环境中,使用RMAN执行备份时,为了执行磁盘I/O的最优化,重要的是,在用户端中对各个文件分割合适的节点的channel(执行备份的服务器进程)。以下是RMAN的命令例,在由4个节点构成的OPS环境中,对各节点连接到本地设备上的数据文件进行备份。

 

 

 

RUN{
ALLOCATE CHANNEL ch1 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE1’;
ALLOCATE CHANNEL ch2 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE2’;
ALLOCATE CHANNEL ch3 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE3’;
ALLOCATE CHANNEL ch4 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE4’;
BACKUP 
FILESPERSET 1 FORMAT ‘DBFILE_%s_%p’
(DATAFILE ‘/dev/rdsk/lv1’ CHANNEL ch1)
(DATAFILE ‘/dev/rdsk/lv2’ CHANNEL ch2)
(DATAFILE ‘/dev/rdsk/lv3’ CHANNEL ch3)
(DATAFILE ‘/dev/rdsk/lv4’ CHANNEL ch4);
}

Oracle8i的RMAN中,在执行备份时,以设备亲和性为基础,对备份对象数据文件进行集群化(group化)这时,备份对象的数据文件所属的节点中如果分配到channel的话,通过channel就可以进行备份。节点中没有分配到channel时,节点用于的数据文件,特别是不需要考虑到亲和性。对于可以使用的channel依次分配,执行备份。
以下的例子是设备亲和性有效的环境中,对之前页面的列表执行相同的备份的命令的例子。(数据文件 /dev/rdsk/lv1 ~ lv4包含于表领域tbs1中的情况)

 

RUN{
ALLOCATE CHANNEL ch1 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE1’;
ALLOCATE CHANNEL ch2 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE2’;
ALLOCATE CHANNEL ch3 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE3’;
ALLOCATE CHANNEL ch4 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE4’;
BACKUP
FORMAT ‘tbs1_%s_%p’ 
FILESPERSET 1
(TABLESPACE tbs1);
}

另外在磁盘亲和性有效的环境中,指定包含在备份set中的磁盘数的最小值,可以对磁盘进行数据分散。这个设定可以通过指定RMAN的BACKUP命令中的DISKRATIO参数来设定。(在DISKRATIO参数中指定整数)

RUN{
ALLOCATE CHANNEL ch1 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE1’;
ALLOCATE CHANNEL ch2 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE2’;
ALLOCATE CHANNEL ch3 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE3’;
ALLOCATE CHANNEL ch4 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE4’;
BACKUP 
FORMAT ‘%d_%s_%p’
DISKRATIO 3
(DATABASE);
}

在DISKRATIO中指定整数n时,各个备份set中所包含的数据文件的总计块数为了不超过n分之一,对各个数执行数据文件分割。
比如DISKRATIO指定为3时,就会如此调整备份set:对一个备份set,从一个备份set从一个磁盘读入的块数,就会小于整体块数的三分之一。(被分散到4个磁盘中)。
这是考虑到磁盘的亲和性的备份,支持MPP平台固有功能。您如果想查看自己使用的平台是否支持磁盘亲和性的话,请参考各个平台的管理者手册。

Oracle8i的OPS中不会考虑磁盘亲和性,日志归档以及复制数据文件的备份时,不会考虑到Load Balancing。

【Oracle数据恢复】ORA-01115、ORA-01110、ORA-27091、ORA-27070、OSD-04006、O/S-Error

某用户windows 2003上的数据库由于存储故障导致系统表空间system.dbf出现IO问题,当打开数据库OPEN database时报错:

 

 

ORA-01115: IO error reading block from file 15 
ORA-01110: data file … 
ORA-27091: unable to queue I/O 
ORA-27070: async read/write failed 
OSD-04006: ReadFile() failure, unable to read from file 
O/S-Error: (OS 121) The semaphore timeout period has expired.

以上ORA-01115、ORA-01110、ORA-27091、ORA-27070、OSD-04006、O/S-Error 这堆报错本质与Oracle数据库层面没有关系,问题的根本原因是Windows上对应磁盘驱动器下的文件无法读取出来,这可能是OS bug 也可能就是对应磁盘出现了坏道或其他物理故障,所以对于该问题有限考虑在OS层面解决文件的读取问题, 如果确实发现无法从OS层面或从备份解决,那么可以考虑特殊的恢复手段。‘

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638 QQ号:47079569 邮箱:service@parnassusdata.com

 

 

Error: OSD 4006
Text: ReadFile() failure, unable to read from file
—————————————————————————
Cause: Unexpected return from Windows NT system service ReadFile()
Action: Check OS error code and consult Windows NT documentation

This is due to a problem in Windows such that when Oracle attempted to access the data file on that device, it could not because the device timed out. This suggests that Windows has run out of asynchronous I/O buffers or there is a communications delay on the device.

There is nothing you’re going to be able to do at the database level to resolve this error, unless you move the data files to another drive. Ask the O/S system administrator to run diagnostics tools to check for possible faulty hardware and disk corruption on the disk device where the error is showing in the loader log. If the error persists, then log a call with Microsoft Support.

Oracle processes may encounter various (OS 1117) errors on a Windows 2003 Server. The text of the (OS 1117) error can be seen as follows:

C:\>net helpmsg 1117
The request could not be performed because of an I/O device error.
This error may manifest itself in different ways, depending on which Oracle process encounters the error:

Oracle RDBMS Instance Encounters (OS 1117) error

1. If an Oracle RDBMS instance encounters the error, you may see messages such as the following in the alert log for the RDBMS instance:

==========================================================================

Fri Jul 13 01:21:33 2007
Errors in file d:\oracle\db\product\admin\mydb\bdump\mydb1_lmon_4608.trc:
ORA-27091 : unable to queue I/O
ORA-27070 : async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 1117) The request could not be performed because of an I/O device error.
==========================================================================

Oracle ASM Instance Encounters (OS 1117) errors

2. If an Oracle ASM instance encounters the error, you may see similar errors in the ASM instance’s Alert log:

============================================
Fri Jul 13 01:22:10 2007
Errors in file d:\oracle\asm\product\admin\+asm\bdump\+asm1_gmon_3836.trc:
ORA-27091 : unable to queue I/O
ORA-27070 : async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 1117) The request could not be performed because of an I/O device error.
============================================

CRS Daemon (crsd.exe) encounters 1117 errors
3. If you are running in an Oracle Clusterware environment, then you may also see errors in the crsd.log and/or certain resource logs, indicating a problem accessing the OCR (Oracle Cluster Registry). An example of those errors would be:

================================================
2007-07-13 01:21:51.766: [ OCROSD][4272]utwrite:4: Problem writing the buffer phy offset 184320 and oserror 1117
2007-07-13 01:21:51.766: [ OCROSD][4352]utwrite:4: Problem writing the buffer phy offset 184320 and oserror 1117
2007-07-13 01:21:51.766: [ OCRRAW][4352]beginlog: problem 26 clearing the log metadata buffer
2007-07-13 01:21:51.766: [ OCRRAW][4352]proprdkey: Problem in begin log
2007-07-13 01:21:51.766: [ OCRRAW][4352]proprseterror: Error in accessing physical storage [26] Marking context invalid.
================================================

CSS Daemon (ocssd.exe) encounters 1117 errors

4. Also, in an Oracle Clusterware environment, the Cluster Synchronization Services daemon (ocssd.exe) may experience problems accessing the voting disk. If this occurs, you will see an error in the ocssd.log similar to the following:

============================================
[ CSSD]2007-07-13 01:22:12.501 [4052] >ERROR: Internal Error Information:
Category: 1234
Operation: scls_block_write
Location: WriteFile
Other: unable to write block(s)
Dep: 1117

[ CSSD]2007-07-13 01:22:12.501 [4052] >ERROR: clssnmvReadBlocks: read failed 1 at offset 533 of \\.\votedsk2
[ CSSD]2007-07-13 01:22:12.501 [4052] >TRACE: clssnmDiskStateChange: state from 4 to 3 disk (1/\\.\votedsk2)
[ CSSD]2007-07-13 01:22:12.501 [2200] >TRACE: clssnmDiskPMT: disk offline (1/\\.\votedsk2)
[ CSSD]2007-07-13 01:22:12.501 [2200] >ERROR: clssnmDiskPMT: Aborting, 1 of 2 voting disks unavailable
[ CSSD]2007-07-13 01:22:12.501 [2200] >ERROR: ###################################
[ CSSD]2007-07-13 01:22:12.501 [2200] >ERROR: clssscExit: CSSD aborting
[ CSSD]2007-07-13 01:22:12.501 [2200] >ERROR: ###################################
==============================================

5. When you are running in an Oracle Clusterware environment, if the ocssd process encounters an I/O error when accessing the Voting Disk, the CSS daemon will evict the node from the cluster. This is done by signalling the Oracle Fence Driver (OraFencedrv.sys) to reboot the machine. When the fence driver reboots the machine, this will be seen as a bugcheck with stop code 0x0000ffff. You will be able to see this in the System Log with a message such as:

The computer has rebooted from a bugcheck.
The bugcheck was: 0x0000ffff (0x0000000000000000, 0x0000000000000000,
0x0000000000000000, 0x0000000000000000).
A dump was saved in: C:\WINDOWS\MEMORY.DMP.

Note that the bugcheck is expected behavior when ocssd.exe (the Cluster Synchornization Services daemon) encounters an I/O error when accessing the voting disk. The node experiencing the I/O error is intentionally rebooted to avoid a split-brain and possible data corruption when access to the voting disk is lost.
CHANGES

You may encounter this error after upgrading the Microsoft Storport driver to version 5.2.3790.4021 or later.

CAUSE

Reference Microsoft KB article#932755, available at the following URL:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;932755

Per that article, one of the changes introduced in this version of the Storport driver is the following:

=========================================================
If a target returns a SCSI status of BUSY or Task Set Full, the port driver retries the command immediately. Storport retries the command an unlimited number of times. Therefore, if the busy status continues, the system could eventually experience problems.

This update configures the following behavior:

• It limits the number of retries. The default is 20.

• If the target returns a status of BUSY, the Storport driver performs a time-based pause before the Storport driver retries the command.

• If the target returns a status of Task Set Full, the Storport driver performs an I/O completion-based pause before the Storport driver retries the command.
=========================================================

Therefore, prior to upgrading the Storport driver, if a storage path had become saturated, the Storport driver would immediately continue to retry – indefinitely. This would result in slow I/O and perhaps a hang or spin scenario, but no error would be returned.

With the later version of the Storport driver, the retries are limited to 20 retries by default, with a pause between each retry. After 20 failures with a device busy status, the (OS 1117) error is returned to applications waiting on I/O. For more information on changes to the Storport driver, you must contact Microsoft.

SOLUTION

This is an I/O performance problem. You will need to increase the performance/capacity of the storage system to avoid the prolonged BUSY status. Specific solutions will vary, depending on your storage vendor, so the storage vendor may need to be contacted to assist with tuning the storage. One potential solution includes implementing multi-pathing technology to improve the throughput of the storage.

DBMS_REPAIR example


PURPOSE
This document provides an example of DBMS_REPAIR as introduced in Oracle 8i.
Oracle provides different methods for detecting and correcting data block
corruption - DBMS_REPAIR is one option.
WARNING: Any corruption that involves the loss of data requires analysis to
understand how that data fits into the overall database system. Depending on
the nature of the repair, you may lose data and logical inconsistencies can
be introduced; therefore you need to carefully weigh the gains and losses
associated with using DBMS_REPAIR.
SCOPE & APPLICATION
This article is intended to assist an experienced DBA working with an Oracle
Worldwide Support analyst only.  This article does not contain general
information regarding the DBMS_REPAIR package, rather it is designed to provide
sample code that can be customized by the user (with the assistance of
an Oracle support analyst) to address database corruption.  The
"Detecting and Repairing Data Block Corruption" Chapter of the Oracle8i
Administrator's  Guide should be read and risk assessment analyzed prior to
proceeding.
RELATED DOCUMENTS
Oracle 8i Administrator's Guide,  DBMS_REPAIR Chapter
Introduction
=============
Note: The DBMS_REPAIR package is used to work with corruption in the
transaction layer and the data layer only (software corrupt blocks).
Blocks with physical corruption (ex. fractured block) are marked as
the block is read into the buffer cache and DBMS_REPAIR ignores all
blocks marked corrupt.
The only block repair in the initial release of DBMS_REPAIR is to
*** mark the block software corrupt ***.
DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM must both be set to FALSE.
A backup of the file(s) with corruption should be made before using package.
Database Summary
===============
A corrupt block exists in table T1.
SQL> desc t1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
COL1                                      NOT NULL NUMBER(38)
COL2                                               CHAR(512)
SQL> analyze table t1 validate structure;
analyze table t1 validate structure
*
ERROR at line 1:
ORA-01498: block check failure - see trace file
---> Note: In the trace file produced from the ANALYZE, it can be determined
---        that the corrupt block contains 3 rows of data (nrows = 3).
---        The leading lines of the trace file follows:
Dump file /export/home/oracle/product/8.1.5/admin/V815/udump/v815_ora_2835.trc
Oracle8 Enterprise Edition Release 8.1.5.0.0 - Beta
With the Partitioning option
*** 1998.12.16.15.53.02.000
*** SESSION ID:(7.6) 1998.12.16.15.53.02.000
kdbchk: row locked by non-existent transaction
table=0   slot=0
lockid=32   ktbbhitc=1
Block header dump:  0x01800003
Object id on Block? Y
seg/obj: 0xb6d  csc: 0x00.1cf5f  itc: 1  flg: -  typ: 1 - DATA
fsl: 0  fnx: 0x0 ver: 0x01
Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   xid:  0x0002.011.00000121    uba: 0x008018fb.0345.0d  --U-    3  fsc
0x0000.0001cf60
data_block_dump
===============
tsiz: 0x7b8
hsiz: 0x18
pbl: 0x28088044
bdba: 0x01800003
flag=-----------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x19d
avsp=0x185
tosp=0x185
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0x5ff
0x14:pri[1]     offs=0x3a6
0x16:pri[2]     offs=0x19d
block_row_dump:
[... remainder of file not included]
end_of_block_dump
DBMS_REPAIR.ADMIN_TABLES (repair and orphan key
================================================
ADMIN_TABLES provides administrative functions for repair and orphan key tables.
SQL> @adminCreate
SQL> connect sys/change_on_install
Connected.
SQL>
SQL> -- Repair Table
SQL>
SQL> declare
2  begin
3  -- Create repair table
4  dbms_repair.admin_tables (
5  --    table_name => 'REPAIR_TABLE',
6      table_type => dbms_repair.repair_table,
7      action => dbms_repair.create_action,
8      tablespace => 'USERS');          -- default TS of SYS if not specified
9  end;
10  /
PL/SQL procedure successfully completed.
SQL> select owner, object_name, object_type
2  from dba_objects
3  where object_name like '%REPAIR_TABLE';
OWNER                 OBJECT_NAME                      OBJECT_TYPE
------------------------------------------------------------------
SYS                   DBA_REPAIR_TABLE                 VIEW
SYS                   REPAIR_TABLE                     TABLE
SQL>
SQL> -- Orphan Key Table
SQL>
SQL> declare
2  begin
3  -- Create orphan key table
4  dbms_repair.admin_tables (
5      table_type => dbms_repair.orphan_table,
6      action => dbms_repair.create_action,
7      tablespace => 'USERS');          -- default TS of SYS if not specified
8  end;
9  /
PL/SQL procedure successfully completed.
SQL> select owner, object_name, object_type
2  from dba_objects
3  where object_name like '%ORPHAN_KEY_TABLE';
OWNER                 OBJECT_NAME                      OBJECT_TYPE
------------------------------------------------------------------
SYS                   DBA_ORPHAN_KEY_TABLE             VIEW
SYS                   ORPHAN_KEY_TABLE                 TABLE
DBMS_REPAIR.CHECK_OBJECT
=========================
CHECK_OBJECT procedure checks the specified object and populates the repair
table with information about corruption and repair directive(s).  Validation
consists of block checking all blocks in the object.  All blocks previously
marked corrupt will be skipped.
Note: In the initial release of DBMS_REPAIR the only repair is to mark the
block as software corrupt.
SQL> @checkObject
SQL> set serveroutput on
SQL>
SQL> declare
2     rpr_count int;
3  begin
4     rpr_count := 0;
5  dbms_repair.check_object (
6     schema_name => 'SYSTEM',
7     object_name => 'T1',
8     repair_table_name => 'REPAIR_TABLE',
9     corrupt_count => rpr_count);
10     dbms_output.put_line('repair count: ' || to_char(rpr_count));
11  end;
12  /
repair count: 1
PL/SQL procedure successfully completed.
SQL> desc repair_table
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OBJECT_ID                                 NOT NULL NUMBER
TABLESPACE_ID                             NOT NULL NUMBER
RELATIVE_FILE_ID                          NOT NULL NUMBER
BLOCK_ID                                  NOT NULL NUMBER
CORRUPT_TYPE                              NOT NULL NUMBER
SCHEMA_NAME                               NOT NULL VARCHAR2(30)
OBJECT_NAME                               NOT NULL VARCHAR2(30)
BASEOBJECT_NAME                                    VARCHAR2(30)
PARTITION_NAME                                     VARCHAR2(30)
CORRUPT_DESCRIPTION                                VARCHAR2(2000)
REPAIR_DESCRIPTION                                 VARCHAR2(200)
MARKED_CORRUPT                            NOT NULL VARCHAR2(10)
CHECK_TIMESTAMP                           NOT NULL DATE
FIX_TIMESTAMP                                      DATE
REFORMAT_TIMESTAMP                                 DATE
SQL> select object_name, block_id, corrupt_type, marked_corrupt,
2  corrupt_description, repair_description
3  from repair_table;
OBJECT_NAME                      BLOCK_ID CORRUPT_TYPE MARKED_COR
------------------------------ ---------- ------------ ----------
CORRUPT_DESCRIPTION
--------------------------------------------------------------------------------
REPAIR_DESCRIPTION
--------------------------------------------------------------------------------
T1                                      3            1 FALSE
kdbchk: row locked by non-existent transaction
table=0   slot=0
lockid=32   ktbbhitc=1
mark block software corrupt
Data Extraction
===============
The repair table indicates that block 3 of file 6 is corrupt - but remember
that this block has not yet been marked as corrupt, therefore now is the
time to extract any meaningful data.  After the block is marked corrupt,
the entire block must be skipped.
1. Determine the number of rows in the block from ALTER SYSTEM DUMP (nrows = 3).
2. Query the corrupt object and extract as much information as possible.
SQL> -- The following query can be used to salvage data from a corrupt block.
SQL> -- Creating a temporary table facilitates data insertion.
SQL> create table temp_t1 as
2  select * from system.t1
3  where dbms_rowid.rowid_block_number(rowid) = 3
4  and dbms_rowid.rowid_to_absolute_fno (rowid, 'SYSTEM','T1') = 6;
Table created.
SQL> select col1 from temp_t1;
COL1
----------
2
3
DBMS_REPAIR.FIX_CORRUPT_BLOCKS  (ORA-1578)
============================================
FIX_CORRUPT_BLOCKS procedure fixes the corrupt blocks in the specified objects
based on information in the repair table.  After the block has been marked as
corrupt,  an ORA-1578 results when a full table scan is performed.
SQL> declare
2     fix_count int;
3  begin
4     fix_count := 0;
5  dbms_repair.fix_corrupt_blocks (
6     schema_name => 'SYSTEM',
7     object_name => 'T1',
8     object_type => dbms_repair.table_object,
9     repair_table_name => 'REPAIR_TABLE',
10     fix_count => fix_count);
11     dbms_output.put_line('fix count: ' || to_char(fix_count));
12  end;
13  /
fix count: 1
PL/SQL procedure successfully completed.
SQL> select object_name, block_id, marked_corrupt
2  from repair_table;
OBJECT_NAME                      BLOCK_ID MARKED_COR
------------------------------ ---------- ----------
T1                                      3 TRUE
SQL> select * from system.t1;
select * from system.t1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 3)
ORA-01110: data file 6: '/tmp/ts_corrupt.dbf'
DBMS_REPAIR.DUMP_ORPHAN_KEYS
==============================
DUMP_ORPHAN_KEYS reports on index entries that point to rows in corrupt data
blocks.
SQL> select index_name from dba_indexes
2  where table_name in (select distinct object_name from repair_table);
INDEX_NAME
------------------------------
T1_PK
SQL> @dumpOrphanKeys
SQL> set serveroutput on
SQL>
SQL> declare
2     key_count int;
3  begin
4     key_count := 0;
5  dbms_repair.dump_orphan_keys (
6     schema_name => 'SYSTEM',
7     object_name => 'T1_PK',
8     object_type => dbms_repair.index_object,
9     repair_table_name => 'REPAIR_TABLE',
10     orphan_table_name => 'ORPHAN_KEY_TABLE',
11     key_count => key_count);
12     dbms_output.put_line('orphan key count: ' || to_char(key_count));
13  end;
14  /
orphan key count: 3
PL/SQL procedure successfully completed.
SQL> desc orphan_key_table
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
SCHEMA_NAME                               NOT NULL VARCHAR2(30)
INDEX_NAME                                NOT NULL VARCHAR2(30)
IPART_NAME                                         VARCHAR2(30)
INDEX_ID                                  NOT NULL NUMBER
TABLE_NAME                                NOT NULL VARCHAR2(30)
PART_NAME                                          VARCHAR2(30)
TABLE_ID                                  NOT NULL NUMBER
KEYROWID                                  NOT NULL ROWID
KEY                                       NOT NULL ROWID
DUMP_TIMESTAMP                            NOT NULL DATE
SQL> select index_name, count(*) from orphan_key_table
2  group by index_name;
INDEX_NAME                       COUNT(*)
------------------------------ ----------
T1_PK                                   3
Note: Index entry in the orphan key table implies that the index should be
rebuilt to guarantee the a table probe and an index probe return the same
result set.
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS
===============================
SKIP_CORRUPT_BLOCKS enables/disables the skipping of corrupt blocks during
index and table scans of a specified object.
Note: If an index and table are out of sync, then a SET TRANSACTION READ ONLY
transaction may be inconsistent in situations where one query probes only
the index and then a subsequent query probes both the index and the table.
If the table block is marked corrupt, then the two queries will return
different results.
Suggestion: If SKIP_CORRUPT_BLOCKS is enabled, then rebuild any indexes
identified in the orphan key table (or all index associated with object
if DUMP_ORPHAN_KEYS was omitted).
SQL> @skipCorruptBlocks
SQL> declare
2  begin
3  dbms_repair.skip_corrupt_blocks (
4     schema_name => 'SYSTEM',
5     object_name => 'T1',
6     object_type => dbms_repair.table_object,
7     flags => dbms_repair.skip_flag);
8  end;
9  /
PL/SQL procedure successfully completed.
SQL> select table_name, skip_corrupt from dba_tables
2  where table_name = 'T1';
TABLE_NAME                     SKIP_COR
------------------------------ --------
T1                             ENABLED
SQL> -- rows in corrupt block skipped, no errors on full table scan
SQL> select * from system.t1;
COL1              COL2
--------------------------------------------------------------------------------
4                 dddd
5                 eeee
--> Notice the pk index has not yet been corrected.
SQL> insert into system.t1 values (1,'aaaa');
insert into system.t1 values (1,'aaaa')
*
SQL> select * from system.t1 where col1 = 1;
no rows selected
DBMS_REPAIR.REBUILD_FREELISTS
===============================
REBUILD_FREELISTS rebuilds freelists for the specified object.
SQL> declare
2  begin
3  dbms_repair.rebuild_freelists (
4     schema_name => 'SYSTEM',
5     object_name => 'T1',
6     object_type => dbms_repair.table_object);
7  end;
8  /
PL/SQL procedure successfully completed.
Rebuild Index
=============
Note:  Every index identified in the orphan key table should be rebuilt to
ensure consistent results.
SQL> alter index system.t1_pk rebuild online;
Index altered.
SQL> insert into system.t1 values (1, 'aaaa');
1 row created.
SQL> select * from system.t1;
COL1              COL2
--------------------------------------------------------------------------------
4                 dddd
5                 eeee
1                 aaaa
Note - The above insert statement was used to provide a simple example.
This is the perfect world - we know the data that was lost.  The temporary
table (temp_t1) should also be used to include all rows extracted from
the corrupt block.
Conclusion
==========
At this point the table T1 is available but data loss was incurred.  In general,
data loss must be seriously considered before using the DBMS_REPAIR package for
mining the index segment and/or table block dumps is very complicated and
logical inconsistencies may be introduced.  In the initial release, the only
repair affected by DBMS_REPAIR is to mark the block as software corrupt.
<<End of Article>

沪ICP备14014813号

沪公网安备 31010802001379号