The approach to be followed depends on the specific scenario in which
the loss of the rollback datafile is detected:
I. THE DATABASE IS DOWN
-----------------------
Attempting to startup the database will result in ORA-1157 and ORA-1110.
The solution here depends on whether the database was cleanly shut down
or not.
I.A. THE DATABASE WAS CLEANLY SHUT DOWN
---------------------------------------
If you are ABSOLUTELY POSITIVE that the database was cleanly shutdown,
i.e., it was closed with either shutdown NORMAL or IMMEDIATE, then
the simplest solution is to offline drop the missing datafile, open the
database in restricted mode, and then drop and recreate the undo
tablespace to which the file belonged. DO NOT follow this procedure
if the database was shut down ABORT or if it crashed.
The steps are:
1. Make sure the database was last cleanly shut down.
Check the alert.log file for this instance. Go to the bottom of
the file and make sure the last time you shut the database down
you got the messages:
"Shutting down instance (immediate)"
OR
"alter database close normal
Completed: alter database close normal"
This also includes the case of a clean shutdown followed by a
failed attempt to startup the database. In that case, Oracle will
issue error messages and shut itself down abort. For the purposes
of this solution, though, this counts as a clean shutdown.
If that is not the case, i.e., if the last time YOU shut the database
down it was in abort mode, or the database crashed itself, it is
NOT safe to proceed. You should follow the instructions for
case I.B below.
2. If using automatic UNDO_MANAGEMENT, comment out this entry from the parameter
file, or set it to MANUAL.
If using rollback segments, remove all the rollback segments in the
tablespace to which the lost datafile belongs from the ROLLBACK_SEGMENTS
parameter in the init.ora file for this instance. If you are not sure about which rollbacks are
in that tablespace, simply comment out the whole ROLLBACK_SEGMENTS entry.
3. Mount the database in restricted mode.
SQL> STARTUP RESTRICT MOUNT
4. Offline drop the lost datafile.
SQL> ALTER DATABASE DATAFILE '<full_path_file_name>' OFFLINE DROP;
5. Open the database.
SQL> ALTER DATABASE OPEN
You should receive the message "Statement processed,".
If instead you get ORA-604, ORA-376, and ORA-1110, it is likely the shutdown
was not normal/immediate. Review the rest of the options available and/or
contact Oracle Support Services.
6. Drop the undo tablespace or tablespace which contains rollback segments
to which the datafile belonged.
SQL> DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS;
7. Recreate the undo tablespace. If using rollback segments, recreate the
rollback segment tablespace and all it's rollback segments. Remember to
bring the rollbacks online after you create them.
8. Edit the parameter file setting:
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=<new undo tablespace name>
If using rollback segments, reinclude the rollbacks you just recreated in
the ROLLBACK_SEGMENTS parameter in the init.ora file for this instance.
As rollback segments were brought online in step #7, no need to proceed
with shutdown/startup as needed for undo tablespace. All that is required
is:
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
9. If using UNDO tablespace, Shutdown and startup the the database.
I.B. THE DATABASE WAS NOT CLEANLY SHUT DOWN
-------------------------------------------
This is the situation where the database was last shut down abort or crashed.
In this case, it is almost certain that the rollback segments that had
extents in the lost datafile still contain active transactions. Therefore,
the file cannot be offlined or dropped. You must restore the lost datafile
from a backup and apply media recovery to it. If the database is in
NOARCHIVELOG mode, you will only succeed in recovering the datafile if the
redo to be applied is within the range of your online logs. Otherwise, the
last full backup must be restored. If a backup of the datafile is not
available, please contact Oracle Support Services.
These are the steps:
1. Restore the lost file from a backup.
2. Mount the database:
SQL> STARTUP MOUNT;
3. Issue the following query:
SQL> SELECT FILE#, NAME, STATUS FROM V$DATAFILE;
If the status of the file you just restored is "OFFLINE," you must
online it before proceeding:
SQL> ALTER DATABASE DATAFILE '<full_path_file_name>' ONLINE;
4. Issue the following query:
SQL> SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# ;
This will list all your online redolog files and their respective
sequence and first change numbers.
5. If the database is in NOARCHIVELOG mode, issue the query:
SQL> SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;
If the CHANGE# is GREATER than the minimum FIRST_CHANGE# of your
logs, the datafile can be recovered. Just keep in mind that all the
logs to be applied will be online logs, and move on to step 6.
If the CHANGE# is LESSER than the minimum FIRST_CHANGE# of your logs,
the file cannot be recovered. Your options at this point include
restoring a full backup if one is available or forcing the database
to open in an inconsistent state to get a full export out of it.
For further details and to assist you in your decision, please contact
Oracle Support Services.
6. Recover the datafile:
SQL> RECOVER DATAFILE '<full_path_file_name>'
7. Confirm each of the logs that you are prompted for until you receive
the message "Media recovery complete". If you are prompted for a
non-existing archived log, Oracle probably needs one or more of the
online logs to proceed with the recovery. Compare the sequence number
referenced in the ORA-280 message with the sequence numbers of your online
logs. Then enter the full path name of one of the members of the redo group
whose sequence number matches the one you are being asked for. Keep entering
online logs as requested until you receive the message "Media recovery
complete".
8. Open the database:
SQL> ALTER DATABASE OPEN;
II. THE DATABASE IS UP
----------------------
If you have detected the loss of the rollback datafile and the database
is still up and running, DO NOT SHUT IT DOWN. In most cases, it is
simpler to solve this problem with the database up than with it down.
Two approaches are possible in this scenario:
A) The first approach involves creating a new UNDO (or rollback segment)
tablespace, altering the system to use the new and dropping the old.
B) The other approach involves offlining the lost datafile, restoring it from
backup, and then applying media recovery to it to make it consistent
with the rest of the database. This method can only be used if
the database is in ARCHIVELOG mode.
In general, approach IIA is simpler to try first, however, depending on
activity and undo usage, the drop of the older tablespace may result
in error regarding active transactions. There are a few things to try to
get the older tablespace to drop, but if all fail, approach IIB may be required.
APPROACH II.A: RECREATING THE ROLLBACK TABLESPACE
-------------------------------------------------
This approach can be used regardless of the archival mode of the database.
The steps are:
For undo tablespaces:
.....................
1. Create a new undo tablespace:
SQL> CREATE UNDO TABLESPACE <new undo tablepace name>
DATAFILE '<new location and name of datafile>' SIZE ....;
2. Alter the system to use the new undo tablespace:
SQL> ALTER SYSTEM SET UNDO_TABLESPACE='<new undo tablepace name>';
At this point, all new undo will be allocated in undo segments managed within
the UNDO_TBS2 tablespace.
3. Try dropping older undo tablespace:
SQL> DROP TABLESPACE <old undo tablespace name> INCLUDING CONTENTS;
If this succeeds, you are done. If it returns an error, please see
"Handling errors dropping undo/rollback segments".
For rollback segment tablespaces:
.................................
1. Create new tablespace to house rollback segments. For spacing and extent
sizing, see information in dba_tablespaces for old rollback segment tablespace.
SQL> CREATE TABLESPACE .....
2. Create rollback segments in this tablespace
SQL> CREATE ROLLBACK SEGMENT.....
3. Online the rollback segments in this new tablespace:
SQL> ALTER ROLLBACK SEGMENT <new_rollback_segment> ONLINE;
Repeat for all rollback segments.
4. Try to offline all the rollback segments in the tablespace to which
the lost datafile belongs.
SQL> ALTER ROLLBACK SEGMENT <rollback_segment> OFFLINE;
Repeat this statement for all rollbacks in the tablespace.
5. Check the status of the rollbacks.
They must all be offline before they can be dropped. Issue the query:
SQL> SELECT SEGMENT_NAME, STATUS FROM DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME = '<TABLESPACE_NAME>';
6. Drop all offlined rollback segments.
For each rollback returned by the query in step 2 with status
"OFFLINE," issue the statement:
SQL> DROP ROLLBACK SEGMENT <rollback_segment>;
7. Drop the rollback segment tablespace:
SQL> DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS;
If this succeeds, you are done. If it returns an error, please see
"Handling errors dropping undo/rollback segments".
HANDLING ERRORS DROPPING UNDO/ROLLBACK SEGMENTS:
-----------------------------------------------
NOTE, although automatic undo and undo tablespace is used by the database,
the following procedure is the same. The only difference is that the segment
names are assigned by Oracle.
1. Check for any rollback segments online.
SQL> SELECT SEGMENT_NAME, STATUS FROM DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME = '<OLD TABLESPACE_NAME>';
If any of the rollbacks you tried to offline still has an "ONLINE" status,
this is usually an indication that this segment contains active transactions.
2. Check for active transactions with the following query:
SQL> SELECT SEGMENT_NAME, XACTS ACTIVE_TX, V.STATUS
FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME = '<TABLESPACE_NAME>' AND SEGMENT_ID = USN;
If the above query returns no rows, it means all the rollbacks in
the affected tablespace are already offline. Repeat the query in
step 1 to retrieve the names of the rollbacks that just became
offline and attempt to drop the undo tablespace or individual
rollback segments as described above.
If the above query returns one or more rows, they should show
status "PENDING OFFLINE". Next, check the ACTIVE_TX
column for each rollback. If it has a value of 0, it implies
there are no pending transactions left in the rollback, and it
should go offline shortly. Repeat the query in step 1 a few
more times until it shows the rollback segments being offline and
then attempt the drop again. Once the drop is successful, you are done.
If any of the "pending offline" rollbacks has a value of 1 or
greater in the ACTIVE_TX column, move on to step 3.
3. Force rollbacks with active transactions to go offline.
At this point, the only way to move forward is to have the
"pending offline" rollbacks released. The active transactions
in these rollbacks must either be committed or rolled back.
The following query shows which users have transactions assigned
to which rollbacks:
SQL> SELECT S.SID, S.SERIAL#, S.USERNAME, R.NAME "ROLLBACK"
FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME R
WHERE R.NAME IN ('<PENDING_ROLLBACK_1>', ... , '<PENDING_ROLLBACK_N>')
AND S.TADDR = T.ADDR AND T.XIDUSN = R.USN;
You may directly contact the users with transactions in the
"pending offline" rollbacks and ask them to commit (preferably)
or rollback immediately. If that is not feasible, you can force
that to happen by killing their sessions. For each of the entries
returned by the above query, issue the statement:
SQL> ALTER SYSTEM KILL SESSION '<SID>, <SERIAL#>';
where <SID> and <SERIAL#> are those returned by the previous
query. After the sessions are killed, it may take a few minutes
before Oracle finishes rolling back and doing cleanup work. Go back
to step 1 and repeat the query in there periodically until all
rollbacks in the affected tablespace are offline and ready to be
dropped.
If you are unable to drop the older undo tablespace or rollback segments,
try to restore the file from backup and recover it fully (approach II.B).
Once the file is recovered try to drop the older undo tablespace. If this
is not possible, contact Oracle Support Services.
APPROACH II.B: RESTORING THE DATAFILE FROM BACKUP
-------------------------------------------------
As mentioned before, this approach can only be followed if the database is
in ARCHIVELOG mode. Here are the steps:
1. Offline the lost datafile.
SQL> ALTER DATABASE DATAFILE '<full_path_file_name>' OFFLINE;
NOTE: Depending on the current amount of database activity,
you may have to create additional rollback segments in a different
tablespace to keep the database going while you take care of the problem.
2. Restore the datafile from a backup.
3. Issue the following query:
SQL> SELECT V1.GROUP#, MEMBER, SEQUENCE#
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# ;
This will list all your online redolog files and their respective
sequence numbers.
4. Recover the datafile:
SQL> RECOVER DATAFILE '<full_path_file_name>'
5. Confirm each of the logs that you are prompted for until you receive
the message "Media recovery complete". If you are prompted for a
non-existing archived log, Oracle probably needs one or more of the
online logs to proceed with the recovery. Compare the sequence number
referenced in the ORA-280 message with the sequence numbers of your online
logs. Then enter the full path name of one of the members of the redo group
whose sequence number matches the one you are being asked for. Keep
entering online logs as requested until you receive the message
"Media recovery complete".
6. Bring the datafile back online.
SQL> ALTER DATABASE DATAFILE '<full_path_file_name>' ONLINE
Saturday, 28 September 2013
RECOVERING FROM A LOST DATAFILE IN A UNDO TABLESPACE
Friday, 27 September 2013
Dataguard Issues - How to resolve ORA-16792
If the
setting of dataguard related parameter is different between database and
the broker, when doing the show configuration within DGMGRL, the
following error will appear:
DGMGRL> show configuration;
Configuration
Name: DGA24L
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
DGA24L - Primary database
DGA24LPS - Physical standby database
Current status for "DGA24L":
Warning: ORA-16608: one or more databases have warnings
DGMGRL> show database verbose 'DGA24L';
Database
Name: DGA24L
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
DGA24L
Properties:
...
o find out what is the differences, by using this command:
DGMGRL> show database '<db_name>' 'InconsistentProperties';
For example, the inconsistent is this parameter log_archive_trace
Either change the value using sqlplus or DGMGRL to make it consistent.
or
DGMGRL> show configuration;
Configuration
Name: DGA24L
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
DGA24L - Primary database
DGA24LPS - Physical standby database
Current status for "DGA24L":
Warning: ORA-16608: one or more databases have warnings
DGMGRL> show database verbose 'DGA24L';
Database
Name: DGA24L
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
DGA24L
Properties:
...
o find out what is the differences, by using this command:
DGMGRL> show database '<db_name>' 'InconsistentProperties';
DGMGRL> show database 'DGA24L' 'InconsistentProperties';
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
DGA24L LogArchiveTrace 1 513 513For example, the inconsistent is this parameter log_archive_trace
Either change the value using sqlplus or DGMGRL to make it consistent.
1. sqlplus / as sysdba
SQL> alter system set log_archive_trace=513;or
2. DGMGRL
DGMGRL> edit database '<db_name>' set property 'LogArchiveTrace'=1;Wednesday, 25 September 2013
How to quickly check that Database is consistent after incomplete recovery (Point in Time Recovery) before OPEN RESETLOGS
At first, It's helpful to enable the session to display the timestamp for DATE type columns :
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:
+ 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.
Occasionally, 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 :
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.
If the files are in RECOVER status, verify if they are OFFLINE :
If you want the data for these files to be accessible, then bring them ONLINE :
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 OFFLINE
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.
Hence, we should perform additional fuzzy check known as Absolute Fuzzy Check:
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#
Monitor the alert.log for the time of OPEN RESETLOGS activities. You
might see some messages like below during dictionary check:
We discuss below the points highlighted :
(1) Check if the temp files exist. If not, add them as per your preference:
(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:
Before proceedig further, let's query the status for these files in alert.log:
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 :
(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 :
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".
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
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 5117431
FILE# SUBSTR(NAME,1,50) SUBSTR(TABLESPA UNDO_OPT_CURRENT_CHANGE#
---------- -------------------------------------------------- --------------- ------------------------
3 /u01/app/oracle/oradata/prod111/undotbs01.dbf UNDOTBS1 5117431
Occasionally, 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 datafile <file#> offline ;
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 unintentionally
SQL> select status, enabled, count(*) from v$datafile group by status, enabled ;
STATUS ENABLED COUNT(*)
------- ---------- ----------
SYSTEM DISABLED 1
ONLINE READ WRITE 4
RECOVER DISABLED 2
STATUS ENABLED COUNT(*)
------- ---------- ----------
SYSTEM DISABLED 1
ONLINE READ WRITE 4
RECOVER DISABLED 2
If 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 datafile <file#> ONLINE ;
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 OFFLINE
Check 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'
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 5311524
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 5311524
Note: 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 complete
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 complete
We 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
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.
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.
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.
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.
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".
Subscribe to:
Posts (Atom)