Interview Questions on Oracle DBA
1. What is a database?
• Database offer a single point of mechanism for storing and retrieving information with the help of tables.
• Table is made up of columns and rows where each column stores specific attribute and each row displays a value for the corresponding attribute.
• It is a structure that stores information about the attributes of the entities and relationships among them.
• It also stores data types for attributes and indexes.
• Well known DBMS include Oracle, ibm db2, Microsoft sql server, Microsoft access, mysql and sqlLite.
2. What are the different responsibilities of a DBA?
Ans: DBA is the database administrator who performs all administrative tasks.
Administrative Tasks include:
User level administration i.e. creates users, remove existing users or modify user permissions.
Maintains database security.
Manages database storage & objects.
Tunes performance of a database.
Performs backups & recovery tasks.
3. Why do we use materialized view instead of table or views?
Ans: Materialized view is a database object that holds query results.
If materialized views are used instead of tables or views in complex query executions, performance gets enhanced as re-execution is not required for repeated queries.
4. What is a SYSTEM tablespace and why do we need it?
Ans: System tablespace is created at the time of database creation.
This tablespace holds all the data dictionary tables and hence it is used for the creation of various database objects. System tablespace must stay online for the database to function smoothly
5. What do you mean by SGA and how is it different from PGA?
Ans: SGA means System Global Area, which is the memory area that is defined by Oracle during instance startup. This area can be shared by the system-level processes and hence it is known as the Shared Global Area as well.
PGA is Program Global Area, which is the memory specific to a process or session. It is created when the Oracle process gets started and each process will have a dedicated PGA.
6. What are the different types of backups that are available in Oracle?
Ans: On a higher level, there are 2 types of backup that are available in Oracle which are physical & logical.
During physical backup, copies of physical database files (like data files, control files, redo logs & other executables) are created and saved for the future. This can be achieved using either operating system utilities or RMAN.
In contrast, logical backup allows taking a backup of the database objects like tables, views, indexes, stored procedures, etc. individually through Export/Import utility provided by Oracle.
7. What do we mean by hot backup & cold backup and how are they different?
Ans: Hot backup is the process of taking database backup while the database is in running mode. Hence, it is also known as Online Backup. While cold backup can be taken only when the database is in shut down mode and hence it is known as Offline Backup as well.
There are few websites like banking & trading ones, which are 24 hours operational and hence, cannot support bringing the database down. Hence, DBAs need to take the backup in online mode only.
8.What is the difference between restoring a database and recovering a database?
Ans: During the restoration process, backup files are copied from the hard disk, media or tapes to the restoration location and later make the database operational. Recovery has an additional step of updating these data files by applying redo logs so as to recover the changes which are not backed up.
Let me explain with the help of a scenario.
• Database full backup is taken on Friday 11 PM
• Database crash happened on Saturday 7 AM
We can restore the lost files using the 11 PM full backup which is Restoration. However, the data will be restored up till Friday 11 PM and not till Saturday at 7 AM. In order to do the same, redo logs can be applied which will bring the database to the point of failure.
9. What do you understand by Redo Log file mirroring?
Ans: Redo log is the most crucial component of database architecture which records all of the transactions within the database even before it goes to the data file.
Hence, the mirroring of these files will be done to protect them. Redo Log file mirroring allows redo logs to be copied to different disks simultaneously. And this can be achieved using Data Guard and other utilities.
10. How is incremental backup different from differential backup?
Ans: Incremental Backup backs up only the changed data files since the last backup, which might be full or incremental. E.g. An incremental/full backup is taken at 10 AM on Friday and next taken at 10 AM Saturday. The second incremental backup will only have the transactions occurred after Friday at 10 AM.
While Differential Backup backs up the files that changed during the last full backup.
If you take a full back up on Friday 10 AM and then differential backup on Saturday 10 AM, it will take the backup of the files changed since Friday, 10 AM. Further, if the differential backup is taken on Sunday 10 AM, it will take the backup of the files changed since Friday, 10 AM.
11. What is a Flashback Query and when should it be used?
Ans: Oracle has introduced a Flashback technology to recover the past states of database objects. It can recover the accidental changes, which got committed as well. Recovery depends on the specified value of the UNDO_RETENTION parameter.
For Example, the UNDO_RETENTION parameter is set to 2 hours and if a user accidentally deletes the data at 11 AM with commit performed. Then, using FLASHBACK QUERY, he can retrieve these rows until 1 PM only.
12. How is RMAN better than the user-managed backup recovery process?
Ans: Recovery Manager (RMAN) is an Oracle built-in utility that can automate database backup & recovery processes and administrate backup strategies as well. In user-managed backups, the user needs to perform backup manually.
RMAN backup time will be less when compared to User-managed backups as RMAN maintains all the metadata in the Central Repository and can quickly retrieve the same.
RMAN does incremental backup rather than taking full file backups which are done by user-managed backups, which again saves time.
RMAN creates backup and recovery scripts that can be re-used and scheduled and does not need manual intervention.
RMAN can detect corrupted data blocks automatically during the backup process and recover them, whereas it doesn’t happen in user-managed backups.
13. What is a Recovery Catalog?
Ans: Recovery Catalog is a database schema that holds the metadata used by RMAN for restoration and recovery processes.
It basically stores information on
Datafiles & their backup files.
Archived Redo Logs & their backup sets.
The catalog gets updated once RMAN takes a backup or switches redo log or changes data file.
14. What is the use of MMON background process in Oracle Database?
MMON (Manageability Monitor)
MMON (Manageability Monitor) is a background process introduced in Oracle 10g. It gathers memory statistics (snapshots) and stores this information in the AWR (automatic workload repository). MMON is also responsible for issuing alerts for metrics that exceed their thresholds.
15. What is the difference between recovery catalog and Nocatalog backup in RMAN?
ByDefault RMAN connects to the target database in Nocatalog Mode.
In Nocatalog Mode, Backup Information and Metadata related to RMAN is stored in target database controlfile.
A recovery catalog is a schema created in a separate database that contains metadata obtained from the target control file.In recovery catalog we can store rman scripts.
We can store metadata about multiple incarnations of a single target database in the catalog.
Recovery catalog is central and can have information of many databases.
If the control file is lost and must be restored from backup, the backup configuration information is available when the database is not mounted.
16.What are the differences between crosscheck and validate commands?
Validate command is to examine a backup set and report whether it can be restored. RMAN scans all of the backup pieces in the specified backup sets and looks at the checksum to verify that the contents are intact so that backup can be successfully restored if necessary.
Crosscheck command is to verify the status of backups and copies recorded in the RMAN repository against media such as disk or tape. The crosscheck command only processes files created on the same device type as the channel running crosscheck.
17.What is obsolete backup & expired backup?
A status of “expired” means that the backup piece or backup set is not found in the backup destination.
A status of “obsolete” means the backup piece is still available, but it is no longer needed. The backup piece is no longer needed since RMAN has been configured to no longer need this piece after so many days have elapsed, or so many backups have been performed.
18. What is the difference between hot backup & RMAN backup?
We need to put the database in Backup mode for Hot Backup.
RMAN Backup does not need the database to be in Backup mode.
19. Which Tables are have information required for RMAN list & report commands ?
V$BACKUP_FILES and recovery catalog views e.g., RC_DATAFILE_COPY or RC_ARCHIVED_LOG.
Below tables contain RMAN Catalog information: