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

How to carry out abnormal recovery of ora-01110 errors, I believe that many inexperienced people are at a loss about this. Therefore, this paper summarizes the causes and solutions of the problems. Through this article, I hope you can solve this problem.

One day, the production database server went down unexpectedly. The following error occurred when restarting the server to open the database:

SQL > startup

ORACLE instance started.

Total System Global Area 1.6911E+10 bytes

Fixed Size 2113696 bytes

Variable Size 8472498016 bytes

Database Buffers 8422162432 bytes

Redo Buffers 14659584 bytes

Database mounted.

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1:'/ oracle/PRD/data1/system_1/system.data1'

One solution is this.

SQL > RECOVER DATAFILE'/ oracle/PRD/data1/system_1/system.data1'

Recover damaged files.

SQL > recover tablespace system;// is not necessarily required. When prompted that recovery is not required, you can directly open the database.

Restore the system tablespace.

SQL > RECOVER DATABASE

Restore the database.

SQL > ALTER DATABASE OPEN

Database altered.

When doing this kind of operation, it may not be successful. So please be sure to back up all the data files, control files and log files in the current state, protect the site first, and then make other attempts.

After cold standby, it is not possible to try this method. After consulting Dbsnake, try abnormal recovery and force open the database.

# su-oracle

$vi / oracle/PRD/data1/init.ora modifies initialization parameters

*. _ allow_resetlogs_corruption=FALSE

Modify it to *. _ allow_resetlogs_corruption=TRUE (# change it to the original value FALSE after starting the library normally)

(this parameter allows you to open it if the data is inconsistent)

* .undo_management='AUTO'

Modify to * .undo _ management='MANUAL' (# change to the original value AUTO after starting the library normally)

(this parameter is to change the UNDO tablespace from automatic management to manual management)

Add this sentence

*. _ corrupted_rollback_segments= (_ SYSSMU12 $) (# remove this sentence after starting the library normally)

(block the rollback segment of the transaction with errors, according to the ALERT prompt ORA-01555: snapshot too old: rollback segment number 12 with name "_ SYSSMU12 $" too small)

Then save the parameter file, reopen the library, and find that it still doesn't work. Check the ALERT log and find that the SCN number is inconsistent. The next step is to advance the SCN value.

View estimated SCN valu

SQL > select dbms_flashback.get_system_change_number () / (1024 / 1024 / 1024) from dual

DBMS_FLASHBACK.GET_SYSTEM_CHAN

-

3.185796233

The valuation is 3.18NG, and the advanced SCN value is larger than this value, so the value is set to 4.

Add this sentence to $vi / oracle/PRD/data1/init.ora

*. _ minimum_giga_scn=4

Then try to open the library

SQL > startup mount pfile='/oracle/PRD/data1/init.ora'

ORACLE instance started.

SQL > recover database until cancel

SQL > alter database open resetlogs

Database altered.

The database opened successfully.

Remove the implied parameters and restore the initial parameters.

Last

SQL > create spfile from pfile='/oracle/PRD/data1/init.ora'

File created.

SQL > startup

ORACLE instance started.

Then export the full database of EXP in time to protect the data, and check the data of DBV to see if there are any physical bad blocks.

The above methods are only used in special cases, please refer to them carefully.

SCN knowledge points attached:

1. The SCN of Oracle can last for 534 years under the condition of 16384 commit per second. 16384 commit per second is the limit commit strength of any system that Oracle thought earlier.

2. The starting point of SCN in Oracle is January 1st, 1988.

3. _ minimum_giga_scn=n means to push SCN forward to nG, but please note that this implied parameter is only used when SCN is less than nG, otherwise Oracle will ignore this implied parameter.

SCN principle: in the process of safely shutting down the database, the system will perform a checkpoint action, and the termination scn of all data files will be set to the value of the startup scn in the header of the data file. When the database is restarted, Oracle compares the startup scn in the header with the database file checkpoint scn, and if the two values match, oracle then compares the startup scn in the data file header with the termination scn of the data file in the control file. If the two values are the same, it means that most of the data blocks have been committed, and none of the database changes have been lost in the process of shutting down the database, so no recovery operation is required to start the database this time. At this point, the database can be opened. When all databases are open, the value of the data file termination scn stored in the control file is changed to null again, indicating that the data file is open and ready for use.

However, in the case of abnormal crash, because the last checkpoint is not carried out or halted in the middle, in the control file, there is a part of the data file whose stop SCN is the maximum. After the database restart, it will check the stop SCN of each data file in the control file, if stop SCN is not equal to the checkpoint SCN of each data file in the control file. The log file redo is used for all database operations from the beginning of checkpoint SCN to stop SCN. When the database finds that the SCN is inconsistent, it should be the SCN in the SCN > = data file in the redo log file. When locating which redo log file is used, the low scn,next scn in the log file header is used, that is, the low scn,next scn of the redo log to be used must contain the change vector needed to redo the data file. After determining which data file must redo,oracle compares the SCN in the change vector with the SCN in the data file data block, if the SCN of the change vector is less than the scn of the data block, skip this change vector, otherwise redo.

After reading the above, have you mastered how to recover from ora-01110 errors? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.