ORA-01110 error caused by standby database query and Its Repair

ORA-01110 error caused by standby database query and Its Repair

Recently, it helped the business department solve a technical problem because it needed to analyze the problematic data to discover data problems. Of course, one difficulty is to filter out the data. When I saw the statements they provided and made a simple data evaluation in the slave database, I found that the data volume was much larger than I expected, there are about 2 million pieces of data, and the business department has an excel file at hand, which needs to be compared with the data. Of course, it is quite complicated to stop the filtering logic, at the beginning, we suggested that they use a large amount of data, and there may still be problems with using excel, but the business department should think that there should be no big problem. They will have formulas in excel to handle it. It also makes sense to think about it, provide them with a file of nearly 40 m.

By noon, the business department found me and said that the two excel files had been fully occupied by the computer. I still want to ask if there is any good solution. From my perspective, these operations are fully qualified using SQL statements, and a larger data volume is not a problem. After a brief understanding of the requirements, I confirmed the business logic with the developers and began to prepare the environment. Of course, the idea is still more conventional and implemented using external tables.

First, you can use excel to obtain the required data columns and generate either a csv file or a text file. Then, create an external table on the target database server to read the text data and perform a set operation with the relevant table, such as Minus and intersect, to obtain the final result.

It is easy to say, and an interesting problem has been encountered in actual operations.

This type of large query is prepared in the standby database, and an error is thrown. The created external table is bkjia. temp_tab.
Select t1.cash, t1.TEST _ TRANSACTION_ID, t2.trade _ no, t2.cash from TEST_NEW.TEST_detail t1, bkjia. temp_tab t2 where req_time> = to_date ('2017-03-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ') and req_time <to_date ('2017-04-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ') and status <> '1' and pay_way_channel_code in ('44', '45', '46', '15', '16', '17', '18 ', '19', '91 ', '93', '94 ', '000000', '000000', '000000', '000000', '000000', '000000 ', '123 ')
*
ERROR at line 1:
ORA-00376: file 21 cannot be read at this time
ORA-01110: data file 21: '/U01/app/Oracle/oradata/TEST/TEST_new_index04.dbf'
The system prompts that the file no. 21 cannot be read. Based on the error, it is determined that the file is in the offline status.
Check the status of the data file. You can see that TEST_new_index04.dbf is in the RECOVER status.

Bkjia @ TEST> select file_name, status, online_status from dba_data_files;
FILE_NAME status online _
------------------------------------------------------------------------
/U01/app/oracle/oradata/TEST/TEST_new_data01.dbf AVAILABLE ONLINE
/U01/app/oracle/oradata/TEST/system01.dbf AVAILABLE SYSTEM
...
/U01/app/oracle/oradata/TEST/TEST_new_index04.dbf AVAILABLE RECOVER
This problem may seem strange. Checking the status of data files in the master database is already online, which indicates that a small problem has occurred in the past.
For such problems, a quick solution is to generate a slave Database Control file from the master database, and then start the database to the Mount stage.
However, this time an error occurs. After copying the generated slave Database Control file to the slave database and replacing it, restart the database. The following error is reported by the dg broker.
DGMGRL> show configuration;
Configuration
Name: TEST
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
TEST-Primary database
STEST4-Physical standby database
STEST2-Physical standby database

Current status for "TEST ":
Warning: ORA-16607: one or more databases have failed
View the alert Log and an error with the ORA-01110 is reported.
RFS [1]: Archived Log: '/U01/app/oracle/flash_recovery_area/STEST2/archivelog/2016_04_12/o1_mf_20178158_cjs8mqfp _. arc'
Tue Apr 12 15:24:33 2016
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT NODELAY
Tue Apr 12 15:24:33 2016
Attempt to start background Managed Standby Recovery process (TEST)
MRP0 started with pid = 23, OS id = 10683
Tue Apr 12 15:24:33 2016
MRP0: Background Managed Standby Recovery process started (TEST)
Managed Standby Recovery not using Real Time Apply
MRP0: Background Media Recovery terminated with error 1110
Tue Apr 12 15:24:38 2016
Errors in file/U01/app/oracle/admin/TEST/bdump/TEST_mrp0_10683.trc:
ORA-01110: data file 21: '/U01/app/oracle/oradata/TEST/TEST_new_index04.dbf'
ORA-01122: database file 21 failed verification check
ORA-01110: data file 21: '/U01/app/oracle/oradata/TEST/TEST_new_index04.dbf'
ORA-01203: wrong incarnation of this file-wrong creation SCN
Tue Apr 12 15:24:38 2016
Errors in file/U01/app/oracle/admin/TEST/bdump/TEST_mrp0_10683.trc:
ORA-01110: data file 21: '/U01/app/oracle/oradata/TEST/TEST_new_index04.dbf'
ORA-01122: database file 21 failed verification check
ORA-01110: data file 21: '/U01/app/oracle/oradata/TEST/TEST_new_index04.dbf'
ORA-01203: wrong incarnation of this file-wrong creation SCN
Tue Apr 12 15:24:38 2016
MRP0: Background Media Recovery process shutdown (TEST)
According to the error, it can be seen that there is a problem during file verification, and there is a problem with creation SCN verification.
At this time, view the verbose details in the dg broker, and display the current status of the standby database:
Current status for "sTEST2 ":
Error: ORA-16766: Redo Apply unexpectedly offline
To fix this problem, one policy of the SCN is BBED, but the online database, and considering this risk, is better than modifying the BBED.
However, rebuilding the slave database is the Final Solution. Let's see if there are other solutions.
This data file has been in this status for a long time by viewing detailed information, which means that this information cannot be retained in the control file, and the SCN of the data file is still very early, for example, the SCN situation six months ago. At this time, it is unrealistic to try recover, and archive will not be retained for that long. However, because it is a slave database, it is better to solve this problem, that is, restore from the master database.
This data file is about 5 Gb. Currently, the usage is 60%, and the data file in the rman backup database is about 3 GB.
Therefore, after copying the backup set of the data file to the standby database, use the catalog start with method to restore it.
RMAN> catalog start with '/U01/app/oracle/temp ';
Using target database control file instead of recovery catalog
Searching for all files that match the pattern/U01/app/oracle/temp
List of Files Unknown to the Database
============================================
File Name:/U01/app/oracle/temp/full_1804_908984436_1
Do you really want to catalog the above files (enter YES or NO )? Yes
Cataloging files...
Cataloging done
List of Cataloged Files
======================================
File Name:/U01/app/oracle/temp/full_1804_908984436_1
RMAN> restore datafile 21;
Starting restore at 12-APR-16
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: sid = 2976 devtype = DISK

Channel ORA_DISK_1: starting datafile backupset restore
Channel ORA_DISK_1: specifying datafile (s) to restore from backup set
Restoring datafile 00021 to/U01/app/oracle/oradata/TEST/TEST_new_index04.dbf
Channel ORA_DISK_1: reading from backup piece/U01/app/oracle/temp/full_1804_908984436_1
Channel ORA_DISK_1: restored backup piece 1
Piece handle =/U01/app/oracle/temp/full_1804_908984436_1 tag = TAG20160412T154036
Channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 12-APR-16

At this time, you do not need to restart the slave database. The SCN of the data file is naturally pushed to a new value, and the status of the data file is changed to ONLINE again.
From this case, we can see that the operations on data files still need to be very careful. The status monitoring of data files should also be an important reference for O & M monitoring.