Oracle Database RMAN Interview Questions

November 20, 2016

1. What is RMAN ?

Recovery Manager (RMAN) is a utility that can manage your entire Oracle backup and recovery activities.

Which Files can be backed up using rman?

Database Files (with RMAN)
Control Files (with RMAN)
Offline Redolog Files (with RMAN)
INIT.ORA (manually)
Password Files (manually)

2. When you take a hot backup putting Tablespace in begin backup mode, Oracle records SCN # from header of a database file. What happens when you issue hot backup database in RMAN at block level backup? How does RMAN mark the record that the block has been backed up ? How does RMAN know what blocks were backed up so that it doesn’t have to scan them again?

In 11g, there is Oracle Block Change Tracking feature. Once enabled; this new 10g feature records the modified since last backup and stores the log of it in a block change tracking file. During backups RMAN uses the log file to identify the specific blocks that must be backed up. This improves RMAN’s performance as it does not have to scan whole datafiles to detect changed blocks.
Logging of changed blocks is performed by the CTRW process which is also responsible for writing data to the block change tracking file. RMAN uses SCNs on the block level and the archived redo logs to resolve any inconsistencies in the datafiles from a hot backup. What RMAN does not require is to put the tablespace in BACKUP mode, thus freezing the SCN in the header. Rather, RMAN keeps this information in either your control files or in the RMAN repository (i.e., Recovery Catalog).

3. What are the Architectural components of RMAN?

1.RMAN executable
2.Server processes
3.Channels
4.Target database
5.Recovery catalog database (optional)
6.Media management layer (optional)
7.Backups, backup sets, and backup pieces

4. What are Channels?

A channel is an RMAN server process started when there is a need to communicate with an I/O device, such as a disk or a tape. A channel is what reads and writes RMAN backup files. It is through the allocation of channels that you govern I/O characteristics such as:
Type of I/O device being read or written to, either a disk or an sbt_tape
Number of processes simultaneously accessing an I/O device
Maximum size of files created on I/O devices
Maximum rate at which database files are read
Maximum number of files open at a time
5. Why is the catalog optional?

Because RMAN manages backup and recovery operations, it requires a place to store necessary information about the database. RMAN always stores this information in the target database control file. You can also store RMAN metadata in a recovery catalog schema contained in a separate database. The recovery catalog
schema must be stored in a database other than the target database.

6. What does complete RMAN backup consist of ?

A backup of all or part of your database. This results from issuing an RMAN backup command. A backup consists of one or more backup sets.

7. What is a Backup set?

A logical grouping of backup files — the backup pieces — that are created when you issue an RMAN backup command. A backup set is RMAN’s name for a collection of files associated with a backup. A backup set is composed of one or more backup pieces.

8. What is a Backup piece?

A physical binary file created by RMAN during a backup. Backup pieces are written to your backup medium, whether to disk or tape. They contain blocks from the target database’s datafiles, archived redo log files, and control files. When RMAN constructs a backup piece from datafiles, there are a several rules that it follows:
A datafile cannot span backup sets
A datafile can span backup pieces as long as it stays within one backup set
Datafiles and control files can coexist in the same backup sets
Archived redo log files are never in the same backup set as datafiles or control files RMAN is the only tool that can operate on backup pieces. If you need to restore a file from an RMAN backup, you must use RMAN to do it. There’s no way for you to manually reconstruct database files from the backup pieces. You must use RMAN to restore files from a backup piece.
9. What are the benefits of using RMAN?

1. Incremental backups that only copy data blocks that have changed since the last backup.
2. Tablespaces are not put in backup mode, thus there is noextra redo log generation during online backups.
3. Detection of corrupt blocks during backups.
4. Parallelization of I/O operations.
5. Automatic logging of all backup and recovery operations.
6. Built-in reporting and listing commands.

How do you install the RMAN recovery catalog?

November 20, 2016

By postgresdba

Steps to be followed:

1) Create connection string at catalog database.
2) At catalog database, create one new user or use existing user and give that user a recovery_catalog_owner privilege.
3)
Login into RMAN with connection string
a) export ORACLE_SID=
b) rman target catalog @connection string
4) rman> create catalog;
5) register database;

Oracle DBA Interview Questions and answwer– 5 in oracle

November 20, 2016

By postgresdba

1.This is what happens when we issued “backup validate ;”
RMAN>backup validate X;
Where X is database|tablespace|datafile spec. When using the VALIDATE option:

