Backup Options   «Prev 

Recover Datafile with no backup(Oracle)

  1. As the DBA, you must first set the ORACLE_SID in order to point to the correct database, ORCL. Type SET ORACLE_SID=ORCL at the prompt and press Enter.
  2. To access Server Manager, type SVRMGR30 at the prompt and press Enter.
  3. To connect to ORCL as sysdba, type CONNECT INTERNAL at the prompt and press Enter. The password ORACLE is entered for you.
  4. Since the database was closed due to a media failure, you must open it in the mount mode. Type STARTUP MOUNT at the prompt and press Enter.
  5. To take the damaged datafile offline, type ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\STUDENT.ORA' OFFLINE; and press Enter.
  6. Now you can open the database and make it available to the users who do not need the STUDENT tablespace. Type ALTER DATABASE OPEN at the prompt and press Enter.
  7. To confirm the recovery status of the corrupt datafile, you query the data dictionary view V$RECOVER_FILE. Type SELECT * FROM V$RECOVER_FILE; at the prompt and press Enter.
  8. The result shows that file #5 needs recovery. If no files needed recovery, this query would return no records. The CHANGE# column indicates the SCN where recovery must start. The value of zero shows that the recovery of this datafile in its original location is impossible. To recreate the datafile in an alternate location, type ALTER DATABASE CREATE DATAFILE 'C:\ORANT\DATABASE\STUDENT.ORA' AS 'D:\NEW_LOCATION\DATABASE\STUDENT.ORA'; at the prompt and press Enter.
  9. You must check the recovery status of the newly created datafile again by querying the V$RECOVER_FILE view. Type SELECT * FROM V$RECOVER_FILE; at the prompt and press Enter.
  10. The query result shows that the recovery for this file should start with SCN 562019. To start the recovery process, type RECOVER DATAFILE 'D:\NEW_LOCATION\DATABASE\STUDENT.ORA' UNTIL TIME '2000-01-18:12:00:00'; at the prompt and press Enter.
  11. To automatically apply all the archived and online redo logs, type AUTO at the cursor, and then press Enter.
  12. All the archived and online redo logs will be applied to bring the data in the datafile back to the time the media failure occurred. In this simulation, only the beginning and the end of the recovery process are displayed. Click anywhere on the screen to go onto the next step.
  13. To bring the recovered datafile back online, type ALTER DATABASE DATAFILE 'D:\NEW_LOCATION\DATABASE\STUDENT.ORA' ONLINE; at the prompt and press Enter.
  14. You have successfully recovered the tablespace STUDENT. The important lesson learned: Do not forget to include the tablespace in the backup strategy to avoid this kind of recovery in future. This completes the Simulation.