First exception Recovery for Oracle 12C-File header bad block

Source: Internet
Author: User
Tags dba session id reserved oracle database


Received the first recovery rescue using Oracle 12C as a production library. There are two business data file file headers damaged, other data files are all a September backup, under current conditions, hope we can help them recover the data in the business document
Database version Information


The code is as follows
Sql> select * from V$version;





BANNER con_id


-------------------------------------------------------------------------------- ----------


Oracle Database 12c Enterprise Edition release 12.1.0.1.0-64bit Production 0


Pl/sql Release 12.1.0.1.0-production 0


CORE 12.1.0.1.0 Production 0


TNS for linux:version 12.1.0.1.0-production 0


Nlsrtl Version 12.1.0.1.0-production 0



Database failure
Specific script refer to: Database recovery check script (Oracle db Recovery check)





Reading datafile '/app/oracle/oradata/freetouch/sales.dbf ' for corruption in rdba:0x00000001 (file 4, block 1)


Reread (file 4, block 1) found same corrupt data (no logical check)


Hex Dump of (file 5, block 1) in Trace file/app/oracle/diag/rdbms/valuenet/valuenet/trace/valuenet_ora_12384.trc





Corrupt block relative dba:0x00000001 (file 5, block 1)


Fractured block found during KCVXFH V8


Data in bad block:


type:0 Format:2 rdba:0x00000001


Last Change scn:0x0000.00000000 seq:0x1 flg:0x05


spare1:0x0 spare2:0x0 spare3:0x0


Consistency value in tail:0x00000001


Check value in block header:0xa701


Computed Block checksum:0x0





Reading datafile '/app/oracle/oradata/freetouch/drp_200200 ' for corruption in rdba:0x00000001 (file 5, block 1)


Reread (file 5, block 1) found same corrupt data (no logical check)


Hex Dump of (file 4, block 1) in Trace file/app/oracle/diag/rdbms/valuenet/valuenet/trace/valuenet_ora_12384.trc





Corrupt block relative dba:0x00000001 (file 4, block 1)


Fractured block found during KCVXFH V8


Data in bad block:


type:0 Format:2 rdba:0x00000001


Last Change scn:0x0000.00000000 seq:0x1 flg:0x05


spare1:0x0 spare2:0x0 spare3:0x0


Consistency value in tail:0x00000001


Check value in block header:0xa701


Computed Block checksum:0x0


ODU the exception file is not recognized

The code is as follows
[Oracle@db odu]$./odu





Oracle Data unloader Trial Version 4.1.3





Copyright (c) 2008,2009,2010,2011 Xiongjun. All rights reserved.








Loading Default Config .....





Byte_order Little


Block_size 8192


Db_timezone-7


Invalid DB timezone:-7


Client_timezone 8


Invalid Client Timezone:8


Asmfile_extract_path/home/oracle/hongye/odu/data


Data_path/home/oracle/hongye/odu/data


Lob_path/home/oracle/hongye/odu/data


Charset_name ZHS16GBK


Ncharset_name AL16UTF16


Output_form DMP


Error at line 10.


Lob_storage infile


Clob_byte_order Big


Trace_level 1


Delimiter |


unload_deleted Yes


File_header_offset 0


Is_tru64 No


RECORD_ROW_ADDR No


Convert_clob_charset Yes





Load config file ' config.txt ' successful


Loading default ASM Disk File ...





Can not open file ' asmdisk.txt ', error message:no such file or directory.


Loading default control File ...





Unknown file format '/app/oracle/oradata/freetouch/sales.dbf '


Unknown file format '/app/oracle/oradata/freetouch/drp_200200 '





ts# fn RFN bsize blocks BF offset filename


---- ---- ---- ----- -------- -- ------ --------------------------------------------


1 1 1 8192 194560 N 0/app/oracle/oradata/freetouch/system01.dbf


6 2 8192 45840 N 0/app/oracle/oradata/freetouch/example01.dbf


1 3 3 8192 907520 N 0/app/oracle/oradata/freetouch/sysaux01.dbf


4 1024 8192 0 N 0/app/oracle/oradata/freetouch/sales.dbf


5 1024 9 8192 0 N 0/app/oracle/oradata/freetouch/drp_200200


4 6 6 8192 128320 N 0/app/oracle/oradata/freetouch/users01.dbf


7 7 7 8192 780288 N 0/app/oracle/oradata/freetouch/undotbs03.dbf


One 8 8 8192 25600 N 0/app/oracle/oradata/freetouch/indx01.dbf


Load control file ' Control.txt ' successful


Loading dictionary Data......done





Loading scanned Data......done



Dul the exception file is not recognized


The code is as follows
[Oracle@db dul]$./dul





Data unloader:10.2.0.5.32-internal only-on Sun Nov 2 23:34:42 2014


With 64-bit IO functions





Copyright (c) 1994 2014 Bernard van Duijnen all rights reserved.





Strictly Oracle Internal Use only








DUL:Warning:ulimit process stack size is only 33554432


Found db_id = 270587870


Found db_name = valuenet





DUL:Warning:Cannot Verify file Number for/app/oracle/oradata/freetouch/sales.dbf


DUL:Warning:First four bytes (162 0 0) of Block 2 are not the start of a proper data block header


DUL:Warning:Block corruption or configuration error


DUL:Warning:Check db_block_size and/or osd_file_leader_size and/or file offset


DUL:Error:File number can only is zero for single tablespace datafiles





DUL:Warning:Cannot Verify file Number for/app/oracle/oradata/freetouch/drp_200200


DUL:Warning:First four bytes (162 0 0) of Block 2 are not the start of a proper data block header


DUL:Warning:Block corruption or configuration error


DUL:Warning:Check db_block_size and/or osd_file_leader_size and/or file offset


DUL:Error:File number can only is zero for single tablespace datafiles


Dul> show Datafiles;


ts# rf# Start blocks offs Open Err file name


0 1 0 194561 0 1 0/app/oracle/oradata/freetouch/system01.dbf


1 3 0 907521 0 1 0/app/oracle/oradata/freetouch/sysaux01.dbf


4 6 0 128321 0 1 0/app/oracle/oradata/freetouch/users01.dbf


7 7 0 780289 0 1 0/app/oracle/oradata/freetouch/undotbs03.dbf


8 0 25601 0 1 0/app/oracle/oradata/freetouch/indx01.dbf


6 0 45841 0 1 0/app/oracle/oradata/freetouch/example01.dbf



The exception file cannot be recognized properly using Dul/odu. The header of the document is indeed corrupted



DBV detection


The code is as follows
[Oracle@db trace]$ dBV file=/app/oracle/oradata/freetouch/drp_200200





Dbverify:release 12.1.0.1.0-production on Sun Nov 2 14:08:34 2014





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





Dbverify-verification Starting:file =/app/oracle/oradata/freetouch/drp_200200








Dbverify-verification Complete





Total Pages examined:194560


Total Pages processed (Data): 114596


Total Pages Failing (Data): 0


Total Pages processed (Index): 26198


Total Pages Failing (Index): 0


Total Pages processed (other): 37787


Total Pages processed (SEG): 1


Total Pages Failing (Seg): 0


Total Pages empty:15979


Total Pages Marked corrupt:0


Total Pages influx:0


Total Pages encrypted:0


Highest block scn:129603862 (0.129603862)





[Oracle@db ~]$ dBV file=/app/oracle/oradata/freetouch/sales.dbf





Dbverify:release 12.1.0.1.0-production on Sun Nov 2 23:12:05 2014





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





Dbverify-verification Starting:file =/app/oracle/oradata/freetouch/sales.dbf








Dbverify-verification Complete





Total Pages examined:655360


Total Pages processed (Data): 294938


Total Pages Failing (Data): 0


Total Pages processed (Index): 233404


Total Pages Failing (Index): 0


Total Pages processed (LOB): 38


Total Pages Failing (Lob): 0


Total Pages processed (other): 23252


Total Pages processed (SEG): 0


Total Pages Failing (Seg): 0


Total Pages empty:103728


Total Pages Marked corrupt:0


Total Pages influx:0


Total Pages encrypted:0


Highest block scn:134665298 (0.134665298)



DBV detection results are not bad block, but V$datafile_header and alert log in bad block, the initial judgment is because the file is BIGFILE,DBV not detected a bad block of file header, the actual data file header damage, Other block is normal. Fortunately, the library has a September Rman backup (lost Intermediate archive), so use Rman to restore the September data file, and then use DD to copy two blocks (block 0 and Block 1) to the exception file.


  code is as follows
[Root@db freetouch]# dd if=/app1/oracle/oradata/freetouch/sales.dbf bs=8192 count=2 of=/tmp/odu/sales.2
2+0 Records in
2+0 Records out
16384 bytes (kB) copied, 0.0176368 s, 929 kb/s
[Root@db freetouch]# dd if=/tmp/odu/sales.2 of=/app/oracle/oradata/freetouch/sales.dbf bs=8192 count=2 conv=notrunc
2+0 Records in
2+0 Records out
16384 bytes (kB) copied, 6.4281e-05 s, 255 mb/sec
[Root@db freetouch]# dd if=/app1/oracle/oradata/freetouch/drp_200200 bs=8192 count=2 of=/tmp/odu/drp_200200.2
2+0 Records in
2+0 Records out
16384 bytes (kB) copied, 0.0185934 s, 881 kb/s
[Root@db freetouch]# dd if=/tmp/odu/drp_200200.2 of=/app/oracle/oradata/freetouch/drp_200200 bs=8192 count=2 conv= Notrunc
2+0 Records in
2+0 Records out
16384 bytes (kB) copied, 6.4419e-05 s, 254 mb/sec


Trying to recover a database


The code is as follows Copy Code
sql> Recover database;
Ora-00283:recovery session canceled due to errors
Ora-01610:recovery using the BACKUP controlfile option must is done


sql> Recover database using Backup controlfile;
Ora-00283:recovery session canceled due to errors
Ora-19909:datafile 4 belongs to a orphan incarnation
Ora-01110:data file 4: '/APP/ORACLE/ORADATA/FREETOUCH/SALES.DBF '


Use bbed to modify related file headers, and then resume
Specific See: Bbed solution ORA-01190 Similar method processing


The code is as follows
sql> Recover database using Backup controlfile;


Ora-00279:change 129603904 generated at 11/02/2014 19:19:54 for thread


1


Ora-00289:suggestion:


/app/oracle/recovery_area/valuenet/archivelog/2014_11_02/o1_mf_1_1_%u_.arc


Ora-00280:change 129603904 for thread 1 was in sequence #1








Specify log: {=suggested | filename | AUTO | CANCEL}
Cancel
Ora-00308:cannot Open archived log ' cancel; '
Ora-27037:unable to obtain file status
linux-x86_64 error:2: No such file or directory
Additional Information:3


Specify log: {=suggested | AUTO | CANCEL}
Cancel
Media recovery cancelled.
sql> ALTER DATABASE open resetlogs;
ALTER DATABASE open Resetlogs
*
ERROR at line 1:
Ora-01092:oracle instance terminated. Disconnection forced
Ora-00600:internal error code, arguments: [2662], [0], [129603911], [0],
[129603913], [29360256], [], [], [], [], [], []
Process id:19881
Session Id:1 Serial Number:3


ORA-600[2662] Error occurs because the SCN is relatively small, restarting the database machine, and ora-600[4194] Error


The code is as follows
sql> startup pfile= '/tmp/pfile.txt ' mount


ORACLE instance started.





Total System Global area 2505338880 bytes


Fixed Size 2291472 bytes


Variable Size 973080816 bytes


Database buffers 1526726656 bytes


Redo buffers 3239936 bytes


Database mounted.


sql> ALTER DATABASE open;


ALTER DATABASE Open


*


ERROR at line 1:


Ora-01113:file 1 Needs Media recovery


Ora-01110:data file 1: '/APP/ORACLE/ORADATA/FREETOUCH/SYSTEM01.DBF '








sql> Recover database;


Ora-00283:recovery session canceled due to errors


Ora-16433:the database or pluggable database must is opened in Read/write


Mode.



Resume recovery after rebuilding control files


The code is as follows
sql> ALTER DATABASE open resetlogs;
ALTER DATABASE open Resetlogs
*
ERROR at line 1:
Ora-01092:oracle instance terminated. Disconnection forced
Ora-00600:internal error code, arguments: [4194], [46], [19], [], [], [], [],
[], [], [], [], []
Process id:20351
Session Id:1 Serial Number:3


Set up undo_management=manual and continue recovery


  code is as follows
[oracle@db tmp ]$ sqlplus/as sysdba
 
sql*plus:release 12.1.0.1.0 Production on Sun Nov 2 19:29:45 2014
 
Copyri Ght (c) 1982, 2013, oracle.  All rights reserved.
 
Connected to a idle instance.
&NBSP
sql> startup pfile= '/tmp/pfile.txt '
ORACLE instance started.
&NBSP
Total System Global area 2505338880 bytes
Fixed size                   2291472 bytes
Variable size              973080816 bytes
Database buffers          1526726656 bytes
Redo buffers                 3239936 bytes
Database mounted.
Database opened.


This recovery also proves that Oracle 12C does have an increasing number of users in use.






Contact: Mobile Phone (13429648788) QQ (107644445)
Link:http://www.xifenfei.com/5597.html
Author: Xi-FEI


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.