Complete Closed Database Recovery.
System tablespace is missing. If the system tablespace is missing or corrupted the database cannot be started up so a complete closed database recovery must be performed.
Pre requisites: A closed or open database backup and archived logs.
1. Use OS commands to restore the missing or corrupted system datafile to its original location, ie: cp -p /user/backup/uman/system01.dbf /user/oradata/u01/dbtst/system01.dbf
2. startup mount;
3. recover datafile 1;
4. alter database open;
Complete Open Database Recovery (when the database is initially closed).
Non system tablespace is missingIf a non system tablespace is missing or corrupted and the database crashed, recovery can be performed after the database is open.
Pre requisites: A closed or open database backup and archived logs.
1. startup; (you will get ora-1157 ora-1110 and the name of the missing datafile, the database will remain mounted)
2. Use OS commands to restore the missing or corrupted datafile to its original location, ie: cp -p /user/backup/uman/user01.dbf /user/oradata/u01/dbtst/user01.dbf
3. alter database datafile3 offline; (tablespace cannot be used because the database is not open)
4. alter database open;
5. recover datafile 3;
6. alter tablespace online;
Recovery of a Missing Datafile that has no backups (database is open).
If a non system datafile that was not backed up since the last backup is missing, recovery can be performed if all archived logs since the creation of the missing datafile exist.
Pre requisites: All relevant archived logs.
1. alter tablespace offline immediate;
2. alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf';
3. recover tablespace ;
4. alter tablespace online;
If the create datafile command needs to be executed to place the datafile on a location different than the original use:
alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf' as '/user/oradata/u02/dbtst/newdata01.dbf'
