Oracle expdp and impdp - Some useful tips

Introduction

Many of you may be using Oracle Data Pump Export (expdp) and Data Pump Import (impdp) for logical backups of objects/schemas as well as the full databases for say, performing a database platform migration. I have some useful tips for you in an Oracle 11g R1 database.

1) How to run expdp as sysdba

There are two ways of running it using sysdba. Either by giving the option in the command line, or giving the option when it prompts for the username. Note the escape characters in the command line

i) expdp \"/ as sysdba\" parfile=myparfile.lst
ii) expdp parfile=myparfile.lst

Export: Release 11.1.0.7.0 - 64bit Production on Thursday, 15 May, 2011 13:11:57

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Username: / as sysdba

2) How to use nohup

nohup lets one run a operating system command in the background. The process won't be terminated even if the operating system (os) user has logged out. Using nohup for expdp can give errors unless a parfile (parameter file) is used.

The below is an example of using nohup to perform a full database export with the parfile option.

The parfile (fulldb_export.par) has the following contents

dumpfile=DATA_PUMP_DIR:mydb_full_%U.dmp
LOGFILE=DATA_PUMP_DIR:mydb_full_dump.log
parallel=4
filesize=40G
full=Y

nohup expdp \"/ as sysdba\" parfile=fulldb_export.par &

3) How to exclude schemas

Get the syntax right when you want to exclude a particular schema during the export or import process. In one wants to exclude the system schema, one has to use the EXCLUDE=SCHEMA:"in\('SYSTEM'\)" option.

4) How to exclude multiple schemas

If one wants to exclude multiple schemas during an export or import process, for example, SYSTEM and FIN schemas, one has to use the EXCLUDE=SCHEMA:"in\('SYSTEM','FIN'\)" option

5) Avoid filling up the $ORACLE_HOME location

By default, the DATA_PUMP_DIR (in dba_directories) points to $ORACLE_HOME/rdbms/log directory. If this location does not have much free space left, the $ORACLE_HOME can become 100% full in the case of large dump files, causing problems to the normal database operations. To avoid this issue, consider creating a symbolic link from $ORACLE_HOME/rdbms/log to a location (for e.g /lot_of_freespace) where you have lot of free space available.

cd $ORACLE_HOME/rdbms
mv log log.old
ln -s /lot_of_freespace log

6) Consider COMPRESSION option to reduce the export dump file sizes

Oracle 11g introduced the concept of compressing contents in the export dump file. I am impressed with the compression algorithm because in the event of an import operation (impdp), Oracle decompresses automatically (No additional options required). This is similar to the RMAN backup compression technique that is introduced in Oracle 10g. I have noticed good data compression ratios when used with the expdp command (Almost 1:10 to 1:15). However, it is not well documented in the Utilities guide whether COMPRESSION feature requires a separate license from Oracle (Oracle Advanced Compression)

The option for expdp is COMPRESSION=ALL. It is not applicable for impdp.

Flash Recovery Area - Disable during recovery

Introduction

Flash Recovery Area (FRA) is one my favorite features in the Oracle Database. The FRA helps in automatic management of archive log files, RMAN backup pieces, control file autobackups and the flash back log files (if the FLASHBACK DATABASE is turned on). The FRA nicely creates all these files with well defined sub directory structure (based on dates) and also automatically deletes obsolete backup pieces (if the RMAN recovery window is set).

The other day, I was tasked with creating a clone of an Oracle 11g R1 (11.1.0.7) production database using the RMAN restore and recovery commands on the destination host. As usual, i made a copy of the production database's parameter file and edited it as per the new database (clone) settings. I did NOT remove any existing parameters though and retained all parameters including the FRA parameters viz. DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE

Problem

Our RMAN restore was not successful as just before the restore started, Oracle strangely deleted two datafiles (as per the alertlog) and then RMAN first created two empty datafiles with the same name which were deleted and then started the actual restore. These two datafiles were created years ago on the production database and they were online datafiles. Nothing wrong with them.


**FROM THE ALERT LOG***

Thu Mar 24 10:49:24 2011
Deleted Oracle managed file +ORADATA/prod_myhost_db/datafile/fints_index2.1887.709799705
Deleted Oracle managed file +ORADATA/prod_myhost_db/datafile/fints_index2.1906.723204215

**FROM THE RMAN LOG**

creating datafile file number=1630 name=+ORADATA/prod_myhost_db/datafile/fints_index2.1887.709799705
creating datafile file number=1649 name=+ORADATA/prod_myhost_db/datafile/fints_index2.1906.723204215

channel d1: starting datafile backup set restore
channel d1: specifying datafile(s) to restore from backup set
channel d1: restoring datafile 00030 to +ORADATA/prod_myhost_db/datafile/sales.1690.700575591
channel d1: restoring datafile 00063 to +ORADATA/prod_myhost_db/datafile/argus2_index2.263.700567185
channel d1: restoring datafile 00083 to +ORADATA/prod_myhost_db/datafile/bits_index2.1800.700576857
channel d1: restoring datafile 00112 to +ORADATA/prod_myhost_db/datafile/cbo_data2.1826.700576943
....
....
....

Solution

I got this working by disabling the FRA. I commented the two FRA parameters DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE in the destination database's init parameter file. Then I recreated the +ORADATA ASM diskgroup (formatted the ASM disks to remove data from the other files that got restored so far) and restarted the restore. This time the restore was successful.

