Backup and recovery interview question

1.What is the difference between cold and hot backup?

a.COLD backup will be taken by shutting down the database, where as HOT backup will be taken while database is up and running

2. What happens when a database or tablespace is placed in begin backup mode?
   1. Datafile header will get freezed i.e CKPT will not update latest SCN
   2. DBWR still will write data into datafiles
   3. When end backup, CKPT will update the latest SCN to datafile header

3. Why more redo will generate during hot backup?
   1. It is to avoid fractured block as oracle will copy entire block as redo entry

4. What is fractured block?
   1. A block which might contain inconsistent data. This happens because the speed of DBWR is different than OS copy during hot backup

5. What is the difference between complete and incomplete recovery?
   1. No data loss in complete recovery whereas some data loss is observed in incomplete recovery

6. What will happen if we use resetlogs?
   1. It will create new redolog files (if not already there)
   2. It will reset log sequence number to 1,2…etc

7. Out of 100 datafiles, I lost 29 files. How you will identify which files to recover?
8. a.Using the view v$recovery_file

8. How to check if incomplete recovery is performed in the database?
   1. SQL> select RESETLOGS_TIME,RESETLOGS_CHANGE from v$database;

9. I placed a tablespace in hot backup mode and datafile which is being backup is  lost. How you will recover it?
   1. We can restore from old backup and apply all the archives till now

10. Yesterday night backup is successful. Today morning we added a datafile at 11  AM. After noon 3’o clock the newly added datafile is lost. Can I recover that  datafile? If so, how?
    1. Yes we can recover it. We need to create that datafile using “alter database create datafile ‘path’;” command and then apply all the archives

11. What is the importance of archives during recovery process?
    1. As they will store all the changes happened in the database, always we can do complete recovery if we have a perfect backup

12. How you will recover database when all copies of control file are lost?
    1. We can do a complete recovery if we have a latest trace of it. Or else, we need to do incomplete recovery by restoring controlfile from last successful backup

13. Application team informed that an important table is dropped. How you will  recover it?
    1. We can recover it by doing until time recovery. But this will affect other user transactions, so we need to get approval for this first

14. What are the pre-requisite factors you will consider before performing until time  recovery?
    1. We need to see if other users are not getting any affect by doing this

15. You need to restore the database and then realized there are no control files.  How you will proceed?
    1. Either we can resotre controlfiles first and do recovery (but a data loss is there as its incomplete recovery) or we can create new controlfile if we have a latest trace