CN110764943A

CN110764943A - Data processing method and device for Oracle database

Info

Publication number: CN110764943A
Application number: CN201910999139.4A
Authority: CN
Inventors: 崔华
Original assignee: China Travelsky Technology Co Ltd
Current assignee: China Travelsky Technology Co Ltd
Priority date: 2019-10-21
Filing date: 2019-10-21
Publication date: 2020-02-07
Anticipated expiration: 2039-10-21
Also published as: CN110764943B

Abstract

The application provides a data processing method of an Oracle database, which comprises the following steps: obtaining the block address of a bad block in a target table; storing the block address of the bad block in the target table to a pre-established transfer table; storing the undamaged data in the bad blocks of the target table to a pre-established recovery table; covering each bad block in the target table by using a blank data block, modifying the block address of each blank data block in the target table into the block address of the bad block covered by each blank data block, modifying the data object number of each blank data block in the target table into the data object number of the target table, and modifying the check value of each blank data block in the target table; writing the data stored in the recovery table into the target table; the index of the target table is reconstructed. Based on the method provided by the application, under the condition that the Oracle database has no backup, the data readability of the table with the bad blocks in the Oracle database can be recovered, so that the DDL operation and the business operation of the Oracle database can be normally executed.

Description

Data processing method and device for Oracle database

Technical Field

The application relates to the technical field of data processing, in particular to a data processing method and device for an Oracle database.

Background

The Oracle database is the most widely used database management system in the world at present, and has complete data management functions.

The occurrence of bad blocks in the Oracle database refers to: the phenomenon of content chaos occurs in the data blocks of the Oracle database. The main reasons for bad blocks in the Oracle database include: in the process of processing the data block, the Oracle database firstly reads the data block into the memory, and after the processing is finished, the data block is written back to the disk by the specific process. In this process, if a memory failure, a disk failure or a calculation error occurs, the content of the data block may be confused. In addition, abnormal shutdown, abnormal power failure of the Oracle database, or Bug of the Oracle database itself may cause content confusion of some data blocks.

The Oracle database needs to read the contents of a plurality of data blocks in the process of executing the DDL operation and the business operation. If the data block needing to be read contains a bad block, the Oracle database cannot read the content of the bad block, so that the DDL operation or the business operation is interrupted to be executed, the process of the Oracle database is wrongly reported, even the process is suspended, and the Oracle database instance is abnormal under extreme conditions.

The conventional treatment method at present is as follows: and when the Oracle database has a bad block, repairing the bad block by using the backup data of the Oracle database. However, in practical applications, many Oracle databases are not backed up or are backed up inefficiently, which results in that all DDL operations and business operations related to bad blocks cannot be executed normally when the Oracle databases have bad blocks.

Therefore, how to recover the data readability of the table with the bad blocks in the Oracle database under the condition that the Oracle database has no backup, so that the DDL operation and the business operation of the Oracle database can be normally executed, is a problem to be solved by those skilled in the art.

Disclosure of Invention

In view of this, the present application provides a data processing method and apparatus for an Oracle database, so as to recover the data readability of a table with a bad block in the Oracle database under the condition that the Oracle database has no backup, thereby enabling the DDL operation and the business operation of the Oracle database to be performed normally.

In order to achieve the above purpose, the embodiments of the present application provide the following technical solutions:

the application provides a data processing method of an Oracle database, which comprises the following steps:

obtaining the block address of a bad block in a target table;

storing the block address of the bad block in the target table to a pre-established transfer table;

storing the undamaged data in the bad blocks of the target table to a pre-established recovery table;

covering each bad block in the target table by using a blank data block based on the block address of the bad block stored in the transfer table, modifying the block address of each blank data block in the target table into the block address of the bad block covered by the blank data block, modifying the data object number of each blank data block in the target table into the data object number of the target table, and modifying the check value of each blank data block in the target table;

writing the data stored in the recovery table into the target table;

reconstructing the index of the target table.

Optionally, the process of constructing the empty data block includes:

creating a temporary table, wherein the table structure of the temporary table is consistent with the table structure of the target table;

inserting a record in the temporary table;

and deleting the record to obtain a null data block.

Optionally, in the above method, the obtaining the block address of the bad block in the target table includes:

respectively carrying out bad block detection on a plurality of data blocks in the target table so as to respectively determine whether the plurality of data blocks are bad blocks;

the block address of the data block determined to be a bad block is obtained.

Optionally, in the method, performing bad block detection on any data block in the target table to determine whether the data block is a bad block includes:

