oracle dba手记1chapter2-08ora1200错误裸设备恢复

1、ORA-01200设备恢复错误（网名 Eygle）Oracle ACE 总监，云和恩墨 CEO，ITPUB超级版主， DBA 服务的倡导者和实践者，致力于以技术服务客户。著有深入 Oracle、循序渐进 Oracle、深入浅出 Oracle等畅销书，深受读者尊敬和爱戴。个人 ：ht0 ORA-01200 ORA-01200 Z40XXX 040=)X resize BZ*V7T Oracle =41RY=400d* YVS0Y00VQ1YH4-eYG.80Y 327 678 655 1040;840 rman 0Y*1Gd rman 0V rman =Y configure exclude f

2、or tablespace PUB_PLOB2_01;usingdatabase controlfile instead of recovery catalogtablespace PUB_PLOB2_01 will be excluded from future whole database backupsnew RMAN configuration parameters are sucsfully storedX0VXvh0w 126MB07T1Xxh71Mar 9 16: 41: 04 2009Errors in file /opt/prod/admin/ds/bdump/ds2_ckp

3、t_827498.trc:ORA- 01171: datafile 59 going offline due to error advancing checkpo ORA-01122: database file 59 failed verification checkORA-01110: data file 59: /dev/rpub_plob2_01ORA-01200: actual file size of 327678 is smallern correct size of 655104 blocks0GX0X0CKPT X71Oracle9i Entrise Edition Rele

4、ase 9.2.0.7.0 - 64bit ProductionWith the Partitioning and Real Application Clusters options JServer Release 9.2.0.7.0 - ProductionORACLE_HOME = /opt/prod/ora92System name:AIXNode name:oracle2Release:150007D76F4C00Ver:Machine:Instance name: ds2Redo thread mounted by this instance: 2Oracle proUnix pro

5、s number: 10s: 827498, image: oracleoracle2 (CKPT)* 2009-03-09 16:41:04.070* SESID:(11.1) 2009-03-09 16:41:04.028ORA-01171: datafile 59 going offline due to error advancing checkpo ORA-01122: database file 59 failed verification checkORA-01110: data file 59: /dev/rpub_plob2_01ORA-01200: actual file

6、size of 327678 is smallern correct size of 655104 blocksGB0V0RzXvh*&171&Wed Nov 16 09:00:44 2005create tablespace pub_plob2_01 datafile /dev/rpub_plob2_01 size 126m segment space management autoWed Nov 16 09:00:45 2005Completed: create tablespace pub_plob2_01 datafile /dev/rpub V$RECOVER_FILe.T7/1XH

7、 OFFLINE 0XhX0 5118MB0 655 104 select * from v$recover_file;FILE# ONLINEONLINE_ ERRORCHANGE# TIME59 OFFLINE OFFLINE5146850095 09-MAR-09SQL select file#,name,blocks,bytes/1024/1024,sus from v$datafile where file#=59;FILE# NAMEBLOCKS BYTES/1024/1024 SUS59 /dev/rpub_plob2_016551045118 RECOVERH.OFFLINE/

8、0 Extent h14XH PUB_PLOB2_01 0:O74 select tablespace_name from dba_data_files where file_id=59; TABLESPACE_NAMEPUB_PLOB2_01SQL select file_id from dba_data_files where tablespace_name=PUB_PLOB2_01; FILE_ID59 10 0whT71SQL select count(*) from dba_segments where tablespace_name=PUB_PLOB2_01; COUNT(*)10

9、SQL select segment_name,PARTITION_NAME ,bytes2 from dba_segments where tablespace_name=PUB_PLOB2_01;SEGMENT_NAMEPARTITION_NAMEBYTESSYS_IL0000031312C00004$ SYS_IL0000031312C00003$ SYS_IL0000031312C00002$ SYS_IL0000031390C00003$ SYS_IL0000031425C00005$ SYS_LOB0000031312C00002$ SYS_LOB0000031312C00003$

10、 SYS_LOB0000031312C00004$ SYS_LOB0000031390C00003$SYS_LOB0000031425C00005$SYS_IL_P511 SYS_IL_P491 SYS_IL_P471 SYS_IL_P531 SYS_IL_P551 SYS_LOB_P461 SYS_LOB_P481 SYS_LOB_P501 SYS_LOB_P521SYS_LOB_P54165536327680393216655363932168053063687969177606553665536114085068810 rowectedV4 DBA_LOBS J9vv1SQL selec

11、t table_name,column_name,segment_name,index_namefrom dba_lobs where segment_name in (select segment_name from dba_segments where tablespace_name=PUB_PLOB2_01);&172&SQL select count(*) from dba_extents where file_id=59; COUNT(*)0TABLE_NAMECOLUMN_NAMESEGMENT_NAMEINDEX_NAMETB_ARTICLE_CONTENT TB_ARTICLE

12、_CONTENT TB_ARTICLE_CONTENT TB_ARTICLE_BLOBTB_ARTICLE_TPCONTENT PRE_PHOTO ARTICLE_XML AFFIX_BLOBMICROCOPYSYS_LOB0000031312C00002$ SYS_LOB0000031312C00003$ SYS_LOB0000031312C00004$ SYS_LOB0000031390C00003$SYS_LOB0000031425C00005$SYS_IL0000031312C00002$ SYS_IL0000031312C00003$ SYS_IL0000031312C00004$

13、SYS_IL0000031390C00003$SYS_IL0000031425C00005$1Oracle .Blob)Clob /Zd4 desc dba_lobs;NameNull?TypeOWNER TABLE_NAME COLUMN_NAME SEGMENT_NAMEINDEX_NAMEVARCHAR2(30) VARCHAR2(30) VARCHAR2(4000) VARCHAR2(30) VARCHAR2(30) NUMBERNUMBER NUMBER NUMBER VARCHAR2(10) VARCHAR2(7)VARCHAR2(3)CKPCTVER RETENTION FREE

14、POOLS CACHE LOGGINGIN_ROWHX OFFLINE0vcV1SQL alter database datafile /dev/rpub_plob2_01 resize 3800M; alter database datafile /dev/rpub_plob2_01 resize 3800M*ERRORine 1:ORA-00376: file 59 cannot be read at this timeORA-01110: data file 59: /dev/rpub_plob2_01 ONLINE P1SQL alter tablespace PUB_PLOB2_01

15、 online; alter tablespace PUB_PLOB2_01 online*ERRORine 1:ORA-01113: file 59 needs media recoveryORA-01110: data file 59: /dev/rpub_plob2_010v0Z1oracleoracle2 $exp publish/publish file=all.dmp tables=TB_ARTICLE_TP,TB_ARTICLE_CONTENTExport: Release 9.2.0.7.0 - Production on Fri Mar 27 15:24:18 2009Cop

16、yright (c) 1982, 2002, Oracle Corporation.s.Connected to: Oracle9i Entrise Edition Release 9.2.0.7.0 - 64bit ProductionWith the Partitioning and Real Application Clusters options JServer Release 9.2.0.7.0 - ProductionExport done in UTF8 character set and UTF8 NCHAR character set&173&About to export

17、specified tables via Conventional Path . . exporting table. . exporting partitionTB_ARTICLE_TPPUB_DATA2_P01EXP-00056: ORACLE error 376 encounteredORA-00376: file 59 cannot be read at this time ORA-01110: data file 59: /dev/rpub_plob2_01. . exporting table. . exporting partitionTB_ARTICLE_CONTENTPUB_

18、DATA2_P01EXP-00056: ORACLE error 376 encounteredORA-00376: file 59 cannot be read at this time ORA-01110: data file 59: /dev/rpub_plob2_01Export terminated sucsfully with warnings.X9XHX9XX80VX) Xhp0w=xh*pXzVd7U.Oracle10g d Level 8 p/1pXhd7U1V7ThXp04hX0FILE HEADER X0 zV7X=1DATA FILE #59:- 59 Xz(name

19、#64) /dev/rpub_plob2_01 Xcreation size=16128 block size=8192 s-016128 8k = 126MBus=0 x1c head=64 tail=64 dup=1tablespace 77, index=60 krfil=59 prev_file=0unrecoverable scn: 0 x0000.00000000 01/01/1988 00:00:00Checkpocnt:2583 scn: 0 x0001.32c6b32f 03/09/2009 10:00:56 SCN Stop scn: 0 x0001.32c92794 03

20、/09/2009 16:41:04- Stop SCN0H CheckpoSCN Creation Checkpoed at scn: 0 x0000.00edd285 11/16/2005 09:00:45thread:2 rba:(0 x173.9bb9.10) - h04 011 T 2 920Db ID=658484424=0 x273facc8, Db Name=DS ID Activation ID=0=0 x0Control Seq=64927=0 xfd9f, File size=655104=0 x9ff00 XX&174&alter sesset events immedi

21、ate trace name FILE_HDRS level 10;alter sesset events immediate trace name CONTROLF level 10;-GX 655104 655 104 0X 327 678 NZ3X80XG0Oracle Z0HV6 X0wO 126MB0=0804 Oracle Z0 8070Oracle Z80ZV608 0V Oracle cG*VX0G0X81SQL recover tablespace PUB_PLOB2_01;ORA-00279: change 5146850095 generated at 03/09/200

22、9 10:00:56 needed for thread 2 ORA-00289: suggestion : /arch_nfs/ds_2_0000002481.dbfORA-00280: change 5146850095 for thread 2 is in sequence #2481Specify log: =suggested | filename | AUTO | CANCEL/arch_nfs/ds_2_0000002481.dbfORA-00279: change 5146850095 generated at 03/06/2009 14:19:50 needed for th

23、read 1 ORA-00289: suggestion : /arch_nfs/ds_1_0000002119.dbfORA-00280: change 5146850095 for thread 1 is in sequence #2119Specify log: =suggested | filename | AUTO | CANCEL/arch_nfs/ds_1_0000002119.dbfORA-00279: change 5146850128 generated at 03/09/2009 10:04:16 needed for thread 1 ORA-00289: sugges

24、tion : /arch_nfs/ds_1_0000002120.dbfORA-00280: change 5146850128 for thread 1 is in sequence #2120ORA-00278: log file /arch_nfs/ds_1_0000002119.dbf no longer needed for this recoverySpecify log: =suggested | filename | AUTO | CANCELAUTOORA-00283: recovery sescanceled due to errorsORA-01237: cannot e

25、xtend datafile 59ORA-01110: data file 59: /dev/rpub_plob2_01ORA-19502: write error on file /dev/rpub_plob2_01, blockno 327809 (blocksize=8192) ORA-27063: skgfospo: number of bytes read/written is incorrectIBM AIX RISC System/6000 Error: 6: No such device or address Additional information: -1Addition

26、al information: 1048576ORA- 19502: write error on file / dev/ rpub_plob2_01, blockno 327681 ( blocksize=8192) ORA-27063: skgfospo: number of bytes read/written is incorrectIBM AIX RISC System/6000 Error: 6: No such device or address Additional information: -1Additional information: 1048576&175&ORA-0

27、1112: media recovery not startedHdv0Vcd lspv U?6.ed4IBM ?0d AIX c/1oracleoracle2 $lspvdlmfdrv0 dlmfdrv1 dlmfdrv2 dlmfdrv3dlmfdrv1000043c5f1a212d0600043c5f1a212d8200043c5f1a212dee 00043c5f1a212e6000043c5f1a21318eora_rac_vg ora_rac_vg ora_rac_vg ora_rac_vgarch2_vghVXh0 pub_plob2_01 G 30 PP1oracleoracl

28、e2 $lspv -l dlmfdrv1 dlmfdrv1:LV NAME(pub_pdata2_01 pub_pind1_01 pub_pind1_09 pub_plob2_01pub_pdata2_09LPsPPsDISTRIBUTIONMOUNT PO20332033302000.00.20.00.0000.00.03.00.0000.00.03.00.003000.00.20.00.00N/A N/A N/A00. 00. 30. 00. 00N/AN/ A20 dlmfdrv10 128MB0D 3840MB0_H 5GB 0H80GX.6 /1oracleoracle2 $lspv

29、 PHYSICAL VOLUME:PV IDENTIFIER: PV SE:STALE PARTITIONS: PP SIZE:TOTAL PPs:FREE PPs:USED PPs:FREE DISTRIBUTION: USED DISTRIBUTION:dlmfdrv1dlmfdrv1VOLUME GROUP:ora_rac_vg00043c5f1a212d82 VG IDENTIFIERactive00043c5f00004c0000000104eb3fbec00128 megabyte( s)651 (83328 megabytes)329 (42112 megabytes)322 (

30、41216 megabytes) 21.00.48.130.130 110.130.82.00.00ALLOCATABLE: yes LOGICAL VOLUMES: 16VG DESCRIPTORS: 1HOT SPARE: noGV1YVY.eY/*X60*&176&4J0X0 ONLINE * Metalink * W8n0i0 Metalink Note1551265.1 4Jv0W0VXc6X* 0604Je0:X80 Metalink 6e1900:xt0Vd*q4HX0HX.OFFLINE/0V rename 0RVgX800DV *6d smitty lv 40dU1rootO

31、racle2 #mklv -y recv_plob2_01 -wn -sn -rn ora_rac_vg 50 recvr_plob2_01rootOracle1 #n oracle:dba /dev/rrecvr_plob2_01rootOracle1 #ls -al /dev/rr*crw-rw 1 oracle dba57,112 Mar 29 15:50 /dev/rrecvr_plob2_017 dd UXpX*d dd U0H8 OS 60 Xc0VW dd X00|sD 122 88.12k/0Oracle XG 1 8k 04 select file#,name,bytes/1

32、024/1024,BLOCK1_OFFSET from v$datafile where file# select file#,name,block1_offset from v$datafile where rownum n 0DV“k”cskip=n B n 327 678 8k 047 327 678*2=655 356 4k 06 X0 dd 655 358 4k 04*7c06X dd Upc61V dbv U.7/47X0X1oracleoracle2 $dbv file=plob2_01.dbf blocksize=8192DBVERIFY: Release 9.2.0.7.0

33、- Production on Sun Mar 29 11:26:26 2009Copyright (c) 1982, 2002, Oracle Corporation.DBVERIFY - Verification starting : FILE = plob2_01.dbfs.DBVERIFY - Verification completeTotal Total Total Total Total Total Total Total Total TotalTotalPages Pages Pages Pages Pages Pages Pages Pages Pages PagesPage

34、sExamined:3274240012302995900027711005146994157 (1.852026861)Prosed (Data) :Failing (Data) :Prosed (Index) :Failing (Index) :ProProsed (Other) :sed (Seg):Failing (Seg) EmptyMarked CorruptInfluxHighest block SCN7 dd UX00WG4 655 104 X0Vc9X)X1&178&oracleoracle2 $dd if=/dev/zero of=plob2_01.dbf bs=8192

35、seek=327679 count=327426 conv=notrunc 327426+0 records in.327426+0 records out.oracleoracle2 $dd if=/dev/rpub_plob2_01 of=plob2_01.dbf bs=4k skip=1 count=655358 655358+0 records in.655358+0 records out.7X60s4 recover tablespace PUB_PLOB2_01;ORA-00279: change 5146850128 generated at 03/09/2009 10:04:

36、16 needed for thread 1 ORA-00289: suggestion : /arch_nfs/ds_1_0000002120.dbfORA-00280: change 5146850128 for thread 1 is in sequence #2120Specify log: =suggested | filename | AUTO | CANCEL/arch_nfs/ds_1_0000002120.dbfORA-00279: change 5146850128 generated at 03/09/2009 10:00:56 needed for thread 2 O

37、RA-00289: suggestion : /arch_nfs/ds_2_0000002481.dbfORA-00280: change 5146850128 for thread 2 is in sequence #2481Specify log: =suggested | filename | AUTO | CANCEL/arch_nfs/ds_2_0000002481.dbfORA-00279: change 5146944507 generated at 03/09/2009 16:12:38 needed for thread 2 ORA-00289: suggestion : /

38、arch_nfs/ds_2_0000002482.dbfORA-00280: change 5146944507 for thread 2 is in sequence #2482ORA-00278: log file /arch_nfs/ds_2_0000002481.dbf no longer needed for this recoverySpecify log: =suggested | filename | AUTO | CANCEL/arch_nfs/ds_2_0000002482.dbfLog appd.Media recovery complete.7V ONLINE0d1SQ

39、L alter tablespace PUB_PLOB2_01 online; Tablespace altered.SQL select count(*) from dba_extents where tablespace_name=PUB_PLOB2_01;COUNT(*)528SQL col name for a30SQL select file#,name,blocks from v$datafile where file#=59;FILE# NAMEBYTESBLOCKS59 /dev/rrecvr_plob2_015366611968655104&179&SQL alter dat

40、abase rename file /dev/rpub_plob2_01 to /dev/rrecvr_plob2_01; Database altered.oracleoracle2 $dd if=plob2_01.dbf of=/dev/rrecvr_plob2_01 bs=4k seek=1 1310210+0 records in.1310210+0 records out.iXh71SQL GetDBfileInfoSesaltered.Enter value for file: 59old 22:new 22: FILE#CREATION_CHANGE# CREATION_TIME

41、 TS#RFILE#&file;59;:59:15585925:2005-11-16 09:00:45:77:59:ONLINE:READ WRITE:5153513665:2009-03-31 01:02:06:0:0:0:5242880000:640000:132120576:8192:/dev/rrecvr_plob2_01:0:12288:NONESUSENABLED CHECKPOCHECKPO_CHANGE#_TIMEUNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONL

42、INE_CHANGE# ONLINE_TIMEBYTES BLOCKSCREATE_BYTES BLOCK_SIZE NAME PLUGGED_IN BLOCK1_OFFSETAUX_NAMEPL/SQL procedure sucsfully completed.dh?71SQL select segment_name,partition_name,segment_type,tablespace_name2 from dba_extents where file_id=59 and rownum 1ALTER SESSET nls_date_format=yyyy-mm-dd hh24:mi

43、:ss;set serveroutput onDECLAREl_colnm l_colvll_stringVARCHAR2 (100);VARCHAR2 (100);VARCHAR2 (4000);&180&CURSOR c_colnm ISSELECT column_name FROM dba_tab_columnsWHERE table_name = V_$DATAFILE ORDER BY column_id;BEGINOPEN c_colnm;LOOPFETCH c_colnm O l_colnm;EXIT WHEN c_colnm%NOTFOUND;l_string :=lect |

44、 l_colnm | from v_$datafile where file#= | &file;EXECUTE IMMEDIA_stringO l_colvl;DBMS_OUTPUT.put_line (RPAD (l_colnm, 30, ) | :| TO_CHAR (l_colvl);END LOOP; END;/d rman Xd0w0wd rman DV*V74ePad rman XX* qd4 EYGLE 0:1oracledy $ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 30

45、 14:41:53 2005Copyright (c) 1982, 2005, Oracle.s.SQL create tablespace eygle datafile / dev/ cciss/ c0d0p11 sizeTablespace created.10M;SQL create table eygle tablespace eygle aTable created.ect * from dba_objects;SQL select count(*) from eygle;COUNT(*)5042001&181&Vd rman copy YXXX1oracledy $ rman/RM

46、AN copy datafile / dev/ cciss/ c0d0p11 to / opt/ oracle/ eygle01. dbf;Starting backup at 30-NOV-05usingdatabase control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=137 devtype=DISK channel ORA_DISK_1: starting datafile copyinput datafile fno=00002 name=/dev/c

47、ciss/c0d0p11output filename=/opt/oracle/eygle01.dbf tag=TAG20051130T160137 recid=2 stamp=575740898 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:02Finished backup at 30-NOV-057= rename iX0 ONLINE1SQL alter database rename file /dev/cciss/c0d0p11 to /opt/oracle/eygle01.dbf; Database

48、 altered.SQL alter tablespace eygle online;Tablespace altered.SQL select file_name from dba_data_files where tablespace_name=EYGLE;FILE_NAME/opt/oracle/eygle01.dbfi001SQL inserto eygle select * from eygle;inserto eygle select * from eygle*ine 1:ERRORORA-01653: unable to extend table SYS.EYGy 128able

49、space EYGLESQL alter database datafile /opt/oracle/eygle01.dbf resize 20m;Database altered.SQL inserto eygle select * from eygle;50420 rows created.SQL commit;Commit complete.SQL alter tablespace eygle offline;Tablespace altered.d rman XR1RMAN copy datafile /opt/oracle/eygle01.dbf to /dev/cciss/c0d0

50、p11;Starting backup at 30-NOV-05usingdatabase control file instead of recovery catalog&182&SQL alter tablespace eygle offline; Tablespace altered.allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=135 devtype=DISK channel ORA_DISK_1: starting datafile copyinput datafile fno=00002 name=/opt/oracle/

51、eygle01.dbfoutput filename=/dev/cciss/c0d0p11 tag=TAG20051130T160935 recid=3 stamp=575741376 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01Finished backup at 30-NOV-05B0VX0 ONLINE01SQL alter database rename file /opt/oracle/eygle01.dbf to /dev/cciss/c0d0p11; Database altered.SQL

52、alter tablespace eygle online;Tablespace altered.SQL select count(*) from eygle;COUNT(*)10084070wVX0V7VX copy 1RMAN copy datafile /opt/oracle/oradata/eygle/eygle01.dbf to /opt/oracle/eygle01.dbf;Starting copy at 09-APR-09 allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=14 devtype=DISK channel O

53、RA_DISK_1: copied datafile 3output filename=/opt/oracle/eygle01.dbf recid=17 stamp=683762642Finished copy at 09-APR-09Starting Control File and SPFILE Autobackup at 09-APR-09piece handle=/opt/oracle/obak/controlc-1407686520-20090409-00 comment=NONE Finished Control File and SPFILE Autobackup at 09-A

54、PR-09 copy =0XG7h1= rename c1SQL alter database rename file /opt/oracle/oradata/eygle/eygle01.dbf to /opt/oracle/eygle01.dbf;Database altered.SQL select name,sNAMEus from v$datafile where file#=3;SUS/opt/oracle/eygle01.dbfOFFLINE ONLINE =0Z0X ONLINE1SQL alter database datafile /opt/oracle/eygle01.db

55、f online; Database altered.SQL select name,sus from v$datafile where file#=3;&183&Thu Apr 9 22:04:02 2009Copy of datafile 3 complete to datafile copy /opt/oracle/eygle01.dbf checkpois 8915762351571NAMESUS/opt/oracle/eygle01.dbfONLINErman dHc*Oracle HdGJ7V0? RAC 70Oracle Database 10g G ASM0Oracle Dat

56、abase 11gR2 = ASM fGH RAC 7 OCR Voting Disk 0Cb*Oracle 0 Oracle Database 12g =8 Raw Device* 2009 9 Oracle Database 11gR20 Oracle R=. 8-1/0Oracle G* 8-1 Oracle Datebase 12gR2 Oracle Database 10gR2 =0 8-2 *&184& 8-2 Oracle Datebase 10gR2 8N3Oracle 1This means customers can no longer keep their datafil

57、es, OCR or Voting disks on raw deviin Oracle12g.D RAC =0GXB0W8dp OCR VD0640dG ASM B0Hp OCR VD0880V RAC =De-Support 64* 8dpX0QdRaw Device RAC *J0Kj0JX* Oracle Database 11gR2 =0Oracle GH ASM XOracle ACFS.Oracle Automatic Storage Management Cluster File System/04X OCR VD uL0ASM Xp0Oracle fG4* Oracle 8?

58、*Oracle AV6e=0d Oracle 9.2.0.7 * T710804o0V Patch i*&185&Connected to: Oracle9i Entrise Edition Release 9.2.0.7.0 - 64bit Production With the Partitioning and Real Application Clusters optionsJServer Release 9.2.0.7.0 ProductionOracle =Ge0GOracle 1 8-3 Oralce V9vh1 select * from v$ver BANNER0HXwh1;O

59、racle Database 10g Entrise Edition Release 10.2.0.3.0 - ProdPL/SQL Release 10.2.0.3.0 - ProductionCORE10.2.0.3.0Production10.2.0.3.0 - ProductionTNS for Linux: VerNLSRTL Ver10.2.0.3.0 Production select * from v$option;al Oracle/0XFPARAMETERVALUEPartitioning ObjectsReal Application Clusters Advanced replicationBit-mapped indexes Connection multiplexing Connection pooling Database que