the WHOLE datafile is fully scanned
no physical backuppiece is written
VALIDATE runtime represents time spent scanning the input files
the difference in runtime between this and the normal backup represents time spent writing to the output device

2.What is the difference between physical and logical backups?
Logical backup implies taking “logical”copies of the database, i.e., the tables, indexes, synonyms, triggers, tablespaces, etc. Physical backup implies taking a “physical” or “actual”backup of the dat…
Logical backup means backup of tablespace in the form of binary but physical backup means backup of databfile, controlfile, logfile, spfile

3.What is the difference between obsolete RMAN backups and expired RMAN backups?
Obsolete: means the backup piece is still available,but it is no longer needed.It depends on retention policy,i,e.recovery window .

Expired: means the backup piece or backup set is not found in the backup destination.

4.Oracle database help us to reduce time for taking backup using followings
1) Oracle performs BLOCKlevel backup.
2) DBA can allocate channels which will start the backup activity in PARALLEL, but we need to ensure that not too many channels have been allocated. Number of allocated channel should not be more than number of CPU.

Oracle DBA Interview Questions and answwer– 4 in oracle

November 20, 2016

By postgresdba

1) What is the use of root.sh & oraInstRoot.sh?
Ans:
Changes ownership & permissions of oraInventory
Creating oratab file in the /etc directory
In RAC, starts the clusterware stack

2) How can you transport tablespaces across platforms with different endian formats?
Ans:
RMAN

3) What is transportable tablespace (and across platforms)?

4) What is xtss (cross platform transportable tablespace)?

5) What is the difference between restore point & guaranteed restore point?

6) How to find if your Oracle database is 32 bit or 64 bit?
Ans:
execute the command “file $ORACLE_HOME/bin/oracle”, you should see output like /u01/db/bin/oracle: ELF 64-bit MSB executable SPARCV9 Version 1

means you are on 64 bit oracle.

If your oracle is 32 bit you should see output like below
oracle: ELF 32-bit MSB executable SPARC Version 1

7) How to find opatch Version ?
Ans:
opatch is utility to apply database patch, In order to find opatch version execute”$ORACLE_HOME/OPatch/opatch version”

8) suppose i created one table after few days i did some insert,update how can i know when will i did ddl or dml operation is undergone on that table ?

ANS:

DDL:

select OWNER,OBJECT_NAME,CREATED,LAST_DDL_TIME,from dba_objects where OBJECT_NAME=’&object_name’;

DML:

SQL> select max(ora_rowscn), scn_to_timestamp(max(ora_rowscn)) from PS_PAY_TAX;

MAX(ORA_ROWSCN) SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN))
————— —————————————————————————
6016929147 04-JAN-12 08.41.20.000000000 AM

SQL>

SQL> select table_name, inserts, updates, deletes, timestamp,truncated from user_tab_modifications where table_name=’TEST1′;

TABLE_NAME INSERTS UPDATE DELETES TIMESTAMP TRU DROP_SEG

——— ——– ——- ——– ——————- — ——–

TEST1 4 0 0 04.08.2008 12:03:32 NO 0

Oracle DBA Interview Questions and answwer– 3 in oracle

November 20, 2016

By postgresdba

1) What are materialized view refresh types and which is default?
ANS:
Complete, fast, force(default)

2) How to find out when was a materialized view refreshed?

ANS:

Query dba_mviews or dba_mview_analysis or dba_mview_refresh_times
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,’YYYY-MM-DD HH24:MI:SS’) from dba_mviews;
(or)
SQL> select NAME, to_char(LAST_REFRESH,’YYYY-MM-DD HH24:MI:SS’) from dba_mview_refresh_times;
(or)
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,’YYYY-MM-DD HH24:MI:SS’) from dba_mview_analysis;

3) What is atomic refresh in mviews?
ANS:
From Oracle 10g, complete refresh of single materialized view can do delete instead of truncate.
To force the refresh to do truncate instead of delete, parameter ATOMIC_REFRESH must be set to false.

ATOMIC_REFRESH = FALSE, mview will be truncated and whole data will be inserted. The refresh will go faster, and no undo will be generated.
ATOMIC_REFRESH = TRUE (default), mview will be deleted and whole data will be inserted. Undo will be generated. We will have access at all times even while it is being refreshed.

SQL> EXEC DBMS_MVIEW.REFRESH(‘mv_emp’, ‘C’, atomic_refresh=FALSE);

