Email: service@parnassusdata.com 7 x 24 online support!
_allow_resetlogs_corruption force open oracle database
If you cannot recover data by yourself, ask Parnassusdata, the professional ORACLE database recovery team for help.
_ALLOW_RESETLOGS_CORRUPTION DB_Parameter
========================================
Steps to attempt to force the database open:
1) Backup the database while the database is closed. THE INSTRUCTIONS HERE ARE DESTRUCTIVE. YOU ARE STRONGLY ADVISED TO BACKUP THE DATABASE BEFORE PROCEEDING. IF YOU DO NOT DO THIS YOU MAY LOSE THE CHANCE TO TRY OTHER OPTIONS.
2) If your datafiles are from different points in time, it is best to try to use system tablespace datafiles at a similar timestamp to the OLDEST files you have online in the database. This reduces the chance that you will get problems during the bootstrap phase of opening the database.
3) Edit your *init<sid>.ora file to change undo_management and add two parameters.
* Change UNDO_MANAGEMENT=AUTO to UNDO_MANAGEMENT=MANUAL
* Remove or comment out UNDO_TABLESPACE and UNDO_RETENTION.
* Add
_ALLOW_RESETLOGS_CORRUPTION = TRUE
_CORRUPTED_ROLLBACK_SEGMENTS =(comma separated list of Automatic Undo segments) Example:
_CORRUPTED_ROLLBACK_SEGMENTS = (_SYSSMU1$, _SYSSMU2$, _SYSSMU3$, _SYSSMU4$,
_SYSSMU5$, _SYSSMU6$, _SYSSMU7$, _SYSSMU8$, _SYSSMU9$, _SYSSMU10$)
Note, sometimes the alert log will tell you what Automatic Undo segments are in use. Search the alert log for SYSS. If the alert log does not contain that
information then use _SYSSMU1$ through _SYSSMU10$ as shown in the example above.
From 11g onwards the names of the UNDO-segments are a little different and have an extension with this format: _SYSSMU_$. Example:
_SYSSMU1_3423929671$
_SYSSMU2_3471197032$
_SYSSMU3_1940572779$
_SYSSMU4_703930491$
_SYSSMU5_2293911943$
_SYSSMU6_2782670761$
_SYSSMU7_3176421677$
_SYSSMU8_1585569843$
_SYSSMU9_1242704454$
_SYSSMU10_777531512$
In UNIX you can issue this command to get the undo segment names:
$ strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u
From the output of the strings command above, add a $ to end of each _SYSSMU undo segment name.
* If you only have a spfile available, you can from the closed, nomount or the mount stage create an init<sid>.ora file as follows:
SQL> CREATE PFILE FROM SPFILE;
Do NOT edit the SPFILE.
4) Invoke SQL*Plus, startup mount, check that correct init<sid>.ora was used and all datafiles are in the status of online or system.
$ sqlplus "/as sysdba"
SQL> startup mount pfile = (full path / file name to init<sid>.ora) Confirm that the hidden parameters from step 3 were used:
SQL> show parameters corrupt
You should see both hidden parameters listed. If not, the wrong init<sid>.ora may have been modified. Do not continue until "show parameters corrupt" shows both hidden parameters.
SQL> show parameters undo
You should see undo management set to manual. If not, the wrong init<sid>.ora may have been modified. Do not continue until "show parameters undo" shows undo management as manual.
Check that all files you want to open with are listed as ONLINE or as SYSTEM.
SQL> select name, file#, status from v$datafile where status not in ('SYSTEM', 'ONLINE');
If any rows are returned from the query above, bring the file(s) online with:
SQL> ALTER DATABASE DATAFILE file# ONLINE;
and repeat until there are no files in an OFFLINE status. If any file remains or changes into "recover" status after you try to online the file proceed to step 5.
5) Perform a fake incomplete recovery then open the database with resetlogs.
SQL> recover database until cancel;
or
SQL> recover database using backup controlfile until cancel;
WHEN PROMPTED FOR AN ARCHIVELOG FILE TYPE cancel THEN PRESS ENTER.
SQL> ALTER DATABASE OPEN RESETLOGS;
6) If the database opens try selecting from a table. For example:
SQL> SELECT SYSDATE FROM DUAL;
If you get a row back the database is open and "functional". If you wish, you may try to select from a other tables to make sure the database is functional enough for the required export.
With the database open and functional you should attempt to export the database IMMEDIATELY. Once you have an export the database MUST be recreated from scratch. This means dropping and deleting ALL datafiles and creating a new database from scratch.
A database which has been opened in this way but not rebuilt will not be supported by Oracle. Any delay in exporting the contents or any attempt to use the system may cause irreparable damage.
NOTE: BE SURE TO REVERSE / REMOVE THE INIT.ORA PARAMETERS ADDED IN STEP 3 OTHERWISE YOU MAY ACCIDENTALLY CORRUPT ANY NEW DATABASE CREATED USING THE SAME INIT<sid>.ORA FILE.
7) If the instance crashed in the open phase of step 5, check for trace files in the background dump destination. If you find a trace file, check to see if the trace file has an ORA-00600 [2662] or ORA-00600 [4000] error in it.
Either of these errors may also be seen in the alert.log file.
If you see the ORA-00600 [2662] or ORA-00600 [4000] error, provide Oracle Support Services the full error message. Oracle Support Services will provide steps to advance the SCN using a hidden parameter.
552438.1 How To Adjust the SCN using parameter _MINIMUM_GIGA_SCN @
Formerly this note mentioned adjust_scn of event 10015 but no longer. An @ event is sometimes not helpful because some types or issues do not allow @ Oracle to reach the point of using the event, thus the change. Note 552438.1 @ requires a calculation best resolved by support.
@ As of 10g, the parameter _ALLOW_ERROR_SIMULATION=TRUE may be set.
NOTE: BE SURE TO REVERSE / REMOVE THE INIT.ORA PARAMETERS ADDED IN STEP 3 OTHERWISE YOU MAY ACCIDENTALLY CORRUPT ANY NEW DATABASE CREATED USING THE SAME INIT<sid>.ORA FILE.
CAUTION: Once the database is open, it is imperative that you export, rebuild the database, and import.
By forcing open the database in this fashion, there is a strong likelihood of logical corruption, possibly affecting the data dictionary. Oracle does not guarantee that all of the data will be accessible nor will it support a database that has been opened by this method and that the database users will be allowed to continue work. All this does is provide a way to get at the contents of the database for extraction, usually by export. It is up to you to determine the amount of lost data and to correct any logical corruption issues.
This documentation has been prepared avoiding the mention of the complex structures from the code and to simply give an insight to the 'damage it could cause'. The usage of this parameter leads to an in-consistent Database with no
other alternative but to rebuild the complete Database. This parameter could be used when we realize that there are no stardard options available and are convinced that the customer understands the implications of using the Oracle's secret parameter.
The factors to be considered are
1. Customer does not have a good backup.
2. A lot of time and money has been invested after the last good backup and there is no possibility for reproduction of the lost data.
3. The customer has to be ready to export the full database and import it back after creating a new one.
4. There is no 100% guarantee that by using this parameter the database would come up.
5. Oracle does not support the database after using this parameter for recovery.
6. ALL OPTIONS including the ones mentioned in the action part of the error message have been tried.
By setting _ALLOW_RESETLOGS_CORRUPTION=TRUE, certain consistency checks are SKIPPED during database open stage. This basically means it does not check the datafile headers as to what the status was before the shutdown and how it was shutdown. The following cases mention few of the checks that were skipped.
Case-I
------
Verification that the datafile present has not been restored from a BACKUP taken before the database was opened successfully by using RESETLOGS.
ORA-01190: control file or data file %s is from before the last RESETLOGS
Cause: Attempting to use a data file when the log reset information in the file does not match the control file. Either the data file or the control file is a backup that was made before the most recent ALTER DATABASE OPEN RESETLOGS.
Action: Restore file from a more recent backup.
Case-II
-------
Verification that the status bit of the datafile is not in a FUZZY state.
The datafile could be in this state due to the database going down when the
- Datafile was on-line and open
- Datafile was not closed cleanly (maybe due to OS).
ORA-01194: file %s needs more recovery to be consistent
Cause: An incomplete recover session was started, but an insufficient number of logs were applied to make the file consistent. The reported file was not closed cleanly when it was last opened by the database. It must be recovered to a time when it was not being updated. The most likely cause of this error is forgetting to restore the file from a backup before doing incomplete recovery.
Action: Either apply more logs until the file is consistent or restore the file from an older backup and repeat recovery.
Case-III
--------
Verification that the COMPLETE recover strategies have been applied for recovering the datafile and not any of the INCOMPLETE recovery options. Basically because the complete recovery is one in which we even apply the ON-LINE redo log files and open the DB without reseting the logs.
ORA-01113: file '%s' needs media recovery starting at log sequence # %s
Cause: An attempt was made to open a database file that is in need of media recovery.
Action: First apply media recovery to the file.
Case-IV
-------
Verification that the datafile has been recovered through an END BACKUP if the control file indicates that it was in backup mode. This is useful when the DB has crashed while in hot backup mode and we lost all log files in DB version's less than V7.2.
ORA-01195: on-line backup of file %s needs more recovery to be consistent"
Cause: An incomplete recovery session was started, but an insufficient number of logs were applied to make the file consistent. The reported file is an on-line backup which must be recovered to the time the backup ended.
Action: Either apply more logs until the file is consistent or resotre the database files from an older backup and repeat recovery. In version 7.2, we could simply issue the ALTER DATABASE DATAFILE xxxx END BACKUP statement and proceed with the recovery. But again to issue this statement, we need to have the ON-LINE redo logs or else we still are forced to use this parameter.
Case-V
------
Verification that the data file status is not still in (0x10) MEDIA recovery FUZZY.
When recovery is started, a flag is set in the datafile header status flag to indicate that the file is presently in media recovery. This is reset when recovery is completed and at times when it has not been reset we are forced to use this paramter.
ORA-01196: file %s is inconsistent due to a failed media recovery session
Cause: The file was being recovered but the recovery did not terminate normally. This left the file in an inconsistent state. No more recovery was successfully completed on this file.
Action: Either apply more logs until the file is consistent or restore the backup again and repeat recovery.
Case-VI
-------
Verification that the datafile has been restored form a proper backup to correspond with the log files. This situation could happen when we have decided that the data file is invalid since its SCN is ahead of the last applied logs SCN but it has not failed on one of the ABOVE CHECKS.
ORA-01152: file '%s' was not restored from a sufficientluy old backup"
Cause: A manual recovery session was started, but an insufficient number of logs were applied to make the database consistent. This file is still in the future of the last log applied. Note that this mistake can not always be caught.
Action: Either apply more logs until the database is consistent or restore the database file from an older backup and repeat recovery.
Search Words:
=============
ORA-01190 ORA-01194 ORA-01113 ORA-01195 ORA-01196 ORA-01152
Copyright © 2013, 2023, parnassusdata.com. 版权所有。dbrecover.com
沪公网安备 31010802001377号