reading data rows in the data blocks one by executing an SQL query instruction; wherein one of the SQL query instructions carries a data line number of one data line in the data block;

and if any one data line in the data block fails to be read, determining that the data block is a bad block, and if all the data lines in the data block are successfully read, determining that the data block is normal.

Optionally, in the method, the storing the uncorrupted data in the bad block of the target table to a pre-established recovery table includes:

reading the data rows in the bad blocks one by executing SQL query instructions, wherein one SQL query instruction carries the data row number of one data row in the bad blocks;

and storing the read data to a pre-established recovery table.

In another aspect, the present application provides a data processing apparatus for an Oracle database, the apparatus comprising:

the block address acquisition unit is used for acquiring the block address of the bad block in the target table;

the block address processing unit is used for storing the block address of the bad block in the target table to a pre-established transfer table;

the bad block data processing unit is used for storing the undamaged data in the bad blocks of the target table to a pre-established recovery table;

the data block processing unit is used for covering each bad block in the target table by using a blank data block based on the block address of the bad block stored in the transfer table, modifying the block address of each blank data block in the target table into the block address of the bad block covered by the blank data block, modifying the data object number of each blank data block in the target table into the data object number of the target table, and modifying the check value of each blank data block in the target table;

a bad block data recovery unit, configured to write the data stored in the recovery table into the target table;

and the index building unit is used for rebuilding the index of the target table.

Optionally, on the basis of the data processing apparatus, the data processing apparatus further includes a null data block construction unit;

the process of constructing the empty data block by the empty data block constructing unit specifically includes: creating a temporary table, wherein the table structure of the temporary table is consistent with the table structure of the target table; inserting a record in the temporary table; and deleting the record to obtain a null data block.

Optionally, in the data processing apparatus, the block address obtaining unit includes:

a bad block detection subunit, configured to perform bad block detection on the multiple data blocks in the target table, respectively, so as to determine whether the multiple data blocks are bad blocks, respectively;

a block address obtaining sub-unit for obtaining a block address of the data block determined as the bad block.

Optionally, in the data processing apparatus, the bad block detecting subunit performs bad block detection on any data block in the target table to determine whether the data block is a bad block, where the method specifically includes:

reading data rows in the data blocks one by executing an SQL query instruction; wherein one of the SQL query instructions carries a data line number of one data line in the data block; and if any one data line in the data block fails to be read, determining that the data block is a bad block, and if all the data lines in the data block are successfully read, determining that the data block is normal.

Optionally, in the data processing apparatus, the bad block data processing unit is specifically configured to:

reading the data rows in the bad blocks one by executing SQL query instructions, wherein one SQL query instruction carries the data row number of one data row in the bad blocks; and storing the read data to a pre-established recovery table.

It can be seen that:

the data processing method of the Oracle database comprises the steps of firstly obtaining block addresses of bad blocks in a target table, storing the block addresses of the bad blocks in a pre-established transfer table, storing data which are not damaged in the bad blocks in a pre-established recovery table, covering each bad block in the target table by using the empty data blocks, modifying the block addresses of the empty data blocks into the block addresses of the bad blocks covered by the empty data blocks, modifying data object numbers of the empty data blocks into data object numbers of the target table, modifying check values of the empty data blocks, writing the data stored in the recovery table into the target table, and reconstructing indexes of the target table.

Based on the data processing method disclosed by the application, the bad blocks in the target table are eliminated, the data which are not damaged in each bad block are reinserted into the target table, the data in the bad blocks are recovered to the maximum extent, and the data readability of the table with the bad blocks in the Oracle database can be recovered under the condition that the Oracle database has no backup, so that the DDL operation and the business operation of the Oracle database can be normally executed. Moreover, the entire data processing process is performed online without shutdown (neither application nor Oracle database is stopped). In addition, the data processing method disclosed by the application only performs data migration on the bad blocks in the target table, that is, the data which is not damaged in the bad blocks is stored in the recovery table, and the data in the recovery table is written into the target table, without migrating the data of the whole target table, the time required by the whole data processing process mainly depends on the number of the bad blocks in the target table and the time for reconstructing the index of the target table, and the recovery speed is high.

Drawings

In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings needed to be used in the description of the embodiments or the prior art will be briefly introduced below, it is obvious that the drawings in the following description are only embodiments of the present application, and for those skilled in the art, other drawings can be obtained according to the provided drawings without creative efforts.

FIG. 1 is a flow chart of a data processing method of an Oracle database disclosed in the present application;

FIG. 2 is a flow chart of a method of constructing a null data block as disclosed herein;

FIG. 3 is a schematic illustration of a data processing method disclosed herein;

fig. 4 is a schematic structural diagram of a data processing apparatus of an Oracle database disclosed in the present application.

Detailed Description

The technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the drawings in the embodiments of the present application, and it is obvious that the described embodiments are only a part of the embodiments of the present application, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present application.

In this application, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising an … …" does not exclude the presence of other identical elements in a process, method, article, or apparatus that comprises the element.

The application is operational with numerous general purpose or special purpose computing device environments or configurations. For example: personal computers, server computers, hand-held or portable devices, tablet-type devices, multiprocessor apparatus, distributed computing environments that include any of the above apparatus or devices, and the like.

The application provides a data processing method and device of an Oracle database, so that the data readability of a table with bad blocks in the Oracle database is recovered under the condition that the Oracle database is free of backup, and the DDL operation and the business operation of the Oracle database can be normally executed.

The following explains the terms appearing in the present application:

oracle database: is a commercial relational database.

DDL: data definition languages used to create, modify and delete various objects in a database-tables, views, indexes, synonyms, clusters, etc.

Tablespace: tablespaces, which are logical concepts in an Oracle database, an Oracle database can have one or more tablespaces, and a tablespace corresponds to one or more physical data files.

Segment: a segment is a collection of space used by an Oracle database object. A paragraph is a logical concept in an Oracle database.

Table: a table is one of Segment types in an Oracle database, and is also commonly referred to as a table Segment (in the Oracle database, besides the table Segment, an index Segment, a rollback Segment, a temporary Segment, and the like).

Block: and the data block is the minimum logical unit for storing data in the Oracle database, and is also a logical concept in the Oracle database.

RDBA: the block address is the physical storage address of the data block in the Oracle database.

data object id: the data object id is a unique identifier used by the Oracle database to identify the database object. The data object id indicates the physical id. Objects that do not have a data object id if they do not have a physical attribute, such as process, function, package, data type, dblink, mv definition, view definition, scratch table, partition table definition, etc., are all not associated with a segment, and thus their data object ids are all empty.

Rowid: and a data line number and rowid are physical storage addresses of data lines in the Oracle database, and the data line number and rowid can be used for rapidly positioning and accessing a certain row of data by the Oracle.

Checksum: checking a value, wherein each data block in the Oracle database records a checksum value, and whenever the content in the data block is modified, Oracle recalculates and writes a new checksum value. And when the Oracle reads the data block, recalculating a checksum value according to the content of the read data block, comparing the calculated checksum value with the checksum value originally recorded in the data block, and if the calculated checksum value is not equal to the checksum value originally recorded in the data block, the Oracle considers that the current data block is damaged.

And (3) bad block: when the Oracle database reads the data stored in the data block, a series of checks are performed on the content of the data block to verify the validity of the data stored in the data block. If the data block fails these checks, the Oracle database considers the contents of the data block to be corrupted, and the data block is referred to as a bad block in the Oracle database.

online rebuild index: the method for reconstructing the index on line built in the Oracle database can be implemented on line (the application does not need to be stopped, the DML operation of the application on the target table cannot be blocked in the implementation process), and online rebuild indexes can be executed in parallel, so that the index reconstruction speed is greatly improved.

SQL: structured Query Language, Structured Query Language.

Referring to fig. 1, fig. 1 is a flowchart of a data processing method of an Oracle database disclosed in the present application. The method comprises the following steps:

step S101: and obtaining the block address of the bad block in the target table.

An Oracle database usually has a plurality of tables, and the table to be processed containing a bad block is referred to as a target table in the application. One or more bad blocks exist in the target table, and block addresses of all the bad blocks in the target table are obtained.

In implementation, a plurality of data blocks contained in the target table are traversed to determine bad blocks in the target table, and block addresses of the bad blocks are obtained.

Step S102: and storing the block address of the bad block in the target table to a pre-established transfer table.

And pre-establishing a transfer table in an Oracle database, and storing the block address of the bad block in the obtained target table into the transfer table.

Optionally, the table structure of the transfer table includes a column.

The table structure is explained here. The table structure refers to a general name of each column name, each column type and length contained in one table in the Oracle database.

For example, the following SQL statement is executed to create table T1:

SQL>create table t1(c1number,c2varchar2(10))；

Table created

then for table T1, the table structure is: table T1 has two columns with column names c1 and c2, respectively, and column c1 is number in type and has a length that is the default length of the number type column in the Oracle database; column c2 is of the type varchar2, which is 10 in length.

In the present application, the table structure of the pre-established transfer table includes a column for storing the block address of the bad block in the target table.

Step S103: and storing the uncorrupted data in the bad block of the target table to a pre-established recovery table.

The data block includes a plurality of data lines. Accordingly, the bad block also includes a plurality of data lines. A bad block contains a plurality of data lines, typically only a portion of the data lines are corrupted while other data lines are uncorrupted. In the application, undamaged data in a bad block of a target table are read out, and the undamaged data are stored in a pre-established recovery table.

In implementation, the undamaged data in the bad block is read and stored to the recovery table by traversing a plurality of data rows contained in the bad block.

Step S104: and based on the block addresses of the bad blocks stored in the transfer table, covering each bad block in the target table by using the empty data blocks, modifying the block address of each empty data block in the target table into the block address of the bad block covered by the empty data block, modifying the data object number of each empty data block in the target table into the data object number of the target table, and modifying the check value of each empty data block in the target table.

The empty data block is constructed in advance. And covering each bad block in the target table by using an empty data block according to the block address of the bad block stored in the transfer table. Here, it should be noted that, at this time, the block address of the empty data block in the target table is still the original block address of the empty data block, so the block address of each empty data block in the target table is modified, specifically, the block address of the empty data block is modified to the block address of the bad block covered by the empty data block. In addition, the data object number of each empty data block in the target table is modified into the data object number of the target table, and the check value of each empty data block in the target table is modified.

Here, the description is given by way of example:

if the data object number of the target table is data _ object _ id _ t1_ original, the target table contains 3 bad blocks b1, b2 and b3, whose block addresses are rdba _ b1, rdba _ b2 and rdba _ b 3. Each bad block in the target table is overwritten with a null data block, i.e., a bad block with block addresses rdba _ b1, rdba _ b2, and rdba _ b3, respectively, while the block addresses of these 3 null data blocks in the target table are still their home block addresses. The block addresses of the 3 empty data blocks are then modified, with the modified block addresses of the 3 empty data blocks being rdba _ b1, rdba _ b2, and rdba _ b3, respectively. Then, the data object numbers of the 3 null data blocks are modified into data _ object _ id _ t1_ original, and the check values of the 3 null data blocks are calculated.

The logic for calculating the check value of the data block is:

step S105: and writing the data stored in the recovery table into the target table.

Step S106: the index of the target table is reconstructed.

The recovery table stores the uncorrupted data in each bad block of the target table. After step S104 is executed, the data stored in the recovery table is written into the target table, so as to recover the data in the bad block to the maximum extent and rebuild the index of the target table. It should be noted that the internal algorithm of the Oracle database determines which data blocks of the target table the data stored in the recovery table is written to. For example, the Oracle database may write the data in the recovery table into a non-empty data block in the target table that has stored data but still has space remaining.

The data processing method of the Oracle database disclosed by the application comprises the steps of firstly obtaining the block address of a bad block in a target table, storing the block address of the bad block into a pre-established transfer table, storing the undamaged data in each bad block into a pre-established recovery table, covering each bad block in the target table by using an empty data block, modifying the block address of each empty data block into the block address of the bad block covered by the empty data block, modifying the data object number of each empty data block into the data object number of the target table, modifying the check value of each empty data block, writing the data stored in the recovery table into the target table, and reconstructing the index of the target table.

Based on the data processing method disclosed by the application, the bad blocks in the target table are eliminated, the data which are not damaged in each bad block are reinserted into the target table, the data in the bad blocks are recovered to the maximum extent, and the data readability of the table with the bad blocks in the Oracle database can be recovered under the condition that the Oracle database has no backup, so that the DDL operation and the business operation of the Oracle database can be normally executed. Moreover, the entire data processing process is performed online without shutdown (neither application nor Oracle database is stopped). In addition, the data processing method disclosed in the above application only performs data migration on the bad blocks in the target table, that is, the data that is not damaged in the bad blocks is stored in the recovery table, and the data in the recovery table is written into the target table, without migrating the data of the entire target table, the time required by the entire data processing process mainly depends on the number of the bad blocks in the target table and the time for reconstructing the index of the target table, and the recovery speed is fast.

In another embodiment, constructing the null data block using the method shown in fig. 2 includes:

step S201: a temporary table is created.

Wherein the table structure of the temporary table is consistent with the table structure of the target table.

Step S202: a record is inserted in the temporary table.

Step S203: and deleting the record in the temporary table to obtain an empty data block.

It should be noted that only one record is inserted into the temporary table, and after the record is deleted, the temporary table only includes one empty data block.

In the method for constructing the empty data block shown in fig. 2 of the present application, a temporary table consistent with the table structure of the target table is created first, a record is inserted into the temporary table, and then the record is deleted, so that an empty data block can be obtained.

In implementations, the null data block may also be constructed in other ways.

In another embodiment, the block address of the bad block in the target table is obtained by the following scheme:

respectively carrying out bad block detection on a plurality of data blocks in the target table to respectively determine whether the plurality of data blocks in the target table are bad blocks; the block address of the data block determined to be a bad block is obtained.

The method comprises the following steps of carrying out bad block detection on any data block in a target table to determine whether the data block is a bad block, and adopting the following scheme:

reading data rows in the data block one by executing an SQL query instruction; and if any one data line in the data block fails to be read, determining that the data block is a bad block, and if all the data lines in the data block are successfully read, determining that the data block is normal.

Wherein, one SQL query instruction carries the data row number of one data row in the data block.

The block address RDBA and the data line number rowid are explained in detail here.

RDBA refers to the physical memory address of a data block in an Oracle database. Rowid is the physical memory address of a row of data stored in a block of data in an Oracle database.

For example: a record is stored in the 2 nd line in the 100 th data block of the 10 th data file, the RDBA of the data block in which the record is located is "10-100" (representing the 100 th data block of the 10 th file), and the rowid corresponding to the record is "10-100-2" (representing the 2 nd line in the 100 th data block of the 10 th file).

The rowid carried in the SQL query instruction may be determined as follows:

and constructing the rowid by using a method rowid _ create in a dbms _ rowid package built in an Oracle database. The parameters involved are as follows:

DBMS_ROWID.ROWID_CREATE(

rowid_type IN NUMBER,

object_number IN NUMBER,

relative_fno IN NUMBER,

block_number IN NUMBER,

row_number IN NUMBER)

RETURN ROWID；

where rowid _ type is a fixed value of 1. According to the length of each column of the target table, it can be estimated how many rows of a data block of the target table are recorded at most, that is, the number of data rows contained in a data block (default value is 10000). After the number of data lines contained in one data block is determined, the data dictionary dba _ extents of the Oracle database is queried, so that the object _ number, relative _ fno value and block _ number range of the rowid corresponding to the data lines can be determined, and the rowid can be constructed. The object _ number is the object number of the object table, is the unique identifier used by the Oracle database to identify the database object, and represents the logical id of the database object. relative _ fno is the file number of a data file in the Oracle database. block _ number indicates a block number of a data block.

As an embodiment, performing bad block detection on any data block in the target table to determine whether the data block is a bad block includes:

constructing an SQL query instruction, wherein the SQL query instruction carries a data line number of one data line in the data block;

executing the currently constructed SQL query instruction;

if the data line reading fails, determining that the data block is a bad block;

if the data line is successfully read, constructing another SQL query instruction, wherein the SQL query instruction carries the data line number of another data line in the data block, and executing the currently constructed SQL query instruction;

if the data reading fails, determining that the data block is a bad block; and if the data line is successfully read, constructing another SQL query instruction, executing subsequent steps until the data line is unsuccessfully read, determining that the data block is a bad block, or determining that the data block is normal until the data lines in the data block are successfully read.

In another embodiment, the uncorrupted data in the bad block of the target table is stored in a pre-established recovery table, and the following scheme is adopted:

reading data rows in the bad blocks one by executing an SQL query instruction; and storing the read data to a pre-established recovery table.

Wherein, one SQL query instruction carries the data line number of one data line in the bad block.

As an embodiment, storing uncorrupted data in a bad block of a target table to a pre-established recovery table, includes:

constructing an SQL query instruction, wherein the SQL query instruction carries a data line number of a data line in a bad block; executing the currently constructed SQL query instruction; if the data row is successfully read, storing the read data into a recovery table; constructing another SQL query instruction, wherein the SQL query instruction carries the data line number of another data line in the bad block; and executing the currently constructed SQL query instruction until the undamaged data in the current bad block are stored in the recovery table.

And then, repeating the process for other bad blocks in the target table until all the undamaged data in the bad blocks are stored in the recovery table.

It should be noted that, if the data line reading fails, the operation of constructing another SQL query instruction is directly performed.

The following describes a data processing method of the Oracle database disclosed in the present application with reference to fig. 3.

The target is denoted as t1_ original. The pre-constructed transfer table t1_ rdba, temporary table t1_ temp and recovery table t1_ recovery are located in the same Oracle database as the target table t1_ original. The table structure of the temporary table t1_ temp is identical to that of the target table t1_ original, and the table structure of the intermediate table t1_ rdba includes only one column. The transfer table t1_ RDBA is used for storing the RDBA addresses of all the bad blocks in the target table t1_ original, the recovery table t1_ recovery is used for storing uncorrupted data read from each bad block in the target table t1_ original, and the temporary table t1_ temp is used for constructing an empty data block.

It is assumed here that the target table t1_ original has n bad blocks, which are denoted as b1, b2, … bn.

Step 1, creating three tables in a database where a target table t1_ original is located: a transfer table t1_ rdba, a temporary table t1_ temp, and a recovery table t1_ recovery. The table structures of the temporary table t1_ temp and the recovery table t1_ recovery are the same as the table structure of the target table t1_ original, and the table structure of the transfer table t1_ rdba is only one column. The transfer table t1_ RDBA is used to store the RDBA addresses of all bad blocks in the target table t1_ original. The recovery table t1_ recovery is used to store uncorrupted data read from the bad block in the target table t1_ original.

Step 2, insert a record into the temporary table t1_ temp in order for the Oracle database engine to format a data block in the temporary table t1_ temp.

And 3, newly establishing a storage process identification _ corrected _ block in a database where the target table t1_ original is located. The core logic of the identification _ corrected _ block is a two-layer loop, the outer layer loop traverses all data blocks in the target table t1_ original, and the inner layer loop loops a specified number of times (the number of loops is configurable, and is generally configured to be the number of data lines contained in the data block, and the default value is 10000). The outer-layer loop traverses a data block in the target table t1_ original each time, the inner-layer loop calls dbms _ rowd.rowid _ create each time to construct a rowid, then reads the corresponding data line in the table t1_ original according to the rowid, if any error occurs during reading the data line, directly ignores the error and inserts the RDBA address of the data block corresponding to the rowid into the transfer table t1_ RDBA, and then interrupts the inner-layer loop and continues the next outer-layer loop. After the outer layer cycle is completed, all the bad blocks in the target table t1_ original are determined, and the RDBA address of each bad block is stored into the transfer table t1_ RDBA.

And 4, newly building another storage process extract _ rows _ from _ corrected _ block in a database where t1_ original is located. The core logic of the extract _ rows _ from _ copied _ block is a two-layer loop, the outer layer loop traverses all records in the transfer table t1_ RDBA, the inner layer loop is configured for a specified number of times (the number of cycles is configurable and is generally configured as the number of data rows contained in a data block, and the default value is 10000), the outer layer loop traverses a single data block corresponding to the RDBA address recorded in the transfer table t1_ RDBA every time, the inner layer loop calls dbms _ rows _ row _ create every time to construct a row, then inserts the corresponding data row in the target table t1_ original into the recovery table t1_ recovery according to the row, and if any error occurs during reading of the data row, directly ignores the error and continues to the next inner layer loop. After the outer loop is completed, all the uncorrupted data in the bad blocks of the target table t1_ original are stored to the recovery table t1_ recovery.

Step 5, two configuration files of fix _ oracle _ corrupttblock _ configuration.txt and fix _ oracle _ corrupttblock _ control.txt of fix _ oracle _ corrupttblock are prepared.

Configuration file fix _ oracle _ corruptblock _ control. txt contains all data file paths corresponding to the tablespace where target table t1_ original and temporary table t1_ temp are located.

And the configuration file fix _ oracle _ corrupttblock _ config.txt contains the following configuration information:

(1) connecting the user name, password and database instance name of the database containing the target table t1_ original containing n bad blocks (here, the database containing t1_ original);

(2) owner and table name of target table t1_ original containing n bad blocks (here t1_ original);

(3) owner and table name for the transfer table t1_ RDBA (here t1_ RDBA) for storing the RDBA addresses of all bad blocks in the target table t1_ original;

(4) owner and table name for temporary table t1_ temp for covering bad blocks, only one empty data block (here t1_ temp);

(5) the winner and table name of the recovery table t1_ recovery (here t1_ recovery) for storing all uncorrupted data read from bad blocks in the target table t1_ original.

Step 6, execute fix _ oracle _ corrupttblock, first read configuration file fix _ oracle _ corrupttblock _ config. txt, then read the following metadata information from the database where target table t1_ original is connected:

(1) the size of the data block (usually 8192) of the database where the target table t1_ original is located, here denoted as t1_ blocksize;

(2) the data object id of the target table t1_ original, herein denoted as data _ object _ id _ t1_ original;

(3) the names of all indexes on the target table t1_ original and the name of the tablespace in which these indexes are located, for all indexes on the online rebuild target table t1_ original;

(4) the data object id of the temporary table t1_ temp, here denoted data _ object _ id _ t1_ temp;

(5) the rowid of only one record in the temporary table t1_ temp, fix _ oracle _ corrupttblock, will calculate the RDBA address of the record of the line based on the rowid, here denoted RDBA _ t1_ temp.

Step 7, fix _ oracle _ corrupttblock deletes the only record in temporary table t1_ temp, thus forming a well-structured empty data block e1 in temporary table t1_ temp. Then, the fix _ oracle _ corrupttblock reads the data file corresponding to the rdba _ t1_ temp in the fix _ oracle _ corrupttblock _ control.txt, and reads the data block corresponding to the rdba _ t1_ temp, namely the empty data block e1, from the data file by taking t1_ blocksize as a unit.

Step 8, the fix _ oracle _ corrupttblock executes the storage process identification _ corrupted _ block, and then executes the storage process extract _ rows _ from _ corrupted _ block. After the two storage processes are completed, the RDBA addresses of all the bad blocks recorded in the transfer table t1_ RDBA, here denoted as RDBA _ bi (i ═ 1, 2.. and n), are read, and then each RDBA _ bi is traversed.

Sequentially executing the following steps according to the RDBA addresses of all the bad blocks:

a data file corresponding to rdba _ bi in fix _ oracle _ corrupttblock _ control.txt is read, and then a data block corresponding to rdba _ bi is read from the data file in a unit of t1_ blocksize, which is denoted as a data block bi (i ═ 1, 2.. multidot.n). The data block bi is then overwritten with an empty data block e1, the RDBA address of the overwritten data block is then modified from RDBA _ t1_ temp to RDBA _ bi, the data object id of the overwritten data block is then modified from data _ object _ id _ t1_ temp to data _ object _ id _ t1_ original, and the checksum value of the data block is recalculated and modified.

Step 9, fix _ oracle _ corrupttblock executes SQL statement, and inserts all data in recovery table t1_ recovery into original target table t1_ original.

Step 10, fix _ oracle _ corrupttblock parallels all the indexes on the online rebuild target table t1_ original.

After the above step 10 is completed, the bad blocks bi (i ═ 1, 2., n) in the original target table t1_ original are all eliminated, the uncorrupted data in the original bad blocks have been re-inserted into the target table t1_ original, and all the indexes on the table have been re-concatenated, and the DDL operation and the service operation can be performed normally.

The application discloses a data processing method of the Oracle database, and correspondingly, the application also discloses a data processing device of the Oracle database.

Referring to fig. 4, fig. 4 is a schematic structural diagram of a data processing apparatus of an Oracle database disclosed in the present application. The data processing apparatus includes a block address acquisition unit 10, a block address processing unit 20, a bad block data processing unit 30, a data block processing unit 40, a bad block data restoration unit 50, and an index construction unit 60.

And the block address acquisition unit 10 is used for acquiring the block address of the bad block in the target table.

And the block address processing unit 20 is used for storing the block address of the bad block in the target table into a pre-established transfer table.

And the bad block data processing unit 30 is used for storing the uncorrupted data in the bad blocks of the target table to a pre-established recovery table.

And the data block processing unit 40 is used for covering each bad block in the target table by using the empty data block based on the block address of the bad block stored in the transfer table, modifying the block address of each empty data block in the target table into the block address of the bad block covered by the empty data block, modifying the data object number of each empty data block in the target table into the data object number of the target table, and modifying the check value of each empty data block in the target table.

And a bad block data restoring unit 50 for writing the data stored in the restoration table into the target table.

And an index constructing unit 60, configured to reconstruct an index of the target table.

Based on the data processing device disclosed by the application, the bad blocks in the target table are eliminated, the data which are not damaged in each bad block are reinserted into the target table, the data in the bad blocks are recovered to the maximum extent, and the data readability of the table with the bad blocks in the Oracle database can be recovered under the condition that the Oracle database has no backup, so that the DDL operation and the business operation of the Oracle database can be normally executed. Moreover, the entire data processing process is performed online without shutdown (neither application nor Oracle database is stopped). In addition, the data processing apparatus disclosed in the above application performs data migration only for the bad blocks in the target table, that is, stores the data in the bad blocks to the recovery table, and writes the data in the recovery table into the target table, without migrating the data in the entire target table, and the time required for the entire data processing process mainly depends on the number of the bad blocks in the target table and the time for reconstructing the index of the target table, so that the recovery speed is fast.

Optionally, on the basis of the data processing apparatus shown in fig. 4, a null data block construction unit is further provided. The empty data block building unit is used for building an empty data block in an Oracle database.

As an implementation manner, the process of constructing the empty data block by the empty data block constructing unit specifically includes: creating a temporary table, wherein the table structure of the temporary table is consistent with that of the target table; inserting a record into the temporary table; and deleting the record to obtain a null data block.

Optionally, in the data processing apparatus disclosed above in the present application, the block address obtaining unit 10 includes:

a bad block detection subunit, configured to perform bad block detection on the multiple data blocks in the target table, respectively, so as to determine whether the multiple data blocks are bad blocks;

As an implementation manner, the bad block detection subunit performs bad block detection on any data block in the target table to determine whether the data block is a bad block, specifically:

reading data rows in the data blocks one by executing an SQL query instruction; wherein, one SQL query instruction carries the data line number of one data line in the data block; and if any one data line in the data block fails to be read, determining that the data block is a bad block, and if all the data lines in the data block are successfully read, determining that the data block is normal.

Optionally, in the data processing apparatus disclosed in the above application, the bad block data processing unit 30 is specifically configured to:

reading data rows in the bad blocks one by executing SQL query instructions, wherein one SQL query instruction carries a data row number of one data row in the bad blocks; and storing the read data to a pre-established recovery table.

The embodiments in the present specification are described in a progressive manner, and the same and similar parts among the embodiments are referred to each other, and each embodiment focuses on the differences from the other embodiments. In particular, for the apparatus or apparatus embodiments, since they are substantially similar to the method embodiments, they are described relatively simply, and reference may be made to some descriptions of the method embodiments for related points. The above-described apparatuses and apparatus embodiments are merely illustrative, wherein the units described as separate parts may or may not be physically separate, and the parts displayed as units may or may not be physical units, may be located in one place, or may be distributed on a plurality of network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the solution of the present embodiment. One of ordinary skill in the art can understand and implement it without inventive effort.

Those of skill would further appreciate that the various illustrative elements and algorithm steps described in connection with the embodiments disclosed herein may be implemented as electronic hardware, computer software, or combinations of both, and that the various illustrative components and steps have been described above generally in terms of their functionality in order to clearly illustrate this interchangeability of hardware and software. Whether such functionality is implemented as hardware or software depends upon the particular application and design constraints imposed on the implementation. Skilled artisans may implement the described functionality in varying ways for each particular application, but such implementation decisions should not be interpreted as causing a departure from the scope of the present application.

The previous description of the disclosed embodiments is provided to enable any person skilled in the art to make or use the present application. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the generic principles defined herein may be applied to other embodiments without departing from the spirit or scope of the application. Thus, the present application is not intended to be limited to the embodiments shown herein but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A data processing method of an Oracle database is characterized by comprising the following steps:

obtaining the block address of a bad block in a target table;

writing the data stored in the recovery table into the target table;

reconstructing the index of the target table.

2. The method of claim 1, wherein constructing the empty data block comprises:

inserting a record in the temporary table;

and deleting the record to obtain a null data block.

3. The method of claim 1, wherein obtaining the block address of the bad block in the target table comprises:

the block address of the data block determined to be a bad block is obtained.

4. The method of claim 3, wherein performing bad block detection on any data block in the target table to determine whether the data block is a bad block comprises:

5. The method of claim 1, wherein storing uncorrupted data in the bad block of the target table to a pre-established recovery table comprises:

and storing the read data to a pre-established recovery table.

6. An apparatus for data processing of an Oracle database, the apparatus comprising:

7. The apparatus of claim 6, further comprising a null data block construction unit;

8. The apparatus of claim 6, wherein the block address obtaining unit comprises:

9. The apparatus according to claim 8, wherein the bad block detecting subunit performs bad block detection on any data block in the target table to determine whether the data block is a bad block, specifically:

10. The apparatus of claim 6, wherein the bad block data processing unit is specifically configured to: