ORA-01113 - ORA-01110: After an Instance Failure or SHUTDOWN ABORT

After an instance failure or SHUTDOWN ABORT, you can`t start your database. At startup you`ll get this messages:

sqlplus /nolog
SQL> connect / as sysda
SQL> startup
.
.
.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/PRD/sapdata1/system_1/system.data1'

Whenever crash recovery is required, if a datafile is in backup mode when an attempt is made to open it, then the database will not open the database until either a recovery command is issued, or the datafile is taken out of backup mode.

If the database indicates that the datafiles for multiple tablespaces require media recovery because you forgot to end the online backups for these tablespaces, then so long as the database is mounted, running the ALTER TABLESPACE END BACKUP statement takes all the datafiles out of backup mode simultaneously.

In high availability situations, and in situations when no DBA is monitoring the database, the requirement for user intervention is intolerable. Hence, you can write a crash recovery script that does the following:

Mount the database, then run ALTER TABLESPACE ... END BACKUP for each tablespace still in backup mode.

To take tablespaces out of backup mode simultaneously:

Mount but do not open the database. For example, enter:
SQL> STARTUP MOUNT

If performing this procedure manually (that is, not as part of a crash recovery script), query the V$BACKUP view to list the datafiles of the tablespaces that were being backed up before the database was restarted:
SQL>  SELECT * FROM V$BACKUP WHERE STATUS = 'ACTIVE';
FILE#      STATUS             CHANGE#    TIME    
---------- ------------------ ---------- ---------
12 ACTIVE                  20863 25-NOV-02
13 ACTIVE                  20863 25-NOV-02
20 ACTIVE                  20863 25-NOV-02
3 rows selected.

Issue the ALTER TABLESPACE END BACKUP statement to take all datafiles currently in backup mode out of backup mode. For example, enter:
SQL> ALTER TABLESPACE PSAPPRD END BACKUP;
SQL> ALTER TABLESPACE PSAPPRDUSR END BACKUP;
SQL> ALTER TABLESPACE PSAPPRD620 END BACKUP;

After redo the SQL Select (SQL>  SELECT * FROM V$BACKUP WHERE STATUS = 'ACTIVE';) until nones are in Backup Mode.

Finally Open your database

SQL> alter database open;