Oracle DBA interview questions

Hi aspiring DBA's, I will be posting the commonly asked interview questions and answers here, be updated regularly. I'm not an expert, do correct me if I'm wrong anywhere, much appreciated, Thank you for reading and learning something today!

Disclaimer: The following questions and solutions are intended for a quick refresh on the interview questions which may or may not be correct 100%. Use them at your own risk.

Non-Technical
Q1. Introduce yourself, brief the previous/current projects.
Q2. Daily activities you do in your current project?
Q3. What is the backup strategy used in your project?

Technical

Q1. If you are given an already exported dmp file with no log details, how do you check the contents of the dmp file?
Solution1:

imp scott/tiger file=/tmp/abc.dmp show=Y

This above command will show the contents inside the dmp file without actually importing it into the database, we can then decide, what all we want to import.


Q2. How do you proceed with the "ORA-01113: file X needs media recovery"?
Solution2:
ORA-01113: file X needs media recovery

Oracle mounts the database, but refuses to open the database with an ORA-01113: file X needs media recovery.

[oracle@primary ~]$ export ORACLE_SID=ORCL
[oracle@primary ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 21 14:17:51 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             507514448 bytes
Database Buffers          335544320 bytes
Redo Buffers                5132288 bytes
Database mounted.
Database opened.
SQL> 
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.dbf'

SQL> !oerr ora 01113
01113, 00000, "file %s needs media recovery"
// *Cause:  An attempt was made to online or open a database with a file that
//         is in need of media recovery.
// *Action: First apply media recovery to the file.

Resolution
Startup the database with the mount option
SQL> startup mount
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             507514448 bytes
Database Buffers          335544320 bytes
Redo Buffers                5132288 bytes
Database mounted.

Find the name of the redo log file which belongs to the active group
SQL> SELECT MEMBER FROM V$LOG G, V$LOGFILE F WHERE G.GROUP# = F.GROUP# AND G.STATUS = 'CURRENT';

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/redo01.log

Using the backup control file, start cancel based recovery. Oracle will suggest a non-existing archive log, we will ignore this suggestion from Oracle and specify the log file name {with full path} that we get from the above query

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
ORA-00279: change 31527825 generated at 03/21/2015 14:24:24 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORCL/archivelog/arch1_7.dbf
ORA-00280: change 31527825 for thread 1 is in sequence #4

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/ORCL/redo01.log
Log applied.
Media recovery complete.

Now we open the database in RESETLOGS mode. Oracle recommends to reset the online logs after incomplete recovery or recovery with a backup control file
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.


Now take a full backup.


Q3. What is the difference between full backup and level 0 backup?
Solution3. Both being similar, but not same, level 0 backup can be used as a parent backup for the level 1 backups, full backups cannot be.