Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to use Oracle to repair ORA-01578 and ORA-26040 errors". In daily operation, I believe many people have doubts about how to use Oracle to repair ORA-01578 and ORA-26040 errors. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "how to use Oracle to repair ORA-01578 and ORA-26040 errors". Next, please follow the editor to study!

DG data synchronization is based on log streams, which is why the main library needs to be set to FORCE LOGGING during the DG configuration phase.

However, this can also cause a lot of problems, such as slower execution of DML-type SQL, especially when updating or importing large quantities of data.

DBA wants to complete the migration in minimum downtime when using the data pump, so it may take into account the minimum log import to speed up the import

Then resynchronize the backup library. In these scenarios, DBA may use NOLOGGING operations to save a lot of data insertion time

The problem with this operation is that if the library has a ready library, because the NOLOGGING insert operation of the main library will not generate Redo

Therefore, it will not be transferred and applied on the slave database, which will lead to problems with the data of the slave database and errors reporting to ORA-01578 and ORA-26040.

Simulation test recovery:

Prod is primary database

Stddb is standby database

Set FORCE_LOGGING to NOLOGGING mode on a master library with an active / standby relationship:

SYS@prod > alter database no force logging

HR@prod > create table test as select * from employees

Perform self-insertion of construction data

HR@prod > insert / + APPEND/ into test select * from test

HR@prod > commit

HR@prod > insert / + APPEND/ into test select * from test

HR@prod > commit

HR@prod > insert / + APPEND/ into test select * from test

HR@prod > commit

HR@prod > insert / + APPEND/ into test select * from test

HR@prod > commit

Perform the query operation in the slave database:

SYS@stddb > select count () from hr.test

Select count () from hr.test

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 4, block # 669)

ORA-01110: data file 4:'/ u01 * app *

ORA-26040: Data block was loaded using the NOLOGGING option

Solution:

Copy the data file that contains the missing data from the main library to the physical standby library and rename the data file to solve the problem.

Main database query:

SYS@prod > select name,unrecoverable_change# from v$datafile

/ u01/app/oracle/oradata/prod/users01.dbf 1151089

SYS@stddb > select name,unrecoverable_change# from v$datafile

/ u01/app/oracle/oradata/stddb/users01.dbf 0

Compare the query results of the primary database and the standby database:

In the above two query results, compare the values of the UNRECOVERABLE_CHANGE# column.

If the value of the UNRECOVERABLE_CHANGE# column in the main library is greater than the same column in the standby library, you need to restore these data files in the standby library.

Copy the data file corresponding to the primary database to the standby database:

SYS@prod > ALTER TABLESPACE USERS BEGIN BACKUP

[oracle@service1 prod] $cp users01.dbf / tmp/

SYS@prod > ALTER TABLESPACE USERS END BACKUP

[oracle@service1 tmp] $scp users01.dbf 192.168.1.104:/home/oracle/

SYS@prod > ALTER TABLESPACE USERS END BACKUP

On the standby library, RENAME the old data file to the new data file:

Since the names of the master and standby data files are the same, rename the data files transferred from the main library

[oracle@service2 ~] $mv users01.dbf users02.dbf

[oracle@service2 ~] $cp users02.dbf / u01/app/oracle/oradata/stddb/

SYS@stddb > alter database recover managed standby database cancel

SYS@stddb > startup force mount

SYS@stddb > alter system set standby_file_management=manual

# when performing RENAME operations in slave database, this parameter is required to be MANUAL

SYS@stddb > alter database rename file'/ u01ActionAccordUs01.dbf's01.dbf' / u01qappr alter database rename file'/ u01qqappr _ r _ r

SYS@stddb > alter system set standby_file_management=auto

SYS@stddb > alter database open

SYS@stddb > alter databsae recover managed standby database using current logfile disconnect from session

SYS@stddb > select count (*) from hr.test

COUNT (*)

1712

At this point, the study on "how to use Oracle to fix ORA-01578 and ORA-26040 errors" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.