How to differentiate the dmp files, whether the file belongs to normal classic exp or the new expdp utility?

SQL> CREATE OR REPLACE DIRECTORY DATAPUMP AS '/tmp/export';

SQL> GRANT READ, WRITE ON DIRECTORY DATAPUMP TO SCOTT;

SQL> GRANT EXP_FULL_DATABASE, IMP_FULL_DATABASE TO SCOTT;

Run the command for getting a sample expdp dmp file:
expdp scott tables=scott.emp directory=datapump dumpfile=expdp.dmp logfile=expdpemp.log

[oracle@primary ~]$ expdp scott tables=scott.emp directory=datapump dumpfile=expdp.dmp logfile=expdpemp.log

Export: Release 11.2.0.1.0 - Production on Mon Mar 30 13:02:54 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** tables=scott.emp directory=datapump dumpfile=expdp.dmp logfile=expdpemp.log 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /tmp/export/expdp.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 13:03:22

Now, Run the command for getting a sample exp dmp file
exp userid=scott tables=scott.emp file=/tmp/export/exp.dmp log=/tmp/export/expemp.log

[oracle@primary ~]$ exp userid=scott tables=scott.emp file=/tmp/export/exp.dmp log=/tmp/export/expemp.log

Export: Release 11.2.0.1.0 - Production on Mon Mar 30 17:00:44 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                            EMP         14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
[oracle@primary ~]$ cd /tmp/export/

Now we have the exp dmp and the expdp dmp files as below:
[oracle@primary export]$ ls -ltr
total 144
-rw-rw-r-- 1 oracle oracle   1345 Mar 30 13:03 expdpemp.log
-rw-rw---- 1 oracle oracle 118784 Mar 30 13:03 expdp.dmp
-rw-rw-r-- 1 oracle oracle    579 Mar 30 17:00 expemp.log
-rw-rw-r-- 1 oracle oracle  16384 Mar 30 17:00 exp.dmp

Now type the below linux OS command to open up the exported files, eg. strings filename | more


[oracle@primary export]$ strings expdp.dmp | more
"SCOTT"."SYS_EXPORT_TABLE_01"
Linuxi386/Linux-2.0.34-8.1.0
ORCL
WE8MSWIN1252
11.02.00.00.00
001:001:000001:000001


[oracle@primary export]$ strings exp.dmp | more
EXPORT:V11.02.00
DSCOTT
RTABLES
8192
                                       Mon Mar 30 17:0:48 2015/tmp/export/exp.dmp
#C##
#C##
+00:00


You can clearly make out the difference from the above files now, we can then use the suitable import utility, either imp or impdp based on your above output. Hope this was useful and informative, though a very very small article.

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.