4) How to find out whether database/tablespace/datafile is in backup mode or not?
ANS:
Query V$BACKUP view.

5) What is row chaining?
ANS:
If the row is too large to fit into an empty data block in this case the oracle stores the data for the row in a chain of one or more data blocks. Can occur when the row is inserted.

6) What is row migration?
ANS:
An update statement increases the amount of data in a row so that the row no longer fits in its data blocks.
Now the oracle tries to find another free block with enough space to hold the entire row if such a block is available oracle moves entire row to new block.

7) What are different types of partitions?
ANS:
With Oracle8, Range partitioning (on single column) was introduced.
With Oracle8i, Hash and Composite(Range-Hash) partitioning was introduced.
With Oracle9i, List partitioning and Composite(Range-List) partitioning was introduced.
With Oracle 11g, Interval partitioning, REFerence partitioning, Virtual column based partitioning, System partitioning and Composite partitioning [Range-Range, List-List, List-Range, List-Hash, Interval-Range, Interval-List, Interval-Interval] was introduced.

8) What is local partitioned index and global partitioned index?
ANS:
A local index is an index on a partitioned table which is partitioned in the exact same manner as the underlying partitioned table. Each partition of a local index corresponds to one and only one partition of the underlying table.
A global partitioned index is an index on a partitioned or non partitioned tables which are partitioned using a different partitioning key from the table and can have different number of partitions. Global partitioned indexes can only be partitioned using range partitioning.

9) How you will recover if you lost one/all control file(s)?

10) Why more archivelogs are generated, when database is begin backup mode?

ANS:

During begin backup mode datafile headers get freezed and as result row information cannot be retrieved as a result the entire block is copied to redo logs as a result more redo generated and more log switch and in turn more archive logs.
Normally only deltas (change vectors) are logged to the redo logs.
When in backup mode, Oracle will write complete changed blocks to the redo log files.

Mainly to overcome fractured blocks. Most of the cases Oracle block size is equal to or a multiple of the operating system block size.

e.g. Consider Oracle blocksize is 2k and OSBlocksize is 4k. so each OS Block is comprised of 2 Oracle Blocks. Now you are doing an update when your db is in backup mode. An Oracle Block is updating and at the same time backup is happening on the OS block which is having this particular DB block. Backup will not be consistent since the one part of the block is being updated and at the same time it is copied to the backup location. In this case we will have a fractured block, so as to avoid this Oracle will copy the whole OS block to redo logfile which can be used for recovery. Because of this redo generation is more.

11) What UNIX parameters you will set while Oracle installation?
ANS:
shmmax, shmmni, shmall, sem,

12) What is the use of inittrans and maxtrans in table definition?

13) What are differences between dbms_job and dbms_schedular?

Through dbms_schedular we can schedule OS level jobs also.

14) What are differences between dbms_schedular and cron jobs?

Through dbms_schedular we can schedule database jobs, through cron we can’t set.

15) Difference between CPU & PSU patches?

CPU – Critical Patch Update – includes only Security related patches.
PSU – Patch Set Update – includes CPU + other patches deemed important enough to be released prior to a minor (or major) version release.

16) What you will do if (local) inventory corrupted [or] opatch lsinventory is giving error?

17) What are the entries/location of oraInst.loc?
ANS:

/etc/oraInst.loc is pointer to central/local Oracle Inventory.

Oracle DBA Interview Questions and answwer– 2 in oracle

November 20, 2016

By postgresdba

1) What is a datafile?
ANS:
Every Oracle database has one or more physical datafiles. Datafiles contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the datafiles allocated for a database.

2) What are the contents of control file?

ANS:
Database name, SCN, LSN, datafile locations, redolog locations, archive mode, DB Creation Time, RMAN Backup & Recovery Details, Flashback mode.

3) What is the use of redo log files?

ANS:

Online redo logs serve to protect the database in the event of an instance failure. Whenever a transaction is committed, the corresponding redo entries temporarily stored in redo log buffers of the system global area are written to an online redo log file by the background process LGWR.

4) What are the uses of undo tablespace or redo segments?

ANS:
Undo records are used to:

Roll back transactions when a ROLLBACK statement is issued
Recover the database
Provide read consistency
Analyze data as of an earlier point in time by using Flashback Query
Recover from logical corruptions using Flashback features

5) How undo tablespace can guarantee retain of required undo data?

ANS:

Alter tablespace undo_ts retention guarantee;

6) What is 01555 – snapshot too old error and how do you avoid it?

ANS:

http://www.dba-oracle.com/t_ora_01555_snapshot_old.htm

7) What is the use/size of temporary tablespace?

ANS:

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables

8) What is the use of password file?

ANS:

If the DBA wants to start up an Oracle instance there must be a way for Oracle to authenticate this DBA. That is if (s)he is allowed to do so. Obviously, his password can not be stored in the database, because Oracle can not access the database before the instance is started up. Therefore, the authentication of the DBA must happen outside of the database. There are two distinct mechanisms to authenticate the DBA: using the password file or through the operating system.
The init parameter remote_login_passwordfile specifies if a password file is used to authenticate the DBA or not. If it set either to shared or exclusive a password file will be used.

9) How to create password file?

ANS:

$ orapwd file=orapwSID password=sys_password force=y nosysdba=y

10) How many types of indexes are there?
ANS:

Clustered and Non-Clustered

1.B-Tree index
2.Bitmap index
3.Unique index
4.Function based index

Implicit index and explicit index.
Explicit indexes are again of many types like simple index, unique index, Bitmap index, Functional index, Organisational index, cluster index.

11) What is bitmap index & when it’ll be used?

ANS:
Bitmap indexes are preferred in Data warehousing environment.
Preferred when cardinality is low.

12) What is B-tree index & when it’ll be used?

ANS:

B-tree indexes are preferred in OLTP environment.
Preferred when cardinality is high.

13) How you will find out fragmentation of index?

ANS:

AUTO_SPACE_ADVISOR_JOB will run in daily maintenance window and report fragmented indexes/Tables.

analyze index validate structure;

This populates the table ‘index_stats’. It should be noted that this table contains only one row and therefore only one index can be analysed at a time.

An index should be considered for rebuilding under any of the following conditions:

* The percentage of deleted rows exceeds 30% of the total, i.e. if
del_lf_rows / lf_rows > 0.3.
* If the ‘HEIGHT’ is greater than 4.
* If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the index should be rebuilt.

14) What is the difference between delete and truncate?]

ANS:

Truncate will release the space. Delete won’t.
Delete can be used to delete some records. Truncate can’t.
Delete can be rollbacked.
Delete will generate undo (Delete command will log the data changes in the log file where as the truncate will simply remove the data without it. Hence data removed by Delete command can be rolled back but not the data removed by TRUNCATE).
Truncate is a DDL statement whereas DELETE is a DML statement.
Truncate is faster than delete.

15) What’s the difference between a primary key and a unique key?
ANS:

Both primary key and unique enforce uniqueness of the column on which they are defined.
But by default primary key creates a clustered index on the column, where unique key creates a nonclustered index by default.
Primary key doesn’t allow NULLs, but unique key allows one NULL only.

16) What is the difference between schema and user?

Schema is collection of user’s objects.

17) What is the difference between SYSDBA, SYSOPER and SYSASM?
ANS:

SYSOPER can’t create and drop database.
SYSOPER can’t do incomplete recovery.
SYSOPER can’t change character set.
SYSOPER can’t CREATE DISKGROUP, ADD/DROP/RESIZE DISK
SYSASM can do anything SYSDBA can do.

18) What is the difference between SYS and SYSTEM?
SYSTEM can’t shutdown the database.
SYSTEM can’t create another SYSTEM, but SYS can create another SYS or SYSTEM.

19) What is the difference between view and materialized view?

View is logical, will store only the query, and will always gets latest data.
Mview is physical, will store the data, and may not get latest data.

Oracle DBA Interview Questions and answwer– 1 in oracle

November 20, 2016

By postgresdba

1) What is an instance?
ANS:

SGA + background processes.

2) What is SGA?
ANS:
System/Shared Global Area.

3) What is PGA (or) what is pga_aggregate_target?
ANS:
Programmable Global Area.

4) What are new memory parameters in Oracle 10g?
ANS:
SGA_TARGET, PGA_TARGET

5) What are new memory parameters in Oracle 11g?
ANS:
MEMORY_TARGET

6) What are the mandatory background processes?
ANS:
DBWR LGWR SMON PMON CKPT RECO.

7) What are the optional background processes?
ANS:

ARCH, MMAN, MMNL, MMON, CTWR, ASMB, RBAL, ARBx etc.

