Problem Symptoms
After performing shutdown abort whenever I try to start my database it fails to perform crash recovery. Below is from alert log.
Wed May 21 07:11:43 2008
Errors in file /ora/udump/arju_ora_12424.trc:
ORA-01115: IO error reading block from file 2 (block # 11546)
ORA-01110: data file 2: '/ora/data/data01/arju_undotbs01.dbf'
ORA-27067: size of I/O buffer is invalid
Additional information: 2
Additional information: 1245184
Cause of the Problem
This is oracle bug. It fired whenever someone gave very large insert operation and during that time issued shutdown abort.
Solution of the Problem
Just perform normal recovery. To do so,
1)$sqlplus / as sysdba
2)startup mount;
3)recover database;
4)alter database open;
Showing posts with label Recovery Problems. Show all posts
Showing posts with label Recovery Problems. Show all posts
Wednesday, September 24, 2008
Thursday, May 22, 2008
Allow Recovery to Corrupt Blocks
Whenever during recovery database finds corrupt block then recovery will automatically stops. In order to do recovery with skipping corrupt blocks run the RECOVER command with the ALLOW n CORRUPTION clause, where n is the number of allowable corrupt blocks.
Then database will allow recovery and mark the problem block as corrupt. However it should not do if problem block is is under SYSTEM tablespace.
In order to allow a single corruption just enter,
SQL>RECOVER DATABASE ALLOW 1 CORRUPTION;
Whenever there is a problem in corrupt block then the object id will be recorded inside alert log file. You can usually query the database to find out which object or table owns this block. To do so, cancel recovery and open the database read only.
CANCEL
SQL>ALTER DATABASE OPEN READ ONLY;
Now determine the object name by getting data object id from alert log. Suppose is it 1230
SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS
WHERE DATA_OBJECT_ID =1230;
Now you should perform trial recovery RECOVER ... TEST and see whether problem is isolated or not. If it is not isolated and if it is isolated but not contain SYSTEM data blocks then you can open the database with RESETLOGS options.
Related Documents:
-------------------------------
Block Media Recovery with RMAN
Extract data from corrupt block
Verify Physical data Structure
Then database will allow recovery and mark the problem block as corrupt. However it should not do if problem block is is under SYSTEM tablespace.
In order to allow a single corruption just enter,
SQL>RECOVER DATABASE ALLOW 1 CORRUPTION;
Whenever there is a problem in corrupt block then the object id will be recorded inside alert log file. You can usually query the database to find out which object or table owns this block. To do so, cancel recovery and open the database read only.
CANCEL
SQL>ALTER DATABASE OPEN READ ONLY;
Now determine the object name by getting data object id from alert log. Suppose is it 1230
SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS
WHERE DATA_OBJECT_ID =1230;
Now you should perform trial recovery RECOVER ... TEST and see whether problem is isolated or not. If it is not isolated and if it is isolated but not contain SYSTEM data blocks then you can open the database with RESETLOGS options.
Related Documents:
-------------------------------
Block Media Recovery with RMAN
Extract data from corrupt block
Verify Physical data Structure
Tuesday, May 20, 2008
Recovering Through an Added Datafile with a Backup Control File
If database recovery with a backup control file rolls forward through a CREATE TABLESPACE or an ALTER TABLESPACE ADD DATAFILE operation, then the database stops recovery when applying the redo record for the added files and lets you confirm the filenames.
In this example I illustrate this behavior and how to solve the problem.
A)Backup The Data file.
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;
FILE_NAME
----------------------------------------
/oradata2/data1/dbase/sysaux01.dbf
/oradata2/data1/dbase/undotbs01.dbf
/oradata2/data1/dbase/system01.dbf
/oradata2/data1/dbase/user01.dbf
SQL> ALTER DATABASE BEGIN BACKUP;
Database altered.
SQL> !cp /oradata2/data1/dbase/sysaux01.dbf /oradata2/arju/
SQL> !cp /oradata2/data1/dbase/undotbs01.dbf /oradata2/arju/
SQL> !cp /oradata2/data1/dbase/system01.dbf /oradata2/arju/
SQL> !cp /oradata2/data1/dbase/user01.dbf /oradata2/arju/
SQL> ALTER DATABASE END BACKUP;
Database altered.
B)Backup the control file.
SQL> alter database backup controlfile to '/oradata2/arju/control.ctl';
Database altered.
Archive the unarchived redo logs so that the redo required to recover the tablespace backup is archived. For example, enter:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
C)Create a Tablespace with two datafiles.
SQL> CREATE TABLESPACE TBS_AFTER_BACKUP DATAFILE '/oradata2/data1/dbase/datafile01.dbf' size 1M, '/oradata2/data1/dbase/datafile02.dbf' size 1M;
Tablespace created.
D)Remove the All copies of control files.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /oradata1/arju/control01.ctl,
/oradata1/arju/control02.ctl
SQL> !rm /oradata1/arju/control01.ctl
SQL> !rm /oradata1/arju/control02.ctl
E)Shutdown and Restore Control file from Backup
SQL> shutdown abort
ORACLE instance shut down.
SQL> !cp /oradata2/arju/control.ctl /oradata1/arju/control01.ctl
SQL> !cp /oradata2/arju/control.ctl /oradata1/arju/control02.ctl
Since these restored control files are before adding tablespace so the datafile information of the latest added tablespace will not be here. So when it will try to apply redo of the new datafile it will generate error.
F)Startup the instance and Try to recover database.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.
Here I am performing automatic recovery.
SQL> RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: '/oradata2/data1/dbase/datafile02.dbf'
ORA-01110: data file 4: '/oradata2/data1/dbase/datafile01.dbf'
G)View the files added by selecting from V$DATAFILE.
SQL> SELECT FILE#, NAME FROM V$DATAFILE;
FILE# NAME
--------------------------------------------------------------------------
1 /oradata2/data1/dbase/system01.dbf
2 /oradata2/data1/dbase/undotbs01.dbf
3 /oradata2/data1/dbase/sysaux01.dbf
4 /oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00004
5 /oradata2/data1/dbase/user01.dbf
6 /oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006
6 rows selected.
H)Rename unnamed datafiles to original names.
If multiple versions of unnamed file exists the identify them from alert log or derive the original file location of each unnamed file from the error message and V$DATAFILE.
SQL> ALTER DATABASE RENAME FILE '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00004' TO '/oradata2/data1/dbase/datafile01.dbf';
Database altered.
SQL> ALTER DATABASE RENAME FILE '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006' TO '/oradata2/data1/dbase/datafile02.dbf';
Database altered.
I)Now perform recovery operations
SQL> RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
ORA-00279: change 1667619 generated at 05/21/2008 03:13:49 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc
ORA-00280: change 1667619 for thread 1 is in sequence #4
ORA-00278: log file
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc'
no longer needed for this recovery
ORA-00308: cannot open archived log
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc'
ORA-27037: unable to obtain file status
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3
Specify log: {=suggested | filename | AUTO | CANCEL}
/oradata2/data1/dbase/redo01.log---SPECIFY REDO LOG MEMBER HERE. QUERY FROM V$LOGFILE
ORA-00310: archived log contains sequence 3; sequence 4 required
ORA-00334: archived log: '/oradata2/data1/dbase/redo01.log'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradata2/data1/dbase/system01.dbf'
As one member fail so provide another member.
SQL> RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
ORA-00279: change 1667619 generated at 05/21/2008 03:13:49 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc
ORA-00280: change 1667619 for thread 1 is in sequence #4
ORA-00278: log file
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc'
no longer needed for this recovery
ORA-00308: cannot open archived log
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc'
ORA-27037: unable to obtain file status
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3
Specify log: {=suggested | filename | AUTO | CANCEL}
/oradata2/data1/dbase/redo03.log---SPECIFY REDO LOG MEMBER HERE. QUERY FROM V$LOGFILE
Log applied.
Media recovery complete.
J)Open the database with RESETLOGS option
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
Related Documents:
User Managed Restore Operation
What you will do if spfile lost Look at Solution of The problem Section
How to solve problem of inconsistent control file.
How to re-create Control file
In this example I illustrate this behavior and how to solve the problem.
A)Backup The Data file.
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;
FILE_NAME
----------------------------------------
/oradata2/data1/dbase/sysaux01.dbf
/oradata2/data1/dbase/undotbs01.dbf
/oradata2/data1/dbase/system01.dbf
/oradata2/data1/dbase/user01.dbf
SQL> ALTER DATABASE BEGIN BACKUP;
Database altered.
SQL> !cp /oradata2/data1/dbase/sysaux01.dbf /oradata2/arju/
SQL> !cp /oradata2/data1/dbase/undotbs01.dbf /oradata2/arju/
SQL> !cp /oradata2/data1/dbase/system01.dbf /oradata2/arju/
SQL> !cp /oradata2/data1/dbase/user01.dbf /oradata2/arju/
SQL> ALTER DATABASE END BACKUP;
Database altered.
B)Backup the control file.
SQL> alter database backup controlfile to '/oradata2/arju/control.ctl';
Database altered.
Archive the unarchived redo logs so that the redo required to recover the tablespace backup is archived. For example, enter:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
C)Create a Tablespace with two datafiles.
SQL> CREATE TABLESPACE TBS_AFTER_BACKUP DATAFILE '/oradata2/data1/dbase/datafile01.dbf' size 1M, '/oradata2/data1/dbase/datafile02.dbf' size 1M;
Tablespace created.
D)Remove the All copies of control files.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /oradata1/arju/control01.ctl,
/oradata1/arju/control02.ctl
SQL> !rm /oradata1/arju/control01.ctl
SQL> !rm /oradata1/arju/control02.ctl
E)Shutdown and Restore Control file from Backup
SQL> shutdown abort
ORACLE instance shut down.
SQL> !cp /oradata2/arju/control.ctl /oradata1/arju/control01.ctl
SQL> !cp /oradata2/arju/control.ctl /oradata1/arju/control02.ctl
Since these restored control files are before adding tablespace so the datafile information of the latest added tablespace will not be here. So when it will try to apply redo of the new datafile it will generate error.
F)Startup the instance and Try to recover database.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.
Here I am performing automatic recovery.
SQL> RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: '/oradata2/data1/dbase/datafile02.dbf'
ORA-01110: data file 4: '/oradata2/data1/dbase/datafile01.dbf'
G)View the files added by selecting from V$DATAFILE.
SQL> SELECT FILE#, NAME FROM V$DATAFILE;
FILE# NAME
--------------------------------------------------------------------------
1 /oradata2/data1/dbase/system01.dbf
2 /oradata2/data1/dbase/undotbs01.dbf
3 /oradata2/data1/dbase/sysaux01.dbf
4 /oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00004
5 /oradata2/data1/dbase/user01.dbf
6 /oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006
6 rows selected.
H)Rename unnamed datafiles to original names.
If multiple versions of unnamed file exists the identify them from alert log or derive the original file location of each unnamed file from the error message and V$DATAFILE.
SQL> ALTER DATABASE RENAME FILE '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00004' TO '/oradata2/data1/dbase/datafile01.dbf';
Database altered.
SQL> ALTER DATABASE RENAME FILE '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006' TO '/oradata2/data1/dbase/datafile02.dbf';
Database altered.
I)Now perform recovery operations
SQL> RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
ORA-00279: change 1667619 generated at 05/21/2008 03:13:49 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc
ORA-00280: change 1667619 for thread 1 is in sequence #4
ORA-00278: log file
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc'
no longer needed for this recovery
ORA-00308: cannot open archived log
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc'
ORA-27037: unable to obtain file status
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3
Specify log: {
/oradata2/data1/dbase/redo01.log---SPECIFY REDO LOG MEMBER HERE. QUERY FROM V$LOGFILE
ORA-00310: archived log contains sequence 3; sequence 4 required
ORA-00334: archived log: '/oradata2/data1/dbase/redo01.log'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradata2/data1/dbase/system01.dbf'
As one member fail so provide another member.
SQL> RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
ORA-00279: change 1667619 generated at 05/21/2008 03:13:49 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc
ORA-00280: change 1667619 for thread 1 is in sequence #4
ORA-00278: log file
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc'
no longer needed for this recovery
ORA-00308: cannot open archived log
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc'
ORA-27037: unable to obtain file status
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3
Specify log: {
/oradata2/data1/dbase/redo03.log---SPECIFY REDO LOG MEMBER HERE. QUERY FROM V$LOGFILE
Log applied.
Media recovery complete.
J)Open the database with RESETLOGS option
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
Related Documents:
User Managed Restore Operation
What you will do if spfile lost Look at Solution of The problem Section
How to solve problem of inconsistent control file.
How to re-create Control file
Monday, May 12, 2008
ORA-01547,ORA-01194,ORA-01589 During Recovery
Error Description:
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 969547 generated at 05/12/2008 23:54:02 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_12/o1_mf_1_4_%u_.arc
ORA-00280: change 969547 for thread 1 is in sequence #4
Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_12/o1_mf_1_4_%u_.arc'
ORA-27037: unable to obtain file status
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_12/o1_mf_1_4_%u_.arc'
ORA-27037: unable to obtain file status
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradata2/data1/dbase/system01.dbf'
Solution of The Problem:
A)Find the datafile(s) which is in FUZZY mode.
SQL> select file#,STATUS, FUZZY from v$datafile_header;
FILE# STATUS FUZ
---------- ------- ---
1 ONLINE YES
2 ONLINE YES
3 ONLINE YES
4 ONLINE YES
5 ONLINE YES
6 ONLINE YES
7 ONLINE YES
8 OFFLINE NO
9 OFFLINE NO
9 rows selected.
So many files need more recovery.
The best solution at this point is to apply more archivelogs in order to get offending datafile/s out of FUZZY mode.
In many cases the recovery session may be requesting an archivelog which has not been generated. The reason for this is because the recovery clause included "using backup controlfile"
If this is the case you need to apply the current online redo log to complete the recovery then
B)Find out the current redo log.
SQL> select member from v$logfile lf , v$log l where l.status='CURRENT' and lf.group#=l.group#;
MEMBER
--------------------------------------------------------------------------------
/oradata2/data1/dbase/redo02.log
C)Now apply this redo log to the database.
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 969547 generated at 05/12/2008 23:54:02 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_12/o1_mf_1_4_%u_.arc
ORA-00280: change 969547 for thread 1 is in sequence #4
Specify log: {=suggested | filename | AUTO | CANCEL}
/oradata2/data1/dbase/redo02.log
Log applied.
Media recovery complete.
D)Open the database with RESETLOGS option.
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 969547 generated at 05/12/2008 23:54:02 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_12/o1_mf_1_4_%u_.arc
ORA-00280: change 969547 for thread 1 is in sequence #4
Specify log: {
AUTO
ORA-00308: cannot open archived log
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_12/o1_mf_1_4_%u_.arc'
ORA-27037: unable to obtain file status
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_12/o1_mf_1_4_%u_.arc'
ORA-27037: unable to obtain file status
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradata2/data1/dbase/system01.dbf'
Solution of The Problem:
A)Find the datafile(s) which is in FUZZY mode.
SQL> select file#,STATUS, FUZZY from v$datafile_header;
FILE# STATUS FUZ
---------- ------- ---
1 ONLINE YES
2 ONLINE YES
3 ONLINE YES
4 ONLINE YES
5 ONLINE YES
6 ONLINE YES
7 ONLINE YES
8 OFFLINE NO
9 OFFLINE NO
9 rows selected.
So many files need more recovery.
The best solution at this point is to apply more archivelogs in order to get offending datafile/s out of FUZZY mode.
In many cases the recovery session may be requesting an archivelog which has not been generated. The reason for this is because the recovery clause included "using backup controlfile"
If this is the case you need to apply the current online redo log to complete the recovery then
B)Find out the current redo log.
SQL> select member from v$logfile lf , v$log l where l.status='CURRENT' and lf.group#=l.group#;
MEMBER
--------------------------------------------------------------------------------
/oradata2/data1/dbase/redo02.log
C)Now apply this redo log to the database.
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 969547 generated at 05/12/2008 23:54:02 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_12/o1_mf_1_4_%u_.arc
ORA-00280: change 969547 for thread 1 is in sequence #4
Specify log: {
/oradata2/data1/dbase/redo02.log
Log applied.
Media recovery complete.
D)Open the database with RESETLOGS option.
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
Tuesday, April 22, 2008
About Media Recovery in Oracle.
Media recovery can be used to recover from a lost or damaged current datafile, SPFILE or control file.
The situations which must undergo Media Recovery:
•You restore a backup of a datafile.
•You restore a backup control file (even if all datafiles are current).
•A datafile is taken offline (either by you or automatically by the database) without the OFFLINE NORMAL option.
Condition for Media Recovery for a Data file:
•Database must not be open or the specific datafile must be offline, if database is open.
•The datafile that needs media recovery cannot be brought online until media recovery has been completed.
•A database cannot be opened if any of the online datafiles needs media recovery.
The situations which must undergo Media Recovery:
•You restore a backup of a datafile.
•You restore a backup control file (even if all datafiles are current).
•A datafile is taken offline (either by you or automatically by the database) without the OFFLINE NORMAL option.
Condition for Media Recovery for a Data file:
•Database must not be open or the specific datafile must be offline, if database is open.
•The datafile that needs media recovery cannot be brought online until media recovery has been completed.
•A database cannot be opened if any of the online datafiles needs media recovery.
Thursday, April 3, 2008
Causes and Solutions on ORA-1113 Error File Needs Media Recovery
Error Explanation:
------------------
ORA-01113: "file %s needs media recovery"
This error is usually followed with ORA-1110 error which will indicate the
name of the datafile that needs media recovery.Like,
ORA-01110: data file 4: 'C:\ORACLE\ORADATA\BDMS\DRSYS01.DBF'
This error message indicates that a datafile that is not up-to-date with respect to the controlfile and other datafiles.
Oracle's architecture is tightly coupled in the sense that all database files i.e., datafiles, redolog files, and controlfiles -- must be in sync when the database is opened or at the end of a checkpoint.
This implies that the checkpoint SCN (System Commit Number) of all datafiles must be the same. If that is not the case for a particular datafile, an ORA-1113 error will be generated.
For example, when you put a tablespace in hot backup mode, the checkpoint SCN of all its datafiles is frozen at the current value until you issue the corresponding end backup. If the database crashes during a hot backup and you try to restart it without doing recovery, you will likely get ORA-1113 for at least one of the datafiles in the tablespace that was being backed up, since its SCN will probably be lower than that of the controlfile and the datafiles in other tablespaces.
Likewise, offlining a datafile causes its checkpoint SCN to freeze. If you simply attempt to online the file without recovering it first, its SCN will likely be much older than that of the online datafiles, and thus an ORA-1113 will result.
Ensure what you need to do?
-----------
1.startup mount;
2.conn / as sysdba
3.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.
The steps to take next depend on the scenario in which the ORA-1113 was issued.
This is discussed in the following sections.
Possible Causes and Corresponding Solutions:
----------------------------------------------
I. AT STARTUP AFTER CRASH WITH TABLESPACE(S) IN HOT BACKUP
II. AT STARTUP AFTER RESTORING A DATAFILE OR TABLESPACE FROM A BACKUP
III. TRYING TO ONLINE A DATAFILE OR TABLESPACE
IV. WHEN RECOVERING ' USING BACKUP CONTROLFILE' OPTION TO DO INCOMPLETE RECOVERY
I. AT STARTUP AFTER CRASH WITH TABLESPACE(S) IN HOT BACKUP
----------------------
1.startup mount
2. Find out which datafiles were in hot backup mode when the database crashed or was shutdown abort or the machine was rebooted by running the query:
SELECT V1.FILE#, NAME
FROM V$BACKUP V1, V$DATAFILE V2
WHERE V1.STATUS = 'ACTIVE' AND V1.FILE# = V2.FILE# ;
3.alter database datafile 'file name' end backup;
4.alter database open;
II. AT STARTUP AFTER RESTORING A DATAFILE OR TABLESPACE FROM A BACKUP
-------------------------
A)Database in Archivelog Mode
1.STARTUP MOUNT;
2.RECOVER DATAFILE 'file name';
3.Confirm each of the archived logs that you are prompted for until you receive the message "Media recovery complete".If you are prompted for an archived log that does not exist, 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.
4. ALTER DATABASE OPEN;
B)Database in noArchivelog Mode
In this case, you will only succeed in recovering the datafile or tablespace if the redo to be applied to it is within the range of your online logs.
Issue the query:
SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;
Compare the change number you obtain with the FIRST_CHANGE# of your online logs.
If the CHANGE# is GREATER than the minimum FIRST_CHANGE# of your logs, the datafile can be recovered. In this case, the procedure to be followed is analogous to that of scenario II.A above, except that you must always enter the appropriate online log when prompted, until recovery is finished.
If the CHANGE# is LESS than the minimum FIRST_CHANGE# of your logs, the file cannot be recovered.Your options at this point include:
- If the datafile is in a temporary or index tablespace, you may drop it with an
ALTER DATABASE DATAFILE '' OFFLINE DROP
statement and then open the database. Once the database is up, you must drop the tablespace to which the datafile belongs and recreate it.
- If the datafile is in the SYSTEM or in a rollback tablespace, restore an up-to-date copy of the datafile (if available) or your most recent full backup.In case you do not have either of this, then it might not be possible to recover the database fully.
III. TRYING TO ONLINE A DATAFILE OR TABLESPACE
--------------------------------------
1.RECOVER DATAFILE 'file name';
2. Confirm each of the archived logs that you are prompted for until you receive the message "Media recovery complete".
3.ALTER DATABASE OPEN;
IV. WHEN RECOVERING ' USING BACKUP CONTROLFILE' OPTION TO DO INCOMPLETE RECOVERY
-------------------------------
1.RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
2. Cancel recovery by issuing the "CANCEL" command.
3. ALTER DATABASE OPEN RESETLOGS;
Related Documents
------------------
ORA-01113: "file %s needs media recovery"
This error is usually followed with ORA-1110 error which will indicate the
name of the datafile that needs media recovery.Like,
ORA-01110: data file 4: 'C:\ORACLE\ORADATA\BDMS\DRSYS01.DBF'
This error message indicates that a datafile that is not up-to-date with respect to the controlfile and other datafiles.
Oracle's architecture is tightly coupled in the sense that all database files i.e., datafiles, redolog files, and controlfiles -- must be in sync when the database is opened or at the end of a checkpoint.
This implies that the checkpoint SCN (System Commit Number) of all datafiles must be the same. If that is not the case for a particular datafile, an ORA-1113 error will be generated.
For example, when you put a tablespace in hot backup mode, the checkpoint SCN of all its datafiles is frozen at the current value until you issue the corresponding end backup. If the database crashes during a hot backup and you try to restart it without doing recovery, you will likely get ORA-1113 for at least one of the datafiles in the tablespace that was being backed up, since its SCN will probably be lower than that of the controlfile and the datafiles in other tablespaces.
Likewise, offlining a datafile causes its checkpoint SCN to freeze. If you simply attempt to online the file without recovering it first, its SCN will likely be much older than that of the online datafiles, and thus an ORA-1113 will result.
Ensure what you need to do?
-----------
1.startup mount;
2.conn / as sysdba
3.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.
The steps to take next depend on the scenario in which the ORA-1113 was issued.
This is discussed in the following sections.
Possible Causes and Corresponding Solutions:
----------------------------------------------
I. AT STARTUP AFTER CRASH WITH TABLESPACE(S) IN HOT BACKUP
II. AT STARTUP AFTER RESTORING A DATAFILE OR TABLESPACE FROM A BACKUP
III. TRYING TO ONLINE A DATAFILE OR TABLESPACE
IV. WHEN RECOVERING ' USING BACKUP CONTROLFILE' OPTION TO DO INCOMPLETE RECOVERY
I. AT STARTUP AFTER CRASH WITH TABLESPACE(S) IN HOT BACKUP
----------------------
1.startup mount
2. Find out which datafiles were in hot backup mode when the database crashed or was shutdown abort or the machine was rebooted by running the query:
SELECT V1.FILE#, NAME
FROM V$BACKUP V1, V$DATAFILE V2
WHERE V1.STATUS = 'ACTIVE' AND V1.FILE# = V2.FILE# ;
3.alter database datafile 'file name' end backup;
4.alter database open;
II. AT STARTUP AFTER RESTORING A DATAFILE OR TABLESPACE FROM A BACKUP
-------------------------
A)Database in Archivelog Mode
1.STARTUP MOUNT;
2.RECOVER DATAFILE 'file name';
3.Confirm each of the archived logs that you are prompted for until you receive the message "Media recovery complete".If you are prompted for an archived log that does not exist, 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.
4. ALTER DATABASE OPEN;
B)Database in noArchivelog Mode
In this case, you will only succeed in recovering the datafile or tablespace if the redo to be applied to it is within the range of your online logs.
Issue the query:
SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;
Compare the change number you obtain with the FIRST_CHANGE# of your online logs.
If the CHANGE# is GREATER than the minimum FIRST_CHANGE# of your logs, the datafile can be recovered. In this case, the procedure to be followed is analogous to that of scenario II.A above, except that you must always enter the appropriate online log when prompted, until recovery is finished.
If the CHANGE# is LESS than the minimum FIRST_CHANGE# of your logs, the file cannot be recovered.Your options at this point include:
- If the datafile is in a temporary or index tablespace, you may drop it with an
ALTER DATABASE DATAFILE '
statement and then open the database. Once the database is up, you must drop the tablespace to which the datafile belongs and recreate it.
- If the datafile is in the SYSTEM or in a rollback tablespace, restore an up-to-date copy of the datafile (if available) or your most recent full backup.In case you do not have either of this, then it might not be possible to recover the database fully.
III. TRYING TO ONLINE A DATAFILE OR TABLESPACE
--------------------------------------
1.RECOVER DATAFILE 'file name';
2. Confirm each of the archived logs that you are prompted for until you receive the message "Media recovery complete".
3.ALTER DATABASE OPEN;
IV. WHEN RECOVERING ' USING BACKUP CONTROLFILE' OPTION TO DO INCOMPLETE RECOVERY
-------------------------------
1.RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
2. Cancel recovery by issuing the "CANCEL" command.
3. ALTER DATABASE OPEN RESETLOGS;
Related Documents
Subscribe to:
Posts (Atom)