Recovering from a corrupted undo tablespace. ORA-01548 and ORA-30025
GOAL
There are many documents available on the internet and other electronic source that show the use ofunsupport parameters to open a database. Two of these parameters are _OFFLINE_ROLLBACK_SEGMENTS and
_CORRUPTED_ROLLBACK_SEGMENTS.
What are _OFFLINE_ROLLBACK_SEGMENTS and _CORRUPTED_ROLLBACK_SEGMENTS?
_OFFLINE_ROLLBACK_SEGMENTS is a unsupported init.ora parameter which can allow you to cause logical database corruption.
_CORRUPTED_ROLLBACK_SEGMENTS is more dangerous parameter than _OFFLINE_ROLLBACK_SEGMENTS. It basically prevents access to the listed rollback segments headers and assumes all transactions in them are committed. This can very easily cause logical database corruption.
It is imperative that these parameters are not used without contacting
Oracle first.
SOLUTION
Do not use these parameters unless Oracle Support advise you to do so.CAUTION:
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.
Whilst playing around with my database to simulate a database recovery with the undo
tablespace missing, I ended up in a bit of a mess regarding dropping the old undo
tablespace.
Here’s how to fix it.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO03
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1043886080 bytes
Fixed Size 2182344 bytes
Variable Size 780141368 bytes
Database Buffers 255852544 bytes
Redo Buffers 5709824 bytes
Database mounted.
Database opened.
SQL> drop tablespace undo2;
drop tablespace undo2
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU21_2734716338$' found, terminate
dropping tablespace
So, we have active segments in the missing UNDO2 tablespace which I cannot drop. So we have to take the undo management to manual and offline those segments.
#*.undo_management='AUTO' - removed #*.undo_tablespace='UNDO03' - removed *.undo_management='MANUAL' - addedNow we need to add the parameter to take the rollback segment offline. This is NOT supported except through oracle support, so use it as a last resort._offline_rollback_segments=('_SYSSMU21_2734716338$')Now mount the database.SQL> startup mount pfile=C:APPGARYPRODUCT11.2.0DB1DATABASEinitmon10g.ora ORACLE instance started. Total System Global Area 1043886080 bytes Fixed Size 2182344 bytes Variable Size 780141368 bytes Database Buffers 255852544 bytes Redo Buffers 5709824 bytes Database mounted. SQL>Find the file you need to remove and perform an offline drop on it.SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- C:APPGARYORADATAMON10GDATAFILEO1_MF_SYSTEM_7B64CS45_.DBF C:APPGARYORADATAMON10GDATAFILEO1_MF_SYSAUX_7B64CS8F_.DBF C:APPGARYORADATAMON10GDATAFILEO1_MF_UNDO03_7NZQL014_.DBF C:APPGARYORADATAMON10GDATAFILEO1_MF_USERS_7B64CSCZ_.DBF C:APPGARYORADATAMON10GDATAFILERDCM.DBF C:APPGARYORADATAMON10GDATAFILEO1_MF_UNDO2_7NZCMO6K_.DBF 6 rows selected. SQL> alter database datafile 'C:APPGARYORADATAMON10GDATAFILEO1_MF_UNDO2_7NZCMO6K_.DBF' offline drop; Database altered.Now open the database.SQL> alter database open; Database altered.Drop the rollback segment and the tablespace which gave the grief.SQL> drop rollback segment "_SYSSMU21_2734716338$"; Rollback segment dropped.And the tablespace.SQL> drop tablespace UNDO2 including contents and datafiles; drop tablespace UNDO2 including contents and datafiles * ERROR at line 1: ORA-01548: active rollback segment '_SYSSMU22_850805769$' found, terminate dropping tablespaceAhh, so we have an additional problem of more rollback segments which are active. Here we really want a list of all the rollback segments in that tablespace.SQL> select segment_name, tablespace_name from dba_rollback_segs where tablespace_name = 'UNDO2'; SEGMENT_NAME TABLESPACE_NAME ------------------------------ ------------------------------ _SYSSMU22_850805769$ UNDO2 _SYSSMU23_2201878545$ UNDO2 _SYSSMU24_4120344252$ UNDO2 _SYSSMU25_3345701716$ UNDO2 _SYSSMU26_2861972660$ UNDO2 _SYSSMU27_2660803323$ UNDO2 _SYSSMU28_1865611126$ UNDO2 _SYSSMU29_1437990130$ UNDO2 _SYSSMU30_135696508$ UNDO2 9 rows selected.Right, got those. Shutdown the database and update the init file.Ensure that the quotes are correct, and you have not entered them as below:_offline_rollback_segments=('_SYSSMU22_850805769$',_'SYSSMU23_2201878545$',_'SYSSMU24_4120344252$',_'SYSSMU25_3345701716$',_'SYSSMU26_2861972660$',_'SYSSMU27_2660803323$',_'SYSSMU28_1865611126$',_'SYSSMU29_1437990130$','_SYSSMU30_135696508$')Shutdown, edit the file, and the above entry and restart the database.QL> startup ORACLE instance started. Total System Global Area 1043886080 bytes Fixed Size 2182344 bytes Variable Size 780141368 bytes Database Buffers 255852544 bytes Redo Buffers 5709824 bytes Database mounted. Database opened. SQL> select 'drop rollback segment "'||segment_name||'";' 2 from dba_rollback_segs 3 where tablespace_name = 'UNDO2'; 'DROPROLLBACKSEGMENT"'||SEGMENT_NAME||'";' ------------------------------------------------------- drop rollback segment "_SYSSMU22_850805769$"; drop rollback segment "_SYSSMU23_2201878545$"; drop rollback segment "_SYSSMU24_4120344252$"; drop rollback segment "_SYSSMU25_3345701716$"; drop rollback segment "_SYSSMU26_2861972660$"; drop rollback segment "_SYSSMU27_2660803323$"; drop rollback segment "_SYSSMU28_1865611126$"; drop rollback segment "_SYSSMU29_1437990130$"; drop rollback segment "_SYSSMU30_135696508$"; 9 rows selected.SQL> drop rollback segment "_SYSSMU22_850805769$"; Rollback segment dropped. SQL> drop rollback segment "_SYSSMU23_2201878545$"; Rollback segment dropped. SQL> drop rollback segment "_SYSSMU24_4120344252$"; Rollback segment dropped. SQL> drop rollback segment "_SYSSMU25_3345701716$"; Rollback segment dropped. SQL> drop rollback segment "_SYSSMU26_2861972660$"; Rollback segment dropped. SQL> drop rollback segment "_SYSSMU27_2660803323$"; Rollback segment dropped. SQL> drop rollback segment "_SYSSMU28_1865611126$"; Rollback segment dropped. SQL> drop rollback segment "_SYSSMU29_1437990130$"; Rollback segment dropped. SQL> drop rollback segment "_SYSSMU30_135696508$"; Rollback segment dropped.Now you can drop the tablespace with one of the following. In 11g, this worked fine.SQL> drop tablespace undo2; Tablespace dropped.You may require the following but I doubt it.SQL> drop tablespace undo2 including contents and datafiles; Tablespace dropped.Now you can shutdown the database and restart it. Because we did not modify the spfile, we can simply start the database again.I’ll also clear the init file to reset it back to the copy of the spfile.SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 1043886080 bytes Fixed Size 2182344 bytes Variable Size 780141368 bytes Database Buffers 255852544 bytes Redo Buffers 5709824 bytes Database mounted. Database opened. SQL> create pfile from spfile; File created.That’s it, tablespace has gone.SQL> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX TEMP USERS RDCM UNDO03 6 rows selected.=============================================================Unable to Drop Undo tablespace Since Undo Segment is in Needs Recovery (Doc ID 1295294.1)APPLIES TO:
Oracle Database - Enterprise Edition - Version 8.1.7.4 to 11.2.0.2.0 [Release 8.1.7 to 11.2] Information in this document applies to any platform.SYMPTOMS
Dropping a Undo tablespace give message ORA-01548: active rollback segment Or Undo segment shows status as needs recoveryCHANGES
New Undo tablespace was created and a attempt is made to drop old undo tablespaceCAUSE
The issue could happen if the datafile on which the undo segments reside is offline and the transaction cannot be rolled backed since the file is offline Or This could also happen if there is any issue in the Undo segment itselfSOLUTION
Check if the Undo segment status first ----------------------------------------SQL> select segment_name,status,tablespace_name from dba_rollback_segs where status not in ('ONLINE', 'OFFLINE') ; SEGMENT_NAME STATUS TABLESPACE_NAME ----------------- ----------- ---------------- _SYSSMU3$ NEEDS RECOVERY UNDO01In the above example Undo segment _SYSSMU3$ is in Needs recovery status. This segment belongs to Undo tablespace UNDO01 Check the status of the datafile present in the tablespace UNDO01SQL> select status, name, file# from v$datafile where ts# in (Select ts# from v$tablespace where name='UNDO01' ); STATUS NAME FILE# ------- -------------------------------------------------- ---------- ONLINE /u01/undo01_01.dbf 56 RECOVER /u02/undo01_03.dbf 77So clearly one file is in Recover statusOption a
======= If the database is in Archive log mode and you have all the required archive log mode you can do the following :- Find if you have all the required Archive logs on disk or If using Rman ensure they exist in the backup Query 1 ---------SQL> Select checkpoint_change# from v$datafile_header where file#=; Now find these changes are present in which Archive log Query 2 ---------SQL> select sequence#,thread#,name from v$archived_log
wherebetween first_change# and next_change# ; Ensure you have all the archive logs starting from this sequence# till the current sequence# in your database For example ==========SQL> select checkpoint_change#,file#,status from v$datafile_header where file#=77; CHECKPOINT_CHANGE# FILE# STATUS ------------------ ---------- ------- 2103113 4 OFFLINE 77 SQL>Select sequence#,thread#,name from v$archived_log where 2103113 between first_change# and next_change# ; SEQUENCE# THREAD# NAME -------------------------------------------------------------------------------- 96 1 /u01/arch/O1_MF_1_96_6OKHP.ArcIf using rmanCheck if the archive log from this sequence till current sequence is available RMAN> list backup of archivelog from sequenceRMAN> recover datafile ; RMAN> sql 'alter database datafile online' ; if using sqlplus ------------- Ensure the archive logs are present on disk SQL> recover datafile ; Type AUTO and hit enter Once recovery is done SQL> alter database datafile online ; If the archive logs have been restored to a different location than the Default archive log destination your database is using then specify the same using set source command in sqlplus SQL> set logsource "/u01/arch/newlocation" ; SQL> recover datafile ; Type AUTO and hit enter Once recovery is done SQL> alter database datafile online ; Option b
========= If database is in No archive log mode and the redo log has been reused Open a ticket with oracle Support and explore the options You can Upload the following trace file while opening the ticketSQL> alter session set tracefile_identifier='corrupt'; SQL> alter system dump undo header ""; Go to udump ls -lrt *corrupt* Upload this trace file Also upload the alert log filePlease note :- Donot set any underscore or unsupported parameters to drop an active Undo segment as it might causes dictionary inconsistency.Please open a ticket with oracle Support for any queries related to the sameHow to quickly check that Database is consistent after incomplete recovery (Point in Time Recovery) before OPEN RESETLOGS (Doc ID 1354256.1)APPLIES TO:
Oracle Database - Enterprise Edition - Version 9.0.1.0 to 11.2.0.2 [Release 9.0.1 to 11.2] Information in this document applies to any platform. ***Checked for relevance on 15-Feb-2013***GOAL
After performing a RESTORE / RECOVER, desire to perform quick validation checks to ensure database is consistent and ready for OPEN RESETLOGS. This proactive check helps to prevent several issues which may appear during or after OPEN RESETLOGS.There can be more scenarios than discussed here. Please consult Oracle Support when in doubt.SOLUTION
At first, It's helpful to enable the session to display the timestamp for DATE type columns :SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS' ;Check 1:
Objective: Verify that the datafiles are recovered to the intended point in time (PIT) and they are consistent (FUZZY=NO) Query the current status and PIT (P-oint I-n T-ime upto which the datafiles have been recovered) of datafiles by reading datafile headers directly from the physical datafiles:SQL> select fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*) from v$datafile_header group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time ; FUZ STATUS ERROR REC CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*) --- ------- --------------- --- ------------------ -------------------- ---------- NO ONLINE 5311260 31-AUG-2011 23:10:14 6 YES ONLINE 5311260 31-AUG-2011 23:10:14 1+ Verify that the checkpoint_time / checkpoint_change# is in line with your intended UNTIL TIME/SCN. If not, recover the database further if you have more archived logs available. + If FUZZY=YES for some datafiles, it means more recovery is required. If no more archived logs are available, identify such datafiles and determine if we can take them offline because we will loose the data in those datafiles. If the datafiles belong to SYSTEM or UNDO tablespace, we can / MUST not bring such datafile offline without proper analysis. Please consult Oracle Support for further actions.SQL> select file#, substr(name, 1, 50), substr(tablespace_name, 1, 15), undo_opt_current_change# from v$datafile_header where fuzzy='YES' ; FILE# SUBSTR(NAME,1,50) SUBSTR(TABLESPA UNDO_OPT_CURRENT_CHANGE# ---------- -------------------------------------------------- --------------- ------------------------ 3 /u01/app/oracle/oradata/prod111/undotbs01.dbf UNDOTBS1 5117431Occasionally, if the tablespace name doesn't indicate it to be UNDO tablespace, if we see non-zero value in column UNDO_OPT_CURRENT_CHANGE#, it indicates that the datafile contains undo segments. To bring a datafile offline :SQL> alter database datafileoffline ; Check 1 can be considered Passed when : + Verified that all the datafiles have been recovered upto the intended Point in time. + Fuzzy=NO for SYSTEM, UNDO and all intended datafiles. For datafiles with Fuzzy=YES, either recover them further or bring them OFFLINE if no further archived logs available.Check 2:
Objective: Verify that the files with status=RECOVER are not OFFLINE unintentionallySQL> select status, enabled, count(*) from v$datafile group by status, enabled ; STATUS ENABLED COUNT(*) ------- ---------- ---------- SYSTEM DISABLED 1 ONLINE READ WRITE 4 RECOVER DISABLED 2If the files are in RECOVER status, verify if they are OFFLINE :SQL> select file#, substr(name, 1, 50), status, error, recover from v$datafile_header ;If you want the data for these files to be accessible, then bring them ONLINE :SQL> alter database datafileONLINE ; If a file remains offline at the time of OPEN RESETLOGS, the datafile may not be brought back online again in the same OPENED database. Check 2 can be considered Passed when: a) All the intended datafiles are not OFFLINECheck 3:
Objective: Additional Fuzzy check (Absolute Fuzzy check) Occasionally, it is possible to see Fuzzy=NO and same checkpoint_change# for all the intended datafiles ; still some of the datafiles might be fuzzy and OPEN RESETLOGS will return error, e.g.SQL> select fuzzy, status, error, recover, checkpoint_change#, checkpoint_time, count(*) from v$datafile_header group by fuzzy, status, error, recover, checkpoint_change#, checkpoint_time ; FUZ STATUS ERROR REC CHECKPOINT_CHANGE# CHECKPOINT_TIME COUNT(*) --- ------- --------------- --- ------------------ -------------------- ---------- NO ONLINE 5311260 31-AUG-2011 23:10:14 7 SQL> ALTER DATABASE OPEN RESETLOGS ; ORA-01194: file 4 needs more recovery to be consistent ORA-01110: data file 3: '/u01/app/oracle/oradata/prod111/undotbs02.dbf'Hence, we should perform additional fuzzy check known as Absolute Fuzzy Check:SQL> select hxfil file#, substr(hxfnm, 1, 50) name, fhscn checkpoint_change#, fhafs Absolute_Fuzzy_SCN, max(fhafs) over () Min_PIT_SCN from x$kcvfh where fhafs!=0 ; FILE# NAME CHECKPOINT_CHANG ABSOLUTE_FUZZY_S MIN_PIT_SCN ---------- -------------------------------------------------- ---------------- ---------------- ---------------- 4 /u01/app/oracle/oradata/prod111/undotbs01.dbf 5311260 5311524 5311524 6 /u01/app/oracle/oradata/prod111/system01.dbf 5311260 5311379 5311524Note: Column Min_PIT_SCN will return same value even for multiple rows as we have applied ANALYTICAL "MAX() OVER ()" function on it.Above query indicates that the recovery must be performed at least UNTIL SCN 5311524 to make datafiles consistent and ready to OPEN. Since the checkpoint_change# is smaller than Min_PIT_SCN, the datafiles will ask for more recovery. Check 3 can be considered Passed when, a) No rows selected from above query (i.e. Min_PIT_SCN is 0 (Zero) for all the datafiles) b) Min_PIT_SCN is returned less than Checkpoint_Change#Check 4 (After successful OPEN RESETLOGS) :
Monitor the alert.log for the time of OPEN RESETLOGS activities. You might see some messages like below during dictionary check:Dictionary check beginning Tablespace 'TEMP' #3 found in data dictionary, <(============================== (1) but not in the controlfile. Adding to controlfile. Tablespace 'USERS' #4 found in data dictionary, but not in the controlfile. Adding to controlfile. File #4 found in data dictionary but not in controlfile. Creating OFFLINE file 'MISSING00004' in the controlfile. <(==================== (2) File #5 is online, but is part of an offline tablespace. <(==================== (3) data file 5: '/u01/app/oracle/oradata/prod111/example01.dbf' File #7 found in data dictionary but not in controlfile. <(==================== (2) Creating OFFLINE file 'MISSING00007' in the controlfile. File #8 is offline, but is part of an online tablespace. <(==================== (4) data file 8: '/u01/app/oracle/oradata/prod111/mydata02.dbf' File #9 is online, but is part of an offline tablespace. <(==================== (3) data file 9: '/u01/app/oracle/oradata/prod111/example02.dbf' Dictionary check completeWe discuss below the points highlighted :(1) Check if the temp files exist. If not, add them as per your preference:SQL> select file#, name from v$tempfile ; no rows selected SQL> select file#, name from dba_temp_files ; no rows selected SQL> select tablespace_name, status, contents from dba_tablespaces where contents='TEMPORARY' ; TABLESPACE_NAME STATUS CONTENTS ------------------------------ --------- --------- TEMP ONLINE TEMPORARY SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/temp01.dbf' size 10m ; Tablespace altered. SQL> select file#, substr(name, 1, 50), status, enabled from v$tempfile FILE# SUBSTR(NAME,1,50) STATUS ENABLED -------- -------------------------------------------------- ------- ---------- 1 /u01/app/oracle/oradata/temp01.dbf ONLINE READ WRITE(2) It appears that the tablespace was brought offline using "ALTER TABLESPACE USERS OFFLINE" command. So, verify if the missing files really exist with original name. You may need to consult your pear DBAs, or refer alert.log / RMAN backup log or any such information which may provide clue about the actual file name. If you find the file, try to rename them. If not, we can offline the datafile or drop associated tablespace:SQL> select file#, status, enabled, substr(name, 1, 50) from v$datafile where name like '%MISSING%' ; FILE# STATUS ENABLED SUBSTR(NAME,1,50) -------- ------- ---------- -------------------------------------------------- 4 OFFLINE DISABLED /u01/app/oracle/product/11.1.0/db_1/dbs/MISSING000 7 OFFLINE DISABLED /u01/app/oracle/product/11.1.0/db_1/dbs/MISSING000 SQL> alter database datafile 4 online ; alter database datafile 4 online * ERROR at line 1: ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01111: name for data file 4 is unknown - rename to correct file ORA-01110: data file 4: '/u01/app/oracle/product/11.1.0/db_1/dbs/MISSING00004' SQL> alter database rename file 'MISSING00004' to '/u01/app/oracle/oradata/prod111/users01.dbf' ; Database altered. SQL> alter database rename file 'MISSING00007' to '/u01/app/oracle/oradata/prod111/users02.dbf' ; Database altered. SQL> select tablespace_name, status from dba_tablespaces where tablespace_name in (select tablespace_name from dba_data_files where file_id in (4, 7)) ; TABLESPACE_NAME STATUS ------------------------------ --------- USERS OFFLINE SQL> ALTER TABLESPACE USERS ONLINE ; Tablespace altered.Before proceedig further, let's query the status for these files in alert.log:SQL> select a.file#, substr(a.name, 1, 50) file_name, a.status file_status, a.error, substr(a.tablespace_name, 1, 10) tablespace_name, b.status tablespace_status from v$datafile_header a, dba_tablespaces b where a.tablespace_name=b.tablespace_name /* and a.file# in (4, 5, 7, 8, 9) */ ;
FILE# FILE_NAME FILE_STATUS ERROR TABLESPA TABLESPACE_STATUS ----- --------------------------------------------- ----------- --------------- -------- ------------------ 1 /u01/app/oracle/oradata/prod111/system01.dbf ONLINE SYSTEM ONLINE 2 /u01/app/oracle/oradata/prod111/sysaux01.dbf ONLINE SYSAUX ONLINE 3 /u01/app/oracle/oradata/prod111/undotbs01.dbf ONLINE UNDOTBS1 ONLINE 4 /u01/app/oracle/oradata/prod111/users01.dbf OFFLINE OFFLINE NORMAL USERS OFFLINE <(== related to (2) in alert.log excerpt above 5 /u01/app/oracle/oradata/prod111/example01.dbf ONLINE EXAMPLE OFFLINE <(== related to (3) in alert.log excerpt above 6 /u01/app/oracle/oradata/prod111/mydata01.dbf ONLINE MYDATA ONLINE 7 /u01/app/oracle/oradata/prod111/users02.dbf OFFLINE OFFLINE NORMAL USERS OFFLINE <(== related to (2) in alert.log excerpt above 8 /u01/app/oracle/oradata/prod111/mydata02.dbf OFFLINE WRONG RESETLOGS MYDATA ONLINE <(=== related to (4) in alert.log excerpt above 9 /u01/app/oracle/oradata/prod111/example02.dbf ONLINE EXAMPLE OFFLINE <(== related to (3) in alert.log excerpt above
9 rows selected.So, we can attempt to correct the "ERROR" as displayed in above query depending on the availability of file / archived logs and other possible factors. Let's continue,(3) It seems that tablespace was brought offline inconsistently ( ALTER TABLESPACE EXAMPLE OFFLINE IMMEDIATE ). If the archived log generated at that time has got applied, the file may be back online :SQL> alter tablespace example ONLINE ; Tablespace altered.(4) This tablespace MYDATA has 2 datafiles File# 6 & 8. It appears that File# 8 was brought offline ( using ALTER DATABASE DATAFILE 8 OFFLINE ) and it was OFFLINE before OPEN RESETLOGS. If the archived log generated at that time has got applied during recovery or all the archived logs are available for recovery since that time, the file may be back online :SQL> alter database datafile 8 online ; alter database datafile 8 online * ERROR at line 1: ORA-01190: control file or data file 8 is from before the last RESETLOGS ORA-01110: data file 8: '/u01/app/oracle/oradata/prod111/mydata02.dbf' SQL> alter tablespace mydata online ; alter tablespace mydata online * ERROR at line 1: ORA-01190: control file or data file 8 is from before the last RESETLOGS ORA-01110: data file 8: '/u01/app/oracle/oradata/prod111/mydata02.dbf' SQL> recover datafile 8 ; Media recovery complete. SQL> alter database datafile 8 online ; Database altered. SQL> alter tablespace mydata online ; Tablespace altered.Please note that it is not always possible to recover and bring the file online which is failing with error " ORA-01190: control file or data file x is from before the last RESETLOGS". (5) There can be a scenario where the tablespace was in READ ONLY mode before OPEN RESETLOGS. Please check below Article on that: Note 266991.1 Recovering READONLY tablespace backups made before a RESETLOGS OpenREFERENCES
NOTE:266991.1 - Recovering READONLY tablespace backups made before a RESETLOGS OpenHow to recover and open the database if the archivelog required for recovery is either missing, lost or corrupted? (Doc ID 465478.1)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.
select status, checkpoint_change#,
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.
The results of the query above may return some offline datafiles. So, ensure that all of the required datafiles are online, because we may not be able to recover later the offline datafile once we open the database in resetlogs. Even though we can recover the database beyond resetlogs for the Oracle database starting from 10g and later versions due to the introduction of the format "%R" in the LOG_ARCHIVE_FORMAT, it is recommended that you online the required datafiles now than after the database is open in resetlogs to avoid any possible problems. However, in some cases, we intentionally offline the datafile(s), because we are doing a partial database restore, or perhaps we don't need the contents of the said datafile.
You may run the following query to determine the offline datafiles:
select file#, name from v$datafile
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":
alter database datafile 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:
set echo on feedback on pagesize 100 numwidth 16
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:
select * from v$logfile;
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-00279: change 189189555 generated at 11/03/2007 09:27:46 needed for thread 1
ORA-00289: suggestion : +BACKUP
ORA-00280: change 189189555 for thread 1 is in sequence #428
Specify log: {=suggested | filename | AUTO | CANCEL}
+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.
UNCOMMITTED DATA IN DATAFILES
Are you looking to earn cash from your websites or blogs with popunder advertisments?
ReplyDeleteIn case you are, have you tried using Clickadu?
No, this blog only for my reference and knowledge purpose..
ReplyDeleteall information took from Oracle meta-link. it will not use commercial purpose.