Backup & Recovery questions pour Oracle DBA

Some of the Common Backup and Recovery Interview Questions for Oracle Database Administrator.

These questions are common for both Senior Oracle DBA or Junior DBA.

I have compiled these questions based upon the feedback I got from many candidates who have attended interviews in various MNC’s

1. Which types of backups you can take in Oracle?

3 types  of backup  Hot,  Cold, partial and incremential


2. A database is running in NOARCHIVELOG mode then which type of backups you can take?

Cold backup ( shutdown immediate , startup mount  and backup database and after startup  a database)


3. Can you take partial backups if the Database is running in NOARCHIVELOG mode?

No, database must be offline.


4. Can you take Online Backups if the the database is running in NOARCHIVELOG mode?

No, only with archivelog


5. How do you bring the database in ARCHIVELOG mode from NOARCHIVELOG mode?

SQL>shutdown immediate;  SQL>startup mount;    SQL> alter database archivelog; SQL> alter database open;


6. You cannot shutdown the database for even some minutes, then in which mode you should run
the database?

RMAN>startup  explicite;

7. Where should you place Archive logfiles, in the same disk where DB is or another disk?

Other disk


8. Can you take online backup of a Control file if yes, how?

RMAN> backup current controlfile;


9. What is a Logical Backup?

A logical backup (import/export) of a database exports the database objects independent of the physical location of those objects. In other words, a logical backup exports the data stored in a table regardless of the location of the table (such as tablespace or datafile).


10. Should you take the backup of Logfiles if the database is running in ARCHIVELOG mode?



11. Why do you take tablespaces in Backup mode?

SQL>alter  system tablespace offline;


12. What is the advantage of RMAN utility?

Make backup when the database is running


13. How RMAN improves backup time?

Scheduler task with file rcv


14. Can you take Offline backups using RMAN?



15. How do you see information about backups in RMAN?

RMAN>show all;


16. What is a Recovery Catalog?

Backup  caltalog


17. Should you place Recovery Catalog in the Same DB?



18. Can you use RMAN without Recovery catalog?



19. Can you take Image Backups using RMAN?



20. Can you use Backupsets created by RMAN with any other utility?



21. Where RMAN keeps information of backups if you are using RMAN without Catalog?

RMAN>show all;


22. You have taken a manual backup of a datafile using o/s. How RMAN will know about it?

RMAN catalog


23. You want to retain only last 3 backups of datafiles. How do you go for it in RMAN?

RMAN>configure retention policy to redundancy 3;


24. Which is more efficient Incremental Backups using RMAN or Incremental Export?

Incremental backup with posibilites  level 0  & 1


25. Can you start and shutdown DB using RMAN?



26. How do you recover from the loss of datafile if the DB is running in NOARCHIVELOG mode?

RMAN>startup nomount;    RMAN> SQL ‘alter database datafile6 offline;  RMAN>recover datafile6;


27. You loss one datafile and it does not contain important objects. The important objects are there in other datafiles which are intact. How do you proceed in this situation?

RMAN> restore  datafile5;


28. You lost some datafiles and you don’t have any full backup and the database was running in NOARCHIVELOG mode. What you can do now?

SQL> startup mount; SQL>flashback database to timestamp sysdate -1/24;  or SQL>flashback  to scn (ex.436982938)…;   SQL> alter database open resetlogs;


29. How do you recover from the loss of datafile if the DB is running in ARCHIVELOG mode?

RMAN> startup mount  RMAN> restore database;  with the last full backup


30. You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week old and partial backup of this datafile which is just 1 day old. From which backup should you restore this file?

RMAN>startup mount;   RMAN> restore database;    RMAN> recover database;


31. You loss controlfile how do you recover from this?

RMAN> restore current controlfile; RMAN> restore  spfile;


32. The current logfile gets damaged. What you can do now?

RMAN> alter database open restlogs;


33. What is a Complete Recovery?

RMAN> restore database;   full  restore database


34. What is Cancel Based, Time based and Change Based Recovery?

- A cancel-based recovery is a type of user-managed incomplete recovery that is performed by specifying the UNTIL CANCEL clause with the RECOVER command (a SQL*Plus command that is used to recover a database). The UNTIL CANCEL clause specifies that the recovery process will continue until the user manually cancels the recovery process issuing the CANCEL command.

-Time-Based, Change-Based, or SCN-based Incomplete Recovery

Incomplete recovery uses a backup to produce a non-current version of the database. In other words, you
do not apply all of the redo records generated after the most recent backup.

You usually perform incomplete recovery of the whole database in the following situations:

Media failure destroys some or all of the online redo logs.

A user error causes data loss, for example, a user inadvertently drops a table.

You cannot perform complete recovery because an archived redo log is missing.

You lose your current control file and must use a backup control file to open the database.

To perform incomplete media recovery, you must restore all datafiles from backups created prior to the time to which you want to recover and then open the database with the RESETLOGS option when recovery completes. The RESETLOGS operation creates a new incarnation of the database; in other words, a database with a new stream of log sequence numbers starting with log sequence 1.


35. Some user has accidentally dropped one table and you realize this after two days. Can you recover this table if the DB is running in ARCHIVELOG mode?

SQL>flashback  table name to timestamp to scn  (ex. 436982938);


36. Do you have to restore Datafiles manually from backups if you are doing recovery using RMAN?


37. A database is running in ARCHIVELOG mode since last one month. A datafile is added to the database last week. Many objects are created in this datafile. After one week this datafile gets damaged before you can take any backup. Now can you recover this datafile when you don’t have any backups?



38. How do you recover from the loss of a controlfile if you have backup of controlfile?

RMAN>restore current controlfile;


39. Only some blocks are damaged in a datafile. Can you just recover these blocks if you are using RMAN?



40. Some datafiles were there on a secondary disk and that disk has become damaged and it will take some days to get a new disk. How will you recover from this situation?

RMAN>restore datafile6;


41. Have you faced any emergency situation. Tell us how you resolved it?

Restore database  with dump file


42. At one time you lost parameter file accidentally and you don’t have any backup. How you will recreate a new parameter file with the parameters set to previous values.

Restore complet machime with image ghost.

Publié dans : Oracle |le 17 février, 2012 |Pas de Commentaires »

Vous pouvez laisser une réponse.

Laisser un commentaire

Logiciel fonds de commerce |
Anglais pour non-spécialist... |
Le Geek-isme d'ici | | Créer un blog | Annuaire | Signaler un abus | Sahirus Solutions
| SEO Firm in India
| clgmonet3eme2gr5