Unable to open database (Datafile corrupted)

oracleoracle-12c

I am unable to open the database, but i am able to mount it.

When i try to open the database, getting following error

ORA-03113 – end-of-file on communication channel

I am using Oracle 12C.

On further analysis below is the trace log created using adrci

> *** 2016-05-02 09:12:27.769 DDE rules only execution for: ORA 1110
> ----- START Event Driven Actions Dump ----
> ---- END Event Driven Actions Dump ----
> ----- START DDE Actions Dump ----- Executing SYNC actions
> ----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) ----- Successfully dispatched
> ----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) ----- Executing ASYNC actions
> ----- END DDE Actions Dump (total 0 csec) ----- ORA-01110: data file 111: 'V:\DB\CST001.DBF' ORA-01114: IO error
> writing block to file 111 (block # 1) ORA-27041: unable to open file
> OSD-04002: unable to open file O/S-Error: (OS 5) Access is denied.
> Read of datafile 'V:\DB\CST001.DBF' (fno 111) header failed with
> ORA-01208 Rereading datafile 111 header failed with ORA-01208 DDE:
> Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
> ORA-01110: data file 111: 'V:\DB\CST001.DBF' ORA-27091: unable to
> queue I/O ORA-27041: unable to open file OSD-04002: unable to open
> file O/S-Error: (OS 5) Access is denied. ORA-01208: data file is an
> old version - not accessing current version error 1114 detected in
> background process ORA-01114: IO error writing block to file 111
> (block # 1) ORA-01110: data file 111: 'V:\DB\CST001.DBF'
> ORA-27091: unable to queue I/O ORA-27041: unable to open file
> OSD-04002: unable to open file O/S-Error: (OS 5) Access is denied.
> ORA-01208: data file is an old version - not accessing current version
> 2016-05-02 09:12:27.778758 :kjzduptcctx(): Notifying DIAG for crash
> event

There is no permission issue. This is windows 2012 R2 Strandard edition. Database in no archive log mode, we have only backup's taken from expdp. The data is in SSD.

Best Answer

Ok, so you have no backup.

You made sure it is not a permission issue.

But you have Data Pump dumps, at least that is something.

First try to recover the datafile without data loss:

startup mount
alter database datafile 'V:\DB\CST001.DBF' online;
recover datafile 'V:\DB\CST001.DBF';

Answer the prompts accordingly, if any. If this succeeds, you can open the database with:

alter database open resetlogs;

I highly doubt the above would work in your case, so below is the data loss scenario.

startup mount
alter database datafile 'V:\DB\CST001.DBF' offline drop;
alter database open;

Using OFFLINE DROP allows you to open the instance (as long as the affected datafile is not critical) without it trying to access that file. Since you can not restore and recover the datafile without backup, you can drop the tablespace (I am just guessing the name from the datafile):

drop tablespace cst including contents and datafiles;

Recreate the tablespace:

create tablespace cst datafile 'V:\DB\CST001.DBF' size ..;
alter tablespace cst add datafile 'V:\DB\CST002.DBF' ...;
...

Regrant any tablespace quotas if you had them before (check DBA_TS_QUOTAS before dropping the tablespace).

And import what you have in your Data Pump dumps.

The not so easy way would be trying to salvage data from the other datafiles in the tablespace before dropping it.

Related Question