recover and open db if archivelog required for recovery is either miss_465478.1
How to recover and open the database if the archivelog required for recovery is either missing, lost or corrupted? (Doc ID 465478.1)
In this Document
Goal |
Solution |
References |
Applies to:
Oracle Database - Enterprise Edition - Version 8.1.7.4 to 11.1.0.6 [Release 8.1.7 to 11.1]
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 8.1.7.4 to 11.1.0.6
***Checked for relevance on 20-Feb-2014***
Goal
How to recover and open the database if the archivelog required for recovery is either missing, lost or corrupted?
Solution
The assumption here is that we have exhausted all possible locations to find another good and valid copy or backup of the archivelog that we are looking for, which could be in one of the following:
- directories defined in the LOG_ARCHIVE_DEST_n
- another directory in the same server or another server
- standby database
- RMAN backup
- OS backup
If the archivelog is not found in any of the above mentioned locations, then the approach and strategy on how to recover and open the database depends on the SCN (System Change Number) of the datafiles, as well as, whether the log sequence# required for the recovery is still available in the online redologs.
For the SCN of the datafiles, it is important to know the mode of the database when the datafiles are backed up. That is whether the database is open, mounted or shutdown (normally) when the backup is taken.
If the datafiles are restored from an online or hot backup, which means that the database is open when the backup is taken, then we must apply at least the archivelog(s) or redolog(s) whose log sequence# are generated from the beginning and until the completion of the said backup that was used to restore the datafiles.
However, if the datafiles are restored from an offline or cold backup, and the database is cleanly shutdown before the backup is taken, that means that the database is either not open, is in nomount mode or mounted when the backup is taken, then the datafiles are already synchronized in terms of their SCN. In this situation, we can immediately open the database without even applying archivelogs, because the datafiles are already in a consistent state, except if there is a requirement to roll the database forward to a point-in-time after the said backup is taken.
The critical key thing here is to ensure that all of the online datafiles are synchronized in terms of their SCN before we can normally open the database. So, run the following SQL statement, as shown below, to determine whether the datafiles are synchronized or not. Take note that we query the V$DATAFILE_HEADER, because we want to know the SCN recorded in the header of the physical datafile, and not the V$DATAFILE, which derives the information from the controlfile.
to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
from v$datafile_header
group by status, checkpoint_change#, checkpoint_time
order by status, checkpoint_change#, checkpoint_time;
The results of the above query must return one and only one row for the online datafiles, which means that they are already synchronized in terms of their SCN. Otherwise, if the results return more than one row for the online datafiles, then the datafiles are still not synchronized yet. In this case, we need to apply archivelog(s) or redolog(s) to synchronize all of the online datafiles. By the way, take note of the CHECKPOINT_TIME in the V$DATAFILE_HEADER, which indicates the date and time how far the datafiles have been recovered.
It is also important to check the status of the datafiles. Sometimes although the SCN is the same for all files you still cannot open the database. The status can be checked via
select fhsta, count(*) from X$KCVFH group by fhsta;
You should expect to find zero, and 8192 for the system datafile. If the status is 1 or 64 it will be in backup mode and requires more recovery, other statuses should be referred to Oracle support.
You may run the following query to determine the offline datafiles:
where file# in (select file# from v$datafile_header
where status='OFFLINE');
You may issue the following SQL statement to change the status of the required datafile(s) from "OFFLINE" to "ONLINE":
If we are lucky that the required log sequence# is still available in the online redologs and the corresponding redolog member is still physically existing on disk, then we may apply them instead of the archivelog. To confirm, issue the following query, as shown below, that is to determine the redolog member(s) that you can apply to recover the database:
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select LF.member, L.group#, L.thread#, L.sequence#, L.status,
L.first_change#, L.first_time, DF.min_checkpoint_change#
from v$log L, v$logfile LF,
(select min(checkpoint_change#) min_checkpoint_change#
from v$datafile_header
where status='ONLINE') DF
where LF.group# = L.group#
and L.first_change# >= DF.min_checkpoint_change#;
If the above query returns no rows, because the V$DATABASE.CONTROLFILE_TYPE has a value of "BACKUP", then try to apply each of the redolog membes one at a time during the recovery. You may run the following query to determine the redolog members:
If you have tried to apply all of the online redolog members instead of an archivelog during the recovery, but you always received the ORA-00310 error, as shown in the example below, then the log sequence# required for recovery is no longer available in the online redolog.
ORA-00289: suggestion : +BACKUP
ORA-00280: change 189189555 for thread 1 is in sequence #428
Specify log: {
+BACKUP/prmy/onlinelog/group_2.258.603422107
ORA-00310: archived log contains sequence 503; sequence 428 required
ORA-00334: archived log: '+BACKUP/prmy/onlinelog/group_2.258.603422107'
After trying all of the possible solutions mentioned above, but you still cannot open the database, because the archivelog required for recovery is either missing, lost or corrupted, or the corresponding log sequence# is no longer available in the online redolog, since they are already overwritten during the redolog switches, then we cannot normally open the database, since the datafiles are in an inconsistent state. So, the following are the 3 options available to allow you to open the database:
Option#1: Force open the database by setting some hidden parameters in the init.ora. Note that you can only do this under the guidance of Oracle Support with a service request. But there is no 100% guarantee that this will open the database. However, once the database is opened, then we must immediately rebuild the database. Database rebuild means doing the following, namely: (1) perform a full-database export, (2) create a brand new and separate database, and finally (3) import the recent export dump. When the database is opened, the data will be at the same point in time as the datafiles used. Before you try this option, ensure that you have a good and valid backup of the current database.
Option#2: If you have a good and valid backup of the database, then restore the database from the said backup, and recover the database by applying up to the last available archivelog. In this option, we will only recover the database up to the last archivelog that is applied, and any data after that are lost. If no archivelogs are applied at all, then we can only recover the database from the backup that is restored. However, if we restored from an online or hot backup, then we may not be able to open the database, because we still need to apply the archivelogs generated during the said backup in order to synchronize the SCN of the datafiles before we can normally open the database.
Option#3: Manually extract the data using the Oracle's Data Unloader (DUL), which is performed by Oracle Field Support at the customer site on the next business day and for an extra charge. If the customer wants to pursue this approach, we need the complete name, phone# and email address of the person who has the authority to sign the work order in behalf of the customer.
References
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/17252115/viewspace-1160654/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ORA-00265: instance recovery required, cannot set ARCHIVELOGUIHive
- ORA-00265: instance recovery required, cannot set ARCHIVELOG modeUIHive
- Open DB failed-DATAFILE NEEDS MORE RECOVERY TO BE CONSISTENT_1528788.1AI
- ORA-16139: media recovery requiredUI
- 關於ORACLE RESTRICT模式,以及START OPEN RECOVEROracleREST模式
- recover database using backup controlfile利用archivelog files.DatabaseHive
- The Db2 Recovery History FileDB2
- [DB2]使用recover命令找回刪除的表DB2
- Backup And Recovery User's Guide-使用RECOVER命令的自動恢復GUIIDE
- Oracle RAC 重置db_recovery_file_dest_size引數Oracle
- Oracle歸檔目錄 和 DB_RECOVERY_FILE_DESTOracle
- oracle 修改archive的地址到db_recovery_file_destOracleHive
- DUMP-CX_SY_OPEN_SQL_DB-DBSQL_DUPLICATE_KEY_ERRORSQLError
- [oracle]log_archive_dest_n與DB_RECOVERY_FILE_DESTOracleHive
- 物理備庫open報錯ORA-10458: standby database requires recoveryDatabaseUI
- 冷備手工完全恢復(recover database,recover tablespace,recover datafile)Database
- archivelog模式和flashback db以及guarantee restore point之間的相互制約關係!Hive模式REST
- 將OPEN BRAVO資料庫從oracle 遷移到DB2資料庫OracleDB2
- cron with recover
- Restore ArchivelogRESTHive
- using rman to creat oracle10g data guard standby db_with recoveryOracle
- Django提示mysql版本過低:django.db.utils.NotSupportedError: MySQL 8 or later is required (found 5.7.26).DjangoMySqlErrorUI
- recover database until cancel和 recover database區別Database
- Error 1: Filename must be either an absolute pathname or blocklist(轉)ErrorBloC
- "locate: can not open `/var/lib/mlocate/mlocate.db"問題解決
- Backup And Recovery User's Guide-介質恢復問題解決-執行RECOVER…TEST語句GUIIDE
- Backup And Recovery User's Guide-使用RECOVER命令的AUTOMATIC選項進行自動恢復GUIIDE
- Backup And Recovery User's Guide-RMAN資料修復概念-OPEN RESETLOGS操作GUIIDE
- Delete Dataguard ArchivelogdeleteHive
- 配置ARCHIVELOG 模式Hive模式
- Recover_DatabaseDatabase
- rman recover databaseDatabase
- Oracle OCP 1Z0 053 Q517(Block Media Recovery&Archivelog Mode&Flashback Logging)OracleBloCHive
- 將OPEN BRAVO後臺資料庫從ORACLE遷移到DB2資料庫OracleDB2
- RMAN - backup archivelog all 與 backup database plus archivelog 區別HiveDatabase
- rman restore archivelog logRESTHive
- [zt] crosscheck archivelog allROSHive
- 轉至archivelog模式Hive模式