Essential OCP 9i DBA Certification Interview Questions & Answers:

1. Oracle OCP 9i DBA interview questions!

1. What is an oracle instance?
2. What is a view?
3. What is referential integrity?
4. Name the data dictionary that stores user-defined constraints?
5. What is a collection of privileges?
6. What is a snapshot?
7. What is a synonym?
8. What is a cursor?
9. What is a sequence?
10. What is a trigger?
11. What is an exception?
12. What is a partition of table?
13. What are pseudo-columns in SQL? Provide examples.
14. What are the Data Control statements?
15. What is a schema?
16. What is a type?
17. What is a data model?
18. What is a relation?
19. Advantages of redo log files?
20. What is an Archiver?
21. What is a database buffer cache?
22. What are the background processes in Oracle?
23. %type and %rowtype are attributes for…?
24. What are the steps in a two-phase commit?
25. What is a union, intersect, minus?
26. What is a join, explain the types of joins?
27. What is a co-related sub-query?
28. ODBC stands for…?
29. Data-type used to work with integers is?
30. Describe data models?
31. Describe the Normalization principles?
32. What are the types of Normalization?
33. What is de-normalization?

2. Suppose You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?

I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.

3. Can you explain difference between a hot backup and a cold backup and the benefits associated with each?

A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.