How to perform a full database export using original export/import utility?

Hello,

In my blog, there is a post already available on how to perform a full database export using the new and enhanced feature of Oracle Database 10g Export Utility, Data Pump – expdp/impdp. However, I’m still being requested, by my blog viewers and OTN forum users, to place a procedure on how to perform a full database export using traditional export utility – exp/imp. However, this is not the complete informative post for export/import utility; so, please refer the Oracle Documents links specified at the end of this post.

Before starting off with the steps, to perform the export at the database level, let’s have clear understanding on the following questions.

Can I perform or consider the export of my database as a backup approach?

Absolutely *NO*, because, the import can be done only up to the time the export was originally taken, i.e. the export dump cant be used to import until the point in time (up-to-date) and will not be imported the updates after the export done. This is not acceptable for Production Databases. So, never treat that, taking the export of the database is part of backup strategy of your Production database. In fact, a couple of other approaches are available to perform the backup of your database using the following ways – Cold Backup (Shutdown/Copy/Startup), Hot Backup (ALETER TABLESPACE ….BEGIN/END BACKUP), and RMAN backup methods.

Situations: When and why to perform a full database export.

* Export/import is only the possible way to construct a similar database on remote servers regardless of Operating System compatibility i.e. cross platform (Export on Linux and Import on Windows Systems).
* To migrate the database from one Operating System to another i.e. (Linux/Unix to Windows), and to upgrade the database from one version to another i.e. 8i-9i or 9i-10g, but not vice versa.
* If you are on Oracle 10g, above two points can be possible, using RMAN Convert Commands i.e. to construct or move a database from one OS to another OS i.e. Cross platform.
* When there is a requirement to clone a database on another test or development server, then take a full database mode export dump and create a database with the similar settings of target database, and import the dump in source database.

Simple Steps: how to perform a full database export using export utility.

* Use either system user or any other database user who has the EXP_FULL_DATABASE privilege.
* Set the NLS_LANG environment variable according the database character set and language details.
SQL> select * from nls_database_parameters
2 where parameter in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');

PARAMETER VALUE
------------------------------ ----------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET WE8ISO8859P1

Windows (Dos Prompt):
C:\> set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

Unix/Linux:
$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

* Start the export with following command and options.

exp system/password@mydb file=c:\exportdmp\exp_fulldb_MYDB_27Aug08.dmp
full=y log= c:\exportdmp\exp_fulldb_MYDB_27Aug08.log

Note: This is just a simple export command to perform the full database export. I would request and suggest you to refer Oracle Documentations on export/import and their options. Check the references.

Help on Export and Import:

Windows:
C:\> exp help=y
C:\> imp help=y

Linux/Unix.
$ exp help=y
$ imp help=y

References:

Oracle 10g :
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm

Oracle 9i:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/part1.htm#435787

Oracle 8i:
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/ch01.htm
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/ch02.htm

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/

Cloning and Refreshing an Oracle Database

Dear Friends and Blog Readers,

I have been asked many times about the Cloning and Refresh process of Oracle Database by emails of blog readers and the users of the Oracle Technology Network (OTN) Forums.

Even though the information about Cloning and Refreshing a Database process available over web widely or has already been discussed. Here, in this post, I would like to explain and provide the information on the following Questions about Cloning and Refreshing of a Database with my simple terms.

Terms used in this post:

Source System - the system to be cloned - Production
Target System - the newly created (or cloned) system – Non Production
Production Database – PROD
Test Database – TEST
Development Database - DEV

What is a Database Clone?

* A database clone is an activity/procedure which is performed by every DBA on regular basis or when there is a requirement or request to do so from the different departments i.e. Test/Development teams.

* Cloning is nothing but creating a copy of production system in to a test or development environment. i.e. Having an exact image of production database in test area.

* Cloning is a procedure for preparing and creating a test or development servers with the copy of Oracle production database for testing upgrades, migrating an existing system to new hardware.

* A cloning process includes a copy of Oracle Home (Directories and Binaries) backup and Database (Database related files) backup to prepare the instance on another server.

* Though, it is possible to clone a database on the same server, Oracle doesn’t suggest to clone a database on the same server, where the production database is running.

What is a Database Refresh?

* A Database Refresh is also referred to as a database clone. However, we don’t clone Oracle Home rather we clone the Database as refresh.

* Refreshing a database is something like applying the changes or updates of production database to the database where the database is already cloned. i.e. let’s say you have cloned a database a month back, and now you are asked for doing refresh of a database, then you will perform the backup of database and prepare the clone the instance again on test server. This is nothing but refreshing.

* Refreshing of a particular table, group of tables, schema, or tablespace will be done using traditional export/import, transportable Tablespaces, or data pump methods.

* When an Oracle patch is applied on Production System, or in doubt, you have to prepare and clone the database again with the copy of Oracle Home (Directories and Binaries) Backup and Database (Database related files) Backup to prepare the instance.

* The difference between Cloning and Refreshing is that cloning process includes Oracle Home and database Clone; where as Refreshing process only includes database clone.

* If seen, the words, Clone and Refresh are used interchangeably for the sake of convenient.

When and why we Clone a Database?

* Generally production (PROD) database is cloned for various reasons and needs i.e. for something to be tested or something to be developed later those to be moved to production.

* It’s normal and quite common thing is that whenever there is any change or update to be performed and do not know the impact or effect after applying it on production (PROD), it’s required to be applied and tested on *NON* production database first (TEST or DEV), after the confirmation of change success, given by the users, then the changes will be moved to production.

* A Cloned test instance (TEST) for testing team/environment is exclusively used for testing the changes or issues which will be come severe on Production. Oracle Support gives the solution as fix when there is an issue in the database, so this fix needs to perform or apply on test/development databases.

* A Cloned development instance (DEV) for development team/environment is used for developing the new changes and then deploying the same on Production.

* A Cloned patch instance is used for patching to know the impact and the time required to apply the same on Production.

How to clone an Oracle Database and different ways of cloning.

There are many possible methods available for cloning a database, but each of them has pros and cons, and significance. Following are the methods.

Using Cold (Offline) Backup:

This is an easy and simple method to perform a clone of a database. This method requires your production database (PROD) needs to be shutdown gracefully, and take the backup of the database related files i.e. Data files, Control files, Redo Log files, using Operating System commands i.e. cp or copy. This is not possible where your PROD database is running 24/7 and should be available continuously for users.

For syntax and the series of steps to perform the clone using cold backup, refer the following URLs from the reference.

References:
http://www.samoratech.com/TopicOfInterest/swCloneDB.htm
http://www.pgts.com.au/pgtsj/pgtsj0211b.html
http://www.jlcomp.demon.co.uk/faq/clone_db.html

Using Hot (Online) Backup:

In this method, backup of the database will be done online i.e. without shutting down the database.

For this, your Production Database is must be in Archive log mode. For syntax and the series of steps to perform the clone using hot backup, refer the following URLs from the reference.

Reference:
http://www.quest-pipelines.com/newsletter/cloning.htm
http://www.oralnx.com/index.php/2007/03/22/cloning-an-oracle-database/
http://www.shutdownabort.com/quickguides/clone_hot.php

Using RMAN Commands:

Cloning can also be performed using RMAN Backups and RMAN commands and it’s also an easy method to perform so. The RMAN DUPLICATE command is used to perform the clone. Until Oracle 9i, to clone the database, it is required to be the Source and Target systems should have the same OS i.e. it is not possible to clone across the platform. But as workaround, using export/import can be cloning the database across the platforms. But starting from Oracle 10g the RMAN capabilities have improved immensely. Cross platform cloning/duplicating a database can be done using RMAN CONVERT commands.

For syntax and the series of steps to perform the clone using RMAN Commands, refer the following URLs from the reference.

References:

Creating and Updating Duplicate Databases with RMAN

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb.htm#i1008564

Cross-Platform Transportable Database: RMAN CONVERT DATABASE

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/dbxptrn002.htm#CHDCFFDI

Creating a Duplicate Database on a Local or Remote Host

http://sabdarsyed.blogspot.com/2007/06/clone-of-database-on-same-host-ie.html

http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb005.htm

Pre & Post Cloning Steps/Changes:

* Do *NOT* set the clone database name as good as production database Name.

* It’s *NOT* mandatory to have the initialization parameter values of cloned instance similar to Production Instance.

* It is *NOT* mandatory to have the cloned instance in Archive log mode. Because unnecessarily archive log files are generated, which consume the hard disk space? If at all, the cloned instance crashed and need to be recovered, it can easily be again cloned from the production.

* After the clone, change the system users passwords i.e. SYS & SYSTEM, and for any critical users passwords.

* Disable the jobs which are not required to be run in the cloned instance.

* Change any application users tables from the cloned database which are still referring the Production Database i.e. Server IP, Port Details, Printer Details etc,

Other Useful Links:

OTN Forums on Cloning:
http://forums.oracle.com/forums/search.jspa?threadID=&q=clone+a+database&objID=f61&dateRange=all&userID=&numResults=30&rankBy=10001

Ask Tom Forums:

DB cloning -- what is it and why
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:575623107841

Creating test environment from production box
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:526422273445

Metalink Notes:

Note:245262.1 - Subject: Create a Duplicate Database with NOCATALOG on Same Node
Note:458450.1 - Subject: Steps to Manually Clone a Database
Note:388431.1 - Subject: Creating a Duplicate Database on a New Host.

Conclusion: These are only my views and outlines about cloning and need *NOT* to be the same with others or Oracle. One individual can still follow their cloning procedure documents which are there in place with them. I strongly suggest one to do the changes first in the test/development before doing it on Production. And also, use Oracle Documentations and Metalink Support for any kind of help.

Hope that this post helps you in understanding the Cloning and Refreshing activity of a database.

**Please leave your comments/suggestions about this post**.

Regards,
Sabdar Syed.
http://sabdarsyed.blogspot.com/

To take the full database export (Oracle 10g):

Create a Export Directory:
##########################

On Solaris:
-----------
SQL> create or replace directory sys_dmp as '/u02/expdp';
Directory created.

On Windows:
-----------
SQL> create or replace directory sys_dmp as 'D:\expdp';
Directory created.

Create a separate export user:
##############################

SQL> Connect /as sysdba

SQL> CREATE USER expdpadmin IDENTIFIED BY expdp default tablespace users;
User created.

Grant Export and Import Privileges.
###################################

SQL> GRANT CONNECT,RESOURCE TO expdpadmin;
Grant succeeded.

SQL> GRANT exp_full_database to expdpadmin;
Grant succeeded.

SQL> alter user expdpadmin quota unlimited on USERS;
User altered.

SQL> GRANT READ, WRITE ON DIRECTORY SYS_DMP to expdpadmin;
Grant succeeded.

To check on which directories you have privilege to read & write:
#################################################################

SQL> SELECT privilege, directory_name
2 FROM user_tab_privs t, all_directories d
3 WHERE t.table_name(+)=d.directory_name
4 ORDER BY 2,1;

Exporting Full Database:
########################

expdp expdpadmin/XXXXXX full=y directory=sys_dmp dumpfile=full_db_expdp.dmp logfile=full_db_expdp.log

Update: Take a look at my other post on "How to perform a full database export using original export/import utility?"

http://sabdarsyed.blogspot.com/2008/08/how-to-perform-full-database-export.html

Regards,
Sabdar Syed.