使用logminer日志挖掘技术
如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!
诗檀软件专业数据库修复团队
服务热线 ： 13764045638 QQ号:47079569 邮箱：service@parnassusdata.com
[gview file=”https://www.askmac.cn/wp-content/uploads/2014/11/less11_Flashback.pdf”]
使用Oracle闪回flashback技术
2009/11/25 by Leave a Comment
使用Oracle闪回flashback技术
如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!
诗檀软件专业数据库修复团队
服务热线 ： 13764045638 QQ号:47079569 邮箱：service@parnassusdata.com
[gview file=”https://www.askmac.cn/wp-content/uploads/2014/11/less11_Flashback.pdf”]
手动递增SCN号的几种方法：How to increase System Change Number by manual
2009/07/08 by 3 Comments
手动递增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）
2009/03/21 by Leave a Comment
本文永久地址：https://www.askmac.cn/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
2009/01/19 by Leave a Comment
某用户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
2006/04/19 by 1 Comment
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>