8) What are the new background processes in Oracle 10g?
ANS:
MMAN MMON MMNL CTWR ASMB RBAL ARBx

9) What are the new features in Oracle 9i?
http://satya-dba.blogspot.com/2009/01/whats-new-in-9i.html

10) . What are the new features in Oracle 10g?
http://satya-dba.blogspot.com/2009/01/whats-new-in-10g.html

11). What are the new features in Oracle 11g?
http://satya-dba.blogspot.com/2009/01/whats-new-in-11g.html

12). What are the new features in Oracle 11g R2?
http://satya-dba.blogspot.com/2009/09/whats-new-in-11g-release-2.html

13) What process will get data from datafiles to DB cache?

ANS:
Server process

14) What background process will writes data to datafiles?

ANS:
DBWR

15) What background process will write undo data?

ANS:
DBWR

16) What are physical components of Oracle database?

ANS:

Oracle database is comprised of three types of files. One or more datafiles, two or more redo log files, and one or more control files.
Password file and parameter file also come under physical components.

17) What are logical components of Oracle database?
ANS:
Blocks, Extents, Segments, Tablespaces.

18) What is segment space management?
ANS:
LMTS and DMTS.

19) What is extent management?

ANS:

Auto and Manual.

20) What are the differences between LMTS and DMTS?

Tablespaces that record extent allocation in the dictionary are called dictionary managed tablespaces,
and tablespaces that record extent allocation in the tablespace header are called locally managed tablespaces.

Performance Tuning interviewsquestions and answwer– 1 in oracle

November 19, 2016

By postgresdba

1) What you’ll check whenever user complains that his session/database is slow?

2) What is the use of statistics?

ANS:

Optimizer statistics are a collection of data that describe more details about the database and the objects in the database. These statistics are used by the query optimizer to choose the best execution plan for each SQL statement.

3) How to generate explain plan?

ANS:

EXPLAIN PLAN FOR ;

4) How to check explain plan of already ran SQLs?

ANS:

select * from TABLE(dbms_xplan.display_cursor(‘&SQL_ID’));

5) How to find out whether the query has ran with RBO or CBO?

ANS:

ts very simple..from sql alone you cannot tell wheather they used CBO or RBO..its like this

If your optimizer_mode=choose
then all sql statements will use the CBO
when the tables they are acessing will have statistics collected..
then all sql statements will use the RBO
when the tables they are acessing will have no statistics..

6) What are top 5 wait events (in AWR report) and how you will resolve them?

ANS:

http://satya-dba.blogspot.in/2012/10/wait-events-in-oracle-wait-events.html

db file sequential read => tune indexing, tune SQL (to do less I/O), tune disks, increase buffer cache. This event is indicative of disk contention on index reads. Make sure all objects are analyzed. Redistribute I/O across disks. The wait that comes from the physical side of the database. It related to memory starvation and non selective index use. Sequential read is an index read followed by table read because it is doing index lookups which tells exactly which block to go to.
db file scattered read => disk contention on full table scans. Add indexes, tune SQL, tune disks, refresh statistics, and create materialized view. Caused due to full table scans may be because of insufficient indexes or unavailability of updated statistics.
db file parallel read => tune SQL, tune indexing, tune disk I/O, increase buffer cache. If you are doing a lot of partition activity then expect to see that wait even. It could be a table or index partition.
db file parallel write => if you are doing a lot of partition activity then expect to see that wait even. It could be a table or index partition.
db file single write => if you see this event than probably you have a lot of data files in your database.

control file sequential read
control file parallel write

log file sync => committing too often, archive log generation is more. Tune applications to commit less, tune disks where redo logs exist, try using nologging/unrecoverable options, log buffer could be too large.
log file switch completion => May need more log files per group.
log file parallel write => Deals with flushing out the redo log buffer to disk. Disks may be too slow or have an I/O bottleneck. Look for log file contention.
log buffer space => Increase LOG_BUFFER parameter or move log files to faster disks. Tune application, use NOLOGGING, and look for poor behavior that updates an entire row when only a few columns change.
log file switch (checkpoint incomplete) => May indicate excessive db files or slow IO subsystem.
log file switch (archiving needed) => Indicates archive files are written too slowly.
redo buffer allocation retries => shows the number of times a user process waited for space in the redo log buffer.
redo log space wait time => shows cumulative time (in 10s of milliseconds) waited by all processes waiting for space in the log buffer.

