Incident
DBA tried to add a datafile to database, but misnamed it and issued the following command

alter database datafile ‘/opt/oracle/emtprod/u18/oradata/EMTDB/mtm_data3_03_EMTDB.dbf’ offline drop;

Copied the file to it’s new location/name and then issued the following

alter database rename file ‘/opt/oracle/emtprod/u18/oradata/EMTDB/mtm_data3_03_EMTDB.dbf’
to ‘/opt/oracle/emtprod/u20/oradata/EMTDB/mtm_data2_04_EMTDB.dbf’;

When dba made the datafile online …

alter database datafile ‘/opt/oracle/emtprod/u20/oradata/EMTDB/mtm_data2_04_EMTDB.dbf’ online;

DBA received the following error

ERROR at line 1:
ORA-01113: file 26 needs media recovery
ORA-01110: data file 26: ‘/opt/oracle/emtprod/u20/oradata/EMTDB/mtm_data2_04_EMTDB.dbf’

Solution:

If this happened just a few minutes ago, Just needed to do
SQL> alter database recover datafile ……

Brought file back into offline mode and then you will be able to online the file.

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;

Check the results of the above query If it return one and only one row for the online datafiles, means they are already synchronized in terms of their SCN. Otherwise the datafiles are still not synchronized yet.

Error Message :
SQL> startup
ORACLE instance started.

Total System Global Area 2264924160 bytes
Fixed Size 2022408 bytes
Variable Size 150995960 bytes
Database Buffers 2097152000 bytes
Redo Buffers 14753792 bytes
Database mounted.
ORA-01122: database file 10 failed verification check
ORA-01110: data file 10: ‘/opt/oracle/xxxxx/xxxxx.dbf’
ORA-01207: file is more recent than control file – old control file

Suggestion:

1. Take a trace of controlfile.

SQL > alter database backup controlfile to trace;

2. You can find the file in userdump dest. Open the trace file and edit for neccesary changes.

You can create control file script using two methods depends on RESETLOGS/NORESETLOGS

3. Startup the database in nomount state.

SQL > startup nomount

4. Run the controlfile script create in step 2

SQL > @cfile.sql

5. Recover the db

SQL > recover database using backup controlfile

6. Open the database.

SQL > alter database open;

—– Recover and Open Database command varies depends on the method you are using to create control file. Best option is alwasy read through your Trace file.

For NORESETLOGS – You have to use the following Command
RECOVER DATABASE
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE ‘/xxx/xxxx/Temp01.dbf’ REUSE;

For RESETLOGS – You have to use the following Command
RECOVER DATABASE USING BACKUP CONTROLFILE
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE ‘/xxx/xxxx/Temp01.dbf’ REUSE;

ORA-00600:[Kccrbl_stale] at Startup Mount
Scenario:
One of our Test Database was running on Unix with 10g Rel1.
Due to power outage database went down when the sever comes up
and we tried to startup the database but we got ORA-600 with
first argument as “Kccrbl_stale”.

Reason: Control file header is corrupted.

Solution :
Anyone solution based on the backup method you use
1. Restore the backup of controlfile and do complete recovery
2. Recreate control file from using the script generated
3. Recreate the control file manually with “NORESETLOGS”

Scenario: It is a Testing Database while Testing Import Database Storage got falied and Import aborted.
Restarted the storage and while trying to bring the Database up we got ORA-01113

1. Database is on No Archive Log Mode
2. There is no Cold Backup of the Database

Solution:
Since there is no Backup and database is not in archive log mode. We will not be able to go for Restore or Reocovery process.
Only way is to drop the Datafile and bring the database up.

Example:
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: ‘/u01/test/users02.dbf’

SQL> select file#,name, status from v$datafile where file#=5;

FILE# NAME STATUS
———- ————————————————- ———–
5 /u01/test/users02.dbf ONLINE

SQL>

SQL> alter database datafile ‘/u01/test/users02.dbf’ offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL>