About Oracle Data Block Damaged Solution, Related Link URLs in: http://blog.9cbs.net/dlinger/archive/2004/08/24/83911.aspxhttp://www.itpub.net/showthread.php? Threadid = 201766 & Pagenumber My Experimental Steps: Method 1: D: /> SQLPlus "/ as sysdba"
SQL * Plus: Release 9.0.1.0.1 - Production on Wed Sep 29 13:51:48 2004
(c) Copyright 2001 Oracle Corporation. All Rights Reserved.connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - productionwith The Partitioning OptionjServer Release 9.0.1.1.1 - production
SQL> SELECT NAME FROM V $ datafile;
Name -------------------------------------
D: /oracle/oradata/orcl/system01.dbfd: /oracle/oradata/orcl/undotbs01.dbfd: /oracle/oradata/orcl/indx01.dbfd: /oracle/oradata/orcl/tools01.dbfd: / Oracle / ORADATA /Orcl/Users01.dbfd:/oracle/oradata/orcl/oem_repository.dbfd:/oracle/roadata/orcl/test.dbfd:/oracle/roadata/orcl/test1.dbfd:/oracle/oradata/orcl/rmants.dbfd : /Oracle/oradata/orcl/expert.dbfd: /oracle/oradata/orcl/dbo.dbfd: /oracle/roadata/orcl/nms.dbfd: /oracle/oradata/orcl/wacos.dbfd: / Oracle / ORADATA / ORCL / RBS.DBFD: /ORACLE/Ordata/orcl/ips_usg_data.dbfd: /oracle/oradata/orcl/ipas_usg_idx.dbfd: /oracle/oradata/orcl/dsf.dbf
17 rows selected.
SQL> CREATE TABLESPACE BLOCK DATAFILE 'D: /Oracle/oradata/orcl/block.dbf' size 2m;
TableSpace created.
SQL> CREATE User Chenfeng Identified by Chenfeng Default TableSpace ShockTemporary TableSpace Temp;
User created.
SQL> ALTER USER CHENFENG Quota Unlimited on Block;
User altered.
SQL> Grant DBA to Chenfeng;
Grant succeeded.
SQL> Connect Chenfeng / ChenfengConnected.
SQL> CREATE TABLE T AS SELECT * FROM DBA_USERS;
Table created.
SQL> INSERT INTO T SELECT * FROM T;
24 rows created.
SQL> /
48 rows created.
SQL> / 96 rows created.
SQL> /
192 rows created.
SQL> /
384 rows create.
SQL> /
768 rows created.
SQL> /
1536 rows created.
SQL> /
3072 rows created.
SQL> / INSERT INTO T SELECT * FROM T * Error At line 1: ORA-01653: Unable to extend Table Chenfeng.t by 256 in TableSpace Block
SQL> commit;
COMMIT COMPLETE.
SQL> ALTER System Checkpoint;
SYSTEM altered.
SQL> SELECT Count (*) from T;
COUNT (*) ---------- 6144
SQL> CREATE INDEX i_USERNAME ON T (UserName);
Index created.
SQL> Connect / as sysdbaconnected.sql> Shutdown ImmediateDatabase Closed.Database DISMOUNTED.Oracle Instance Shut Down.
Turn off the database After editing the Block.dbf file with UldredIt, change several characters to simulate block damage.
SQL> conn / as sysdbaconnected to an idle instance.sql> StartUpoPoracle Instance Started.
Total System Global Area 114061244 bytesFixed Size 282556 bytesVariable Size 79691776 bytesDatabase Buffers 33554432 bytesRedo Buffers 532480 bytesDatabase mounted.Database opened.SQL> connect chenfeng / chenfengConnected.SQL> select count (*) from t; select count (*) from t * ERROR at Line 1: ORA-01578: Oracle Data Block Corrupted (File # 18, Block # 18) ORA-01110: Data File 18: 'D: /oracle/oradata/orcl/block.dbf'
SQL> Show parameter db_block_size
Name Type Value --------------------------------- ----- DB_BLOCK_SIZE INTEGER 4096
SQL> Host uses the DBV command to test bad blocks:
D: /> dbv file = d: /oracle/oradata/orcl/block.dbf blocksize = 4096
DBVERIFY: Release 9.0.1.1.1 - Production on Wed Sep 29 15:30:51 2004
(c) CopyRight 2001 Oracle Corporation. All Rights Reserved.
DBVERIFY - Verification starting: FILE = D: /ORACLE/ORADATA/ORCL/BLOCK.DBFPage 16 is marked corrupt *** Corrupt block relative dba: 0x04800010 (file 18, block 16) Bad check value found during dbv: Data in bad block - type: 30 format: 2 rdba: 0x04800010 last change scn: 0x0000.00227604 seq: 0x1 flg: 0x04 consistency value in tail: 0x76041e01 check value in block header: 0xe4ce, computed block checksum: 0x1000 spare1: 0x0, spare2: 0x0, spare3: 0x0 *** Page 18 is marked corrupt *** Corrupt block relative dba: 0x04800012 (file 18, block 18) Bad check value found during dbv: Data in bad block - type: 6 format: 2 rdba: 0x04800012 last change SCN: 0x0000.00227646 SEQ: 0x2 flg: 0x04 consistency value in tail: 0x76460602 check value in block header: 0xAd38, computed block checksum: 0x4870 spare1: 0x0, spare2: 0x0, spare3: 0x0 ***
Page 45 is marked corrupt *** Corrupt block relative dba: 0x0480002d (file 18, block 45) Bad check value found during dbv: Data in bad block - type: 6 format: 2 rdba: 0x0480002d last change scn: 0x0000.0022769f seq : 0x1 flg: 0x06 consistency value in tail: 0x769f0601 check value in block header: 0x4af9, computed block checksum: 0x2005 spare1: 0x0, spare2: 0x0, spare3: 0x0 ***
Page 95 is marked corrupt *** Corrupt block relative dba: 0x0480005f (file 18, block 95) Bad check value found during dbv: Data in bad block - type: 6 format: 2 rdba: 0x0480005f last change scn: 0x0000.0022769f seq : 0x1 FLG: 0x06 CONSISITENCY VALUE IN TAIL: 0x769F0601 Check Value in Block Header: 0xB609, Compute Block Checksum: 0xc75e spare1: 0x0, spare2: 0x0, spare3: 0x0 ***
Page 97 is marked corrupt *** Corrupt block relative dba: 0x04800061 (file 18, block 97) Bad check value found during dbv: Data in bad block - type: 6 format: 2 rdba: 0x04800061 last change scn: 0x0000.0022769f seq : 0x1 flg: 0x06 consistency value in tail: 0x769f0601 check value in block header: 0x6e6a, computed block checksum: 0x9b20 spare1: 0x0, spare2: 0x0, spare3: 0x0 *** Page 203 is marked corrupt *** Corrupt block relative dba : 0x048000cb (file 18, block 203) Bad check value found during dbv: Data in bad block - type: 6 format: 2 rdba: 0x048000cb last change scn: 0x0000.0022769b seq: 0x28 flg: 0x04 consistency value in tail: 0x769b0628 check Value in block header: 0xeb1d, computed block checksum: 0x7520 spare1: 0x0, spare2: 0x0, spare3: 0x0 ***
Page 243 is marked corrupt *** Corrupt block relative dba: 0x048000f3 (file 18, block 243) Bad check value found during dbv: Data in bad block - type: 6 format: 2 rdba: 0x048000f3 last change scn: 0x0000.0022769b seq : 0x28 FLG: 0x04 Consistency Value in Tail: 0x769b0628 Check Value in Block Header: 0x1174, Computed Block Chem: 0x110 Spare1: 0x0, Spare2: 0x0, Spare3: 0x0 ***
Page 469 is marked corrupt *** Corrupt block relative dba: 0x048001d5 (file 18, block 469) Bad check value found during dbv: Data in bad block - type: 0 format: 2 rdba: 0x000001d5 last change scn: 0x0000.00000000 seq : 0x1 FLG: 0x05 Consistency Value in Tail: 0x00000001 Check Value In Block Header: 0x6d5, Compute Block CHECKSUM: 0X1000 SPARE1: 0x0, Spare2: 0x0, Spare3: 0x0 ***
DBVERIFY - VERIFICATION COMPLETE
Total Pages Examined: 512Total Pages Processed (Data): 249Total Pages Failing (Data): 0Total Pages Processed (Index): 35Total Pages Failing (Index): 0Total Pages Processed (Other): 17Total Pages Processed (Seg): 0Total Pages Failing ( SEG): 0Total Pages Empty Corrupt: 8Total Pages Influx: 0 This time exported data is not allowed: d: /> exp Chenfeng / chenfeng file = t.dmp tables = t
Export: Release 9.0.1.1.1 - Production on Wed Sep 29 15:34:15 2004
(c) CopyRight 2001 Oracle Corporation. All Rights Reserved.
Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - ProductionWith the Partitioning optionJServer Release 9.0.1.1.1 - ProductionExport done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character setserver uses ZHS16GBK character set (possible charset conversion)
About To Export Specified Tables Via Conventional Path ..... Exporting Table Texp-00056: Oracle Error 1578 Encounteredora-01578: Oracle Data Block Corrupted (File # 18, Block # 18) ORA-01110: Data File 18: D: /Oracle/oradata/orcl/block.dbf'Export Terminated SuccessFully with Warnings.
Check the damaged object, use the following SQL: SQL> SELECT TABLESPACE_NAME, Segment_Type, Owner, Segment_NameFrom DBA_EXTENTS WHERE File_ID = 18 and 18 Between Block_ID and Block_ID blocks - 1;
TABLESPACE_NAME Segment_Type Owner ---------------------------------------------------------------------------------------------------------------------------------------- - ---------------------------
Segment_name ----------------------------- BLOCK TABLE CHENFENGT
Set internal events to make EXPs to skip these corrupted blocks:
SQL> ALTER System Set Events = '10231 Trace Name Context ForeVer, Level 10';
SYSTEM altered.
SQL> HostMicrosoft Windows 2000 [Version 5.00.2195] (C) Copyright 1985-2000 Microsoft Corp. Remove Try: D: /> Exp Chenfeng / Chenfeng File = T.dmp Tables = T
Export: Release 9.0.1.1.1 - Production on Wed Sep 29 15:47:17 2004
(c) CopyRight 2001 Oracle Corporation. All Rights Reserved.
Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - ProductionWith the Partitioning optionJServer Release 9.0.1.1.1 - ProductionExport done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character setserver uses ZHS16GBK character set (possible charset conversion)
About to Export Specified Tables Via Conventional Path ..... Exporting Table T 6004 Rows ExportedExp-00091: Exporting Questions.
The result exports 6004 row data. D: /> EXIT
SQL> ALTER System Set Events = '10231 Trace Name Context Off'; (Close Setting Event)
SYSTEM altered.
SQL> Connect Chenfeng / ChenfengConnected.sql> DROP TABLE T;
Table Dropped.
SQL> HostMicrosoft Windows 2000 [Version 5.00.2195] (C) Copyright 1985-2000 Microsoft Corp.
D: /> omp chenfeng / chenfeng file = t.dmp tables = t
Import: Release 9.0.1.1.1 - Production on Wed Sep 29 15:50:53 2004
(c) CopyRight 2001 Oracle Corporation. All Rights Reserved.
Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - Productionwith The Partitioning OptionjServer Release 9.0.1.1.1 - Production
Export file created by EXPORT:. V09.00.01 via conventional pathimport done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character setimport server uses ZHS16GBK character set (possible charset conversion) importing CHENFENG's objects into CHENFENG importing table "T" 6004 rows importedImport terminated successfully.. WITHOUT WARNINGS.SQL> SELECT Count (*) from T;
COUNT (*) ---------- 6004 results show that the 6144 - 6004 = 110 line data is lost. Method 2 (DBMS_REPAIR): D: /> SQLPLUS "/ as sysdba"
SQL * Plus: Release 9.0.1.0.1 - Production on Wed Sep 29:06:37 2004
(c) CopyRight 2001 Oracle Corporation. All Rights Reserved.
Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.1 - Productionwith The Partitioning OptionjServer Release 9.0.1.1.1 - Production
SQL> connection chenfeng / chenfeng
Connected.
SQL> SELECT Count (*) from T;
COUNT (*) ---------- 6004
SQL> Shutdown ImmediateDatabase CLOSED.DATABASE DISMOUNTED. ORACLE Instance Shut Down.
Use UltraEdit to edit block.dbf, modify several character simulation blocks
SQL> StartupoPoracle Instance Started.
Total System Global Area 114061244 Bytesfixed Size 282556 Bytesvariable Size 79691776 Bytesdatabase Bytesdatabase 532480 Bytesdatabase mounted.Database.
SQL> Select Count (*) from T; Select Count (*) from T * Error At Line 1: ORA-01578: Oracle Data Block Corrupted (File # 18, Block # 152) ORA-01110: Data File 18: D : /Oracle/oradata/orcl/block.dbf '
D: /> dbv file = d: /oracle/oradata/orcl/block.dbf blocksize = 4096
DBVERIFY: Release 9.0.1.1.1 - Production On Wed Sep 29 16:41:40 2004
(C) Copyright 2001 Oracle Corporation All rights reserved.DBVERIFY - Verification starting:. FILE = D: /ORACLE/ORADATA/ORCL/BLOCK.DBFPage 2 is marked corrupt *** Corrupt block relative dba: 0x04800002 (file 18, block 2 ) Bad check value found during dbv: Data in bad block - type: 29 format: 2 rdba: 0x04800002 last change scn: 0x0000.0022819b seq: 0x2 flg: 0x04 consistency value in tail: 0x819b1d02 check value in block header: 0x1f08, computed Block Checksum: 0x7000 spare1: 0x0, spare2: 0x0, spare3: 0x0 ***
Page 4 is marked corrupt *** Corrupt block relative dba: 0x04800004 (file 18, block 4) Bad check value found during dbv: Data in bad block - type: 30 format: 2 rdba: 0x04800004 last change scn: 0x0000.002275f8 seq : 0x1 flg: 0x04 Consistency Value in Tail: 0x75f81e01 Check Value in Block Header: 0xE4BC, Compute BlockSum: 0x8000 Spare1: 0x0, Spare2: 0x0, Spare3: 0x0 ***
Page 16 is marked corrupt *** Corrupt block relative dba: 0x04800010 (file 18, block 16) Bad check value found during dbv: Data in bad block - type: 30 format: 2 rdba: 0x04800010 last change scn: 0x0000.00227604 seq : 0x1 FLG: 0x04 Consistency Value in Tail: 0x76041e01 Check Value In Block Header: 0xE4CE, Computed Blocksum: 0x1000 spare1: 0x0, spare2: 0x0, spare3: 0x0 ***
Page 152 is marked corrupt *** Corrupt block relative dba: 0x04800098 (file 18, block 152) Bad check value found during dbv: Data in bad block - type: 6 format: 2 rdba: 0x04800098 last change scn: 0x0000.00227fae seq : 0x1 FLG: 0x06 Consistency Value in Tail: 0x7fae0601 Check Value in Block Header: 0xc082, Compute Block Checksum: 0x2f Spare1: 0x0, Spare2: 0x0, Spare3: 0x0 ***
Page 154 is marked corrupt *** Corrupt block relative dba: 0x0480009a (file 18, block 154) Bad check value found during dbv: Data in bad block - type: 6 format: 2 rdba: 0x0480009a last change scn: 0x0000.00227fae seq : 0x1 flg: 0x06 consistency value in tail: 0x7fae0601 check value in block header: 0x507c, computed block checksum: 0x7000 spare1: 0x0, spare2: 0x0, spare3: 0x0 *** Page 261 is marked corrupt *** Corrupt block relative dba : 0x04800105 (file 18, block 261) Bad check value found during dbv: Data in bad block - type: 6 format: 2 rdba: 0x04800105 last change scn: 0x0000.0022769b seq: 0x28 flg: 0x04 consistency value in tail: 0x769b0628 check Value in Block Header: 0x117c, Compute Block Checksum: 0x4050 spare1: 0x0, spare2: 0x0, spare3: 0x0 ***
Page 396 is marked corrupt *** Corrupt block relative dba: 0x0480018c (file 18, block 396) Bad check value found during dbv: Data in bad block - type: 0 format: 2 rdba: 0x0000018c last change scn: 0x0000.00000000 seq : 0x1 FLG: 0x05 Consistency Value in Tail: 0x00000001 Check Value in Block Header: 0x68c, Compute Block Checksum: 0x400 Spare1: 0x0, Spare2: 0x0, SPARE3: 0x0 ***
Page 469 is marked corrupt *** Corrupt block relative dba: 0x048001d5 (file 18, block 469) Bad check value found during dbv: Data in bad block - type: 0 format: 2 rdba: 0x000001d5 last change scn: 0x0000.00000000 seq : 0x1 FLG: 0x05 Consistency Value in Tail: 0x00000001 Check Value In Block Header: 0x6d5, Compute Block CHECKSUM: 0X1000 SPARE1: 0x0, Spare2: 0x0, Spare3: 0x0 ***
DBVERIFY - VERIFICATION COMPLETE
Total Pages Examined: 512Total Pages Processed (Data): 182Total Pages Failing (Data): 0Total Pages Processed (Index): 103Total Pages Failing (Index): 0Total Pages Processed (Other): 17Total Pages Processed (Seg): 0Total Pages Failing ( SEG): 0Total Pages Empty: 202 Total Pages Marked Corrupt: 8Total Pages Influx: 0 Use the DBMS_REPAIR package 1. Create a management table: SQL> Connect / as sysdbaconnected.
Create a REPAIR table:
SQL> BEGINDBMS_REPAIR.ADMIN_TABLES (TABLE_NAME => 'REPAIR_TABLE', TABLE_TYPE => dbms_repair.repair_table, ACTION => dbms_repair.create_action, TABLESPACE => 'USERS'); END; /
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 varcha2 (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
Create an ORPHAN KEY table:
SQL> BEGINDBMS_REPAIR.ADMIN_TABLES (TABLE_NAME => 'ORPHAN_KEY_TABLE', TABLE_TYPE => dbms_repair.orphan_table, ACTION => dbms_repair.create_action, TABLESPACE => 'USERS'); END; / 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
2. check for bad blocks: dbms_repair.check_object SQL> SET SERVEROUTPUT ON DECLARE num_corrupt INT; BEGIN num_corrupt: = 0; DBMS_REPAIR.CHECK_OBJECT (SCHEMA_NAME => 'CHENFENG', OBJECT_NAME => 'T', REPAIR_TABLE_NAME => 'REPAIR_TABLE', CORRUPT_COUNT => Num_corrupt); dbms_output.put_line ('number corrupt:' || to_char (num ax)); end; /
. PL / SQL procedure successfully completed with a view to create block REPAIR_TABLE impairment information: SQL> SELECT object_name, relative_file_id, block_id, marked_corrupt, corrupt_description, repair_description, CHECK_TIMESTAMP from repair_table; results can be seen damaged block information, where the information obtained And we can use DBV as seen. 3. Positioning bad blocks: dbms_repair.fix_corrupt_blocks
SQL> declare cc number; begin dbms_repair.fix_corrupt_blocks (schema_name => 'CHENFENG', object_name => 'T', fix_count => cc); dbms_output.put_line (a => to_char (cc)); end; / PL / SQL Procedure successfully completed.
4. Skip bad blocks: Although we have a bad block in front, if we visit Table:
SQL> SELECT Count (*) from chenfeng.t; select count (*) from chenfeng.t * error at line 1: ORA-01578: Oracle Data Block Corrupted (File # 18, Block # 152) ORA-01110: Data file 18: 'D: /oracle/oradata/orcl/block.dbf' still gets an error message. Here you need to skip the bad blocks with Skip_Corrupt_Blocks:
SQL> EXEC DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (Schema_Name => 'chenfeng', Object_name => 'T', Flags => 1); PL / SQL Procedure SuccessFully Completed.
SQL> SELECT Count (*) from chenfeng.t;
COUNT (*) ---------- 5926
Lost 6004-5926 = 78 row data.
5. Handle the invalid key value on Index: dump_orphan_keys
SQL> declarecc number; begindbms_repair.dump_orphan_keys (schema_name => 'CHENFENG', object_name => 'I_USERNAME', object_type => 2, repair_table_name => 'REPAIR_TABLE', orphan_table_name => 'ORPHAN_TABLE', key_count => CC); end; / PL / SQL Procedure SuccessFully Completed.
SQL> SELECT Count (*) from orphan_table;
COUNT (*) ---------- 78
The number of data lines that have been seen above is consistent.
Consider whether rebuild index is required according to this result.
6. Rebuild Freelist: Rebuild_Freelists
SQL> EXEC DBMS_REPAIR.REBUILD_FREELISTS (Schema_Name => 'chenfeng', Object_name => 'T');
PL / SQL Procedure SuccessFully Completed. Appendix (provided a classic case): Method for repairing data blocks in the alpha server
There are two ways to install the database using the Alpha server sharing disk: one is to use the shared disk as a bare device mode, the database is installed in an OPS method; another way is to make the shared disk into a file system in one server On the sharing disk, when you switch the database, you will first turn off the database, remove the shared disk from this server, and then start it on another server and start the database again. In the case of dual switching, unexpected power failure, or other cases, the shared discs are sometimes occurring, and the mount is not possible, you need to use the FSCK to fix the shared disk. After the repair is completed, the phenomenon of "data block corruption, unable to start the database" occurs during the database startup, at this time, the error can be detected and repaired according to different data blocks. Here is three ways to use Oracle8i to repair damage to damaged data blocks. First, the data block is damaged, the error code is ORA-01578
ORA-1115 I / O Error Reading Block
Usually followed by ORA-737x errors and operating system errors (such as error number in UNIX)
cause:
1. Hardware problem (disk controller problem or disk problem)
2. Physical grade data block is damaged (usually caused by the previous cause)
3. When processing a giant file, followed by error code ORA-7371
Determining the cause of the fault and recovery:
1. View the occurrence of other ORA-1115 errors in the alert.log file:
1) If you point to a file of different disks, the problem of disk controller, view V $ datafile, and which files are located under the controller and go to the second step.
2) If you point to different files of the same disk, it is a problem with the disk and goes to the second step.
3) If you point to the same file, perform the following statement to find the file name:
Select segment_name, segment_type from dba_extents where
FILE_ID =
Block_id blocks-1;
Where the file number is indicated in ORA-1115, if the query continues to point to a table or an index,
Then rebuild them.
2. If the file is System table space, or in the noarchiveLog mode, turn off the database, go to the fourth step.
3. If the database is in ArchiveLog mode, the database should still be turned off. If the database cannot be closed, the corresponding data file is offline: ALTER DATABASE DATAFILE 'file name' OFFLINE;
4. Try to copy the data file to another disk.
5. If the copy fails, the file will be lost.
6. Startup mount;
7. Rename the data file as a file name that successfully copied to another disk:
ALTER DATABASE RENAME FILE 'Old Path File Name' To 'New Path File Name'
8. ALTER DATABASE OPEN
9. Recover DataFile file name;
ALTER DATABASE DATAFILE 'file name' online;
Second, the rollback segment needs to be recovered
If the rollover is in the NEED Recovery status, you need to perform the following steps to recover: 1. See all online tablespaces and data files
2. Add Event = "10015 Trace Name Context Forever, Level 10" in the init.ora file, which will generate a tracking file containing information about the transaction and rollback.
3. Close and reopen the database.
4. View the trace file, there should be Error Recovery TX (#, #) Object # .tx (#, #), pointing out transaction information, where Object # is the same as Object_ID in sys.dba_objects.
5. Use the following query to find out the object being recovered:
SELECT OWNER, Object_name, Object_Type, Status from DBA_OBJECTS WHERE
Object_id =
6. This object must be deleted to release the rollback block.
Third, the common method of detecting and repairing damaged blocks
(1) Use the initialization parameter db_block_checking with db_block_checksum.
DB_BLOCK_CHECKING is logically verified when the block changes. The occurrence of 10210 and 10211 errors will be prevented.
(2) Use the DBMS_REPAIR package, generated by dbmsrpr.sql and prvtrprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprprise generated information in a particular table.
1. DBMS_REPAIR.ADMIN_TABLES is used to create a table with deleting storage corruption blocks. Where Table_Type is: REPAIR_TABLE, OPHAN_TABLE (index); action is: create_action, purge_action (deleted data), DROP_ACTION. example:
DBMS_REPAIR.ADMIN_TABLES ('REPAIR_TABLE', DBMS_REPAIR.REPAIR_TABLE, DBMS_REPAIR.CREATE_ACITION, 'TEMP_DATA');
2. DBMS_REPAIR.CHECK_OBJECT checkscripts, index, block damage in the partition. Where Object_Type is: Table_Object (Table), INDEX_Object (index), repair_table_name (for the table for storing damage to block information). example:
DBMS_REPAIR.CHECK_OBJECT ('ORATRAIN', 'LOCATION', CORRUPT_COUNT =>: CC);
3. Use the following statement to query the block corruption information:
Select Object_name, relative_file_no, block_id, marked_corrupt, corrupt_description, repair_description from repair_table;
4. The block mark is damaged: dbms_repair.fix_corrupt_blocks ('ORATRAIN', 'LOCATION', FIX_COUNT =>: Fc);
5. Skip damaged blocks: dbms_repair.skip_corrupt_blocks ('oratrain', 'Locations'); where Object_Type is: Table_Object (Table), Cluster_Object (index).
6. Reconstruction of damaged idle lists using rebuild_freelists: dbms_repair.rebuild_freeelists
7. Use the following methods to find the index of corrugation blocks:
(1) Creating a table with a memory index
(2) DBMS_REPAIR.DUMP_ORPHAN_KEYS ('ORATRAIN', 'LOC_PK',
Orpha_table_name => 'orphan_tab1', key_count =>: kc);
(3) SELECT INDEX_NAME, Count (*) from orphan_key_table where table_name = 'classes' group by index_name;
(4) Reconstruction of indexes with Orpha KEYS
Limit: Index-Organized Tables and LOB INDEXES, DUMP_ORPHAN_KEYS cannot operate with Bitmap with Function-Based Indexes.
(3) Using SQL Command Analyze Table | INDEX ... VALIDATE STRUCTURE
UTLVALID.SQL. Create an Invalid_Rows table with corrupted block information, Analyze Table Validate Structure Cascade simultaneously checks the table and index.
(4) Using dbverify
DBVERIFY is an external tool, so it has little impact on the database. It can be used to verify the integrity of the backup file before copying the backup file back to the original position, and position the data block corruption. The command is as follows:
DBV /USERS/DB00/U03/Data01.dbf start = 1 end = 500 logfile = dbv.log