buffer busy waits/ read by other session => Increase DB_CACHE_SIZE. Tune SQL, tune indexing, we often see this event along with full table scans, if the SQL is inserting data, consider increasing FREELISTS and/or INITRANS, if the waits are on segment header blocks, consider increasing extent sizes.
free buffer waits => insufficient buffers, process holding buffers too long or i/o subsystem is over loaded. Also check you db writes may be getting clogged up.
cache buffers lru chain => Freelist issues, hot blocks.
no free buffers => Insufficient buffers, dbwr contention.

latch free
latch: session allocation
latch: in memory undo latch => If excessive could be bug, check for your version, may have to turn off in memory undo.
latch: cache buffer chains => check hot objects.
latch: cache buffer handles => Freelist issues, hot blocks.
direct path write => You wont see them unless you are doing some appends or data loads.
direct Path reads => could happen if you are doing a lot of parallel query activity.
direct path read temp or direct path write temp => this wait event shows Temp file activity (sort,hashes,temp tables, bitmap) check pga parameter or sort area or hash area parameters. You might want to increase them.
library cache load lock
library cache pin => if many sessions are waiting, tune shared pool, if few sessions are waiting, lock is session specific.
library cache lock => need to find the session holding the lock, look for DML manipulating an object being accessed, if the session is trying to recompile PL/SQL, look for other sessions executing the code.
undo segment extension => If excessive, tune undo.
wait for a undo record => Usually only during recovery of large transactions, look at turning off parallel undo recovery.

enque wait events => Look at V$ENQUEUE_STAT

SQL*Net message from client
SQL*Net message from dblink
SQL*Net more data from client
SQL*Net message to client
SQL*Net break/reset to client

7) What are the init parameters related to performance/optimizer?

ANS:

optimizer_mode = choose
optimizer_index_caching = 90
optimizer_index_cost_adj = 25
optimizer_max_permutations = 100
optimizer_use_sql_plan_baselines=true
optimizer_capture_sql_plan_baselines=true
optimizer_use_pending_statistics = true;
optimizer_use_invisible_indexes=true
_optimizer_connect_by_cost_based=false
_optimizer_compute_index_stats= true;

8) What are the values of optimizer_mode init parameters and their meaning?

ANS:

optimizer_mode = choose

9) What is the use of AWR, ADDM, ASH?

10) How to generate AWR report and what are the things you will check in the report?

11). How to generate ADDM report and what are the things you will check in the report?

12). How to generate ASH report and what are the things you will check in the report?

13) How to generate TKPROF report and what are the things you will check in the report?

ANS:

The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. Use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.

Export/Import – Data Pump interviews questions and answwer– 1 in oracle

November 19, 2016

By postgresdba

1) What is use of CONSISTENT option in exp?

Cross-table consistency. Implements SET TRANSACTION READ ONLY. Default value N.

2) What is use of DIRECT=Y option in exp?

Setting direct=yes, to extract data by reading the data directly, bypasses the SGA,
bypassing the SQL command-processing layer (evaluating buffer), so it should be faster. Default value N.

3) What is use of COMPRESS option in exp?

Imports into one extent. Specifies how export will manage the initial extent for the table data.
This parameter is helpful during database re-organization.
Export the objects (especially tables and indexes) with COMPRESS=Y.
If table was spawning 20 Extents of 1M each (which is not desirable, taking into account performance), if you export the table with COMPRESS=Y, the DDL generated will have initial of 20M. Later on when importing the extents will be coalesced.
Sometime it is found desirable to export with COMPRESS=N, in situations where you do not have contiguous space on disk (tablespace), and do not want imports to fail.

4) How to improve exp performance?

ANS:

a). Set the BUFFER parameter to a high value. Default is 256KB.
b). Stop unnecessary applications to free the resources.
c). If you are running multiple sessions, make sure they write to different disks.
d). Do not export to NFS (Network File Share). Exporting to disk is faster.
e). Set the RECORDLENGTH parameter to a high value.
f). Use DIRECT=yes (direct mode export).

5) How to improve imp performance?

ANS:

a). Place the file to be imported in separate disk from datafiles.
b). Increase the DB_CACHE_SIZE.
c). Set LOG_BUFFER to big size.
d). Stop redolog archiving, if possible.
e). Use COMMIT=n, if possible.
f). Set the BUFFER parameter to a high value. Default is 256KB.
g). It’s advisable to drop indexes before importing to speed up the import process or set INDEXES=N and building indexes later on after the import.
Indexes can easily be recreated after the data was successfully imported.
h). Use STATISTICS=NONE
i). Disable the INSERT triggers, as they fire during import.
j). Set Parameter COMMIT_WRITE=NOWAIT(in Oracle 10g) or COMMIT_WAIT=NOWAIT (in Oracle 11g) during import.

6) What is use of INDEXFILE option in imp?

ANS:

Will write DDLs of the objects in the dumpfile into the specified file.

7) What is use of IGNORE option in imp?

ANS:

Will ignore the errors during import and will continue the import.

8) What are the differences between expdp and exp (Data Pump or normal exp/imp)?

ANS:

Data Pump is server centric (files will be at server).
Data Pump has APIs, from procedures we can run Data Pump jobs.
In Data Pump, we can stop and restart the jobs.
Data Pump will do parallel execution.
Tapes & pipes are not supported in Data Pump.
Data Pump consumes more undo tablespace.
Data Pump import will create the user, if user doesn’t exist.

9) Why expdp is faster than exp (or) why Data Pump is faster than conventional export/import?

Data Pump is block mode, exp is byte mode.
Data Pump will do parallel execution.
Data Pump uses direct path API.

10) How to improve expdp performance?

ANS:

Using parallel option which increases worker threads. This should be set based on the number of cpus.

11) How to improve impdp performance?

ANS:

Using parallel option which increases worker threads. This should be set based on the number of cpus.

12) In Data Pump, where the jobs info will be stored (or) if you restart a job in Data Pump, how it will know from where to resume?

Whenever Data Pump export or import is running, Oracle will create a table with the JOB_NAME and will be deleted once the job is done. From this table, Oracle will find out how much job has completed and from where to continue etc.

Default export job name will be SYS_EXPORT_XXXX_01, where XXXX can be FULL or SCHEMA or TABLE.
Default import job name will be SYS_IMPORT_XXXX_01, where XXXX can be FULL or SCHEMA or TABLE.

13) What is the order of importing objects in impdp?

Tablespaces
Users
Roles
Database links
Sequences
Directories
Synonyms
Types
Tables/Partitions
Views
Comments
Packages/Procedures/Functions
Materialized views

14) How to import only metadata?

ANS:

CONTENT= METADATA_ONLY

15) How to import into different user/tablespace/datafile/table?

ANS:

REMAP_SCHEMA
REMAP_TABLESPACE
REMAP_DATAFILE
REMAP_TABLE
REMAP_DATA

16) Using Data Pump, how to export in higher version (11g) and import into lower version (10g), can we import to 9i?

ANS:

Import data pump can always read export datapump dumpfile sets created by older versions of database. In your case it works, normal expdp on 10g and impdp on 11g
VERSION parameter in datapump is for other way around, if you want to import data taken from 11g into 10g database you need
to specify VERSION while taking backup.

17) How to do transport tablespaces (and across platforms) using exp/imp or expdp/impdp?

ANS: [http://satya-dba.blogspot.in/2010/01/oracle-transportable-tablespaces-tts.html ]

We can use the transportable tablespaces feature to copy/move subset of data (set of user tablespaces), from an Oracle database and plug it in to another Oracle database. The tablespaces being transported can be either dictionary managed or locally managed.

With Oracle 8i, Oracle introduced transportable tablespace (TTS) technology that moves tablespaces between databases. Oracle 8i supports tablespace transportation between databases that run on same OS platforms and use the same database block size.

With Oracle 9i, TTS (Transportable Tablespaces) technology was enhanced to support tablespace transportation between databases on platforms of the same type, but using different block sizes.

With Oracle 10g, TTS (Transportable Tablespaces) technology was further enhanced to support transportation of tablespaces between databases running on different OS platforms (e.g. Windows to Linux, Solaris to HP-UX), which has same ENDIAN formats. Oracle Database 10g Release 1 introduced cross platform transportable tablespaces (XTTS), which allows data files to be moved between platforms of different endian format. XTTS is an enhancement to the transportable tablespace (TTS). If ENDIAN formats are different we have to use RMAN (e.g. Windows to Solaris, Tru64 to AIX).

select * from v$transportable_platform order by platform_id;

18)
How to determine the Schemas inside an Oracle Data Pump Export file ?

strings dumpfile.dmp | grep SCHEMA_LIST
(or)
$ strings myfile.dmp|more