Conclusion

Turn off the FRA (remove/comment the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE) on the destination database until the cloning process is over (restore and recovery). Once the new database is opened successfully, the FRA can be turned on again.

Database Administrator is TOP 7 out of 100 BEST Jobs in America

CNN.com partnered with PayScale.com and come out with a list of the TOP 100 best jobs in America. The criteria for selection included great pay and growth prospects.

Guess what, Database Administrators are again in the TOP TEN. It is ranked 7th best out of the TOP 100 in USA.

Apart from Database Administrators, the only other IT job in the TOP TEN is Software Architect.

For a full list, click here.

Oracle DBA Training

Interested in Oracle DBA Training and Oracle Apps DBA (Oracle Applications DBA) Training ?. If anyone is interested in getting trained in these skills, please register for courses offered by DBA University.

All the courses are covered with the latest topics, practical assignments as well as access to a world class LAB environment along with job oriented training.

Both on-site and online training are available.

Database Administrator stands 5th in the 'MOST DIFFICULT TO FILL' IT Job

I wrote in March 2008 (just before the onset of the worst recession in USA) that statistics showed Oracle Database is the skill with the biggest demand in the IT Job Market. Click here to read that post.

With the job market in USA still not recovered fully, the latest July 2010 dice.com monthly job report says that Database Administrators stand 5th in the 'MOST DIFFICULT to FILL' IT Job. This shows that IT organizations continue to have a tough time to find quality Database Administrators even in the ongoing recession in USA.

The same report states that DBAs make $10,000 more than the average IT job paycheck in the United States. Same applies for pay raises. DBAs get double the average yearly pay raises in the USA. Click here to download the latest IT job market report.

CNN.com partnered with the leading job portal www.CareerBuilder.com and ranked DBAs among the TOP TEN most IN-DEMAND professions for the 2009-2016 period with a projected growth rate of 29% per year. Click here for more details.

Question on restoring SPFILE after a complete database server crash

Introduction

I am posting a database recovery related question asked by one my blog's visitors' last week for restoring an spfile. A few additional steps are necessary if the database is using ASM technology.

[06:19] sunil: i had a catalog db and i m taking the bkp of target db in to tape drive, all of a sudden my target server crashed(means i lost everything includibg spfile,now i want to built a new server and want to restore the bkp from tape drive,how it'll happen.

[06:19] sunil: how i can restore my spfile?

Sunil,

It is not very tough to restore the spfile. Please try the below commands to restore your spfile. For simplicity, I am making the following assumptions.

1) You have used RMAN and a seperate CATALOG database for your backup strategy.
2) You still know the DBID of your "crashed" database. If you don't have your backup logfile, you can still find the DBID by querying the RMAN Catalog. Let it be 123456789
3) You have made the tapes containing your backup accessible to the new server.

A)

rman
connect target /
connect catalog catuser/catuser@catdb
set DBID 123456789
startup force nomount;
exit


B)

rman
connect target /
connect catalog catuser/catuser@catdb
set DBID 123456789
run
{
allocate channel t1 type 'SBT_TAPE';
restore spfile;
}
exit

c)

sqlplus '/ as sysdba'
create pfile from spfile;
shutdown immediate;

D)

Restore CONTROLFILE and update PFILE with new controlfile location

E)
The following additional steps must be performed if you have ASM for your database.
Let +ASMDATA be the diskgroup name for storing the spfile.

sqlplus '/ as sysdba'
create spfile='+ASMDATA' from pfile;
exit

cd $ORACLE_HOME/dbs
mv pfile pfile.old

Create a new pfile with the below contents after verifying the exact name of the restored spfile from v$asm_alias view
SPFILE='+ASMDATA/DBNAME/parameterfile/spfile_name_in_asm_alias_view'

rm the spfile on disk in $ORACLE_HOME/dbs directory.

The below command will startup the database using the above pfile which points to the actual SPFILE

sqlplus '/ as sysdba'
startup mount;

ASM for Oracle 11g with R12 E-Business Suite

Introduction

One of my visitors' posed a question in my weekly online chat in this site.

>> Tom asked

Hi Srinivas, we are planning to utilizing ASM 11gR2 DB for R12 ebusiness, is there any document that you can point me to for best practice for setting the ASM disks? Currently have only 2 disks; 1 is DATA and the other is RECOVERY. Data disk is for transaction and RECOVERY is for redo, archive log, etc. Can you point me to the right direction. thanks, Tom.

I am making a blogpost for this question because it is ASM is such an important consideration for today's enterprise databases, both for manageability and high availability of data.

Tom,

You have taken a good decision to implement ASM for your database. We had very good success with ASM and its also fun to work with ASM. Makes your life easy.

You should put even redo also as part of the DATA diskgroup.

1) DATA diskgroup = Datafiles + online redologs + controlfiles
2) RECOVERY diskgroup = just the archived redologs.

Are you using ASM on Linux platform ? ASM is optimized for Linux platform. Oracle supplies an utility called ASMLib for the Linux platform, an utility that further automates ASM management and administration.


Also when you create the diskgroup, I prefer you to use "EXTERNAL REDUNDANCY". Your organization must already be using SAN Fabric for your database. For example, if you use EMC Symmetrix as the SAN solution, you must utilize its inbuilt redundancy feature. So you let EMC handle redundancy instead of Oracle.