What Everybody Should Know about Export Database and Import Database In Oracle ?

Export Database and Import Database are common tasks for a DBA. So Every visitor of this blog should know about this. Though there are so many ways to do it, but EXPDP and IMPDP are most easy and preferred ways to achieve this. Export Database in Oracle 10 and Export Database in Oracle 11g are same in functionality and syntax. Usually Database Administrator has to make a copy of database from Production to UAT and Test environment etc. In this case EXPDP and IMPDP is the best tool to achieve this.

In full Export database whole database is exported to a dump file at OS location having all commands for creating tablespace, user, roles, Grants and every other object. DBA has to move to this dump file at destination server and execute an Impdp command with full option. By doing this a copy of whole database from source database is ready to use on destination server.

In this post I will use EXPDP full feature to Export the database and IMPDP full parameter to Import Database.

3 Steps for Exporting Full Database

1. Check Source Database is in Open mode.
2. User Exporting database must have DATAPUMP_EXP_FULL_DATABASE Role.
3. Export Database user EXPXP.

I am taking an example of database "orcl", I will Export this database to another empty data (A database will basic tablespaces and user and objects).

1. Check Source database is Open: Set ORACLE_SID env variable, Connect to source database using sys user and execute below command and result should be like give below.

[oracle@database dpdump]$ export ORACLE_SID=orcl
[oracle@database dpdump]$ sqlplus sys as sysdba
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE

2. Assigning DATAPUMP_EXP_FULL_DATABASE Role to User: I would recommend to use "system" user for Exporting Database which has DATAPUMP_EXP_FULL_DATABASE Role by default given to it. If you wish to use any other user, use below steps to give DATAPUMP_EXP_FULL_DATABASE Role to a user and verify.

SQL> grant DATAPUMP_EXP_FULL_DATABASE to scott;
Grant succeeded.

SQL> select grantee,GRANTED_ROLE,DEFAULT_ROLE from dba_role_privs where grantee='SCOTT';
GRANTEE                        GRANTED_ROLE                   DEF
------------------------------ ------------------------------ ---
SCOTT                          RESOURCE                       YES
SCOTT                          DATAPUMP_EXP_FULL_DATABASE     YES
SCOTT                          CONNECT                        YES


3. Export Database using Expdp: Export database in Oracle 10g and Export Database in Oracle 11g are same, However I am using Oracle 11g as test environment for this post.

Syntex for Export command:

expdp username/password directory={database directory} dumpfile={Dump file name} full=yes logfile={logfile name}

"Expdp" is command has to execute on command prompt, "username/password" are database usename and passwods usually "system" is the username. "Directory" is database directory created into to database for dump creation default is "data_pump_dir". Database Administrator can list existing directories using.

SQL> select * from dba_directories;

"Full=yes" is parameter defined for database full export, default value for this parameter is "no". "Logfile" parameter has value for log file name generated during database export. The location of log file is same as directory given into this expdp command.

Example:
[oracle@ dba]$ expdp system/sys directory=data_pump_dir dumpfile=full_database.dmp full=yes logfile=full_database.log

Export: Release 11.2.0.2.0 - Production on Wed Jun 26 22:06:10 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** directory=data_pump_dir dumpfile=full_database.dmp full=yes logfile=full_database.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 398.8 MB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
-----------------------------------------------------
-----------------------------------------------------
-----------------------------------------------------
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  /etc/oracle/admin/orcl/dpdump/full_database.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" completed with 1 error(s) at 22:11:44

At the end of log file or above command you can find name and actual path of dump file generated like below Cross check log file for any error and work accordingly. Here, Export of database is done. Next we will see How to Import full database into new database.


5 Steps for Importing Database

As DBA has completed Export of database, Now He has to restore database on to another server. Below are the steps for Full database import on another server.

1. Copy database dump file on destination server using OS utility.
2. Create same directory structure for Datafile at destination server.
3. Check Destination database is in open mode.
4. User importing database must have IMP_FULL_DATABASE role.
5. Import Database using Impdp:


1. Copy Database Dump file on Destination Server using OS Utility: Fist task for DBA is to copy dump file generated at source database "/etc/oracle/admin/orcl/dpdump/full_database.dmp" to destination server. The destination server physical directory should mapped to a database directory or you can choose default database directory data_pump_dir. You can find out physical location of a database directory using.

SQL> select * from dba_directories;
OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ ------------------------------------------------------------
SYS                            SUBDIR                         /etc/oracle/oracle/demo/schema/
SYS                            DATA_PUMP_DIR                  /etc/oracle/admin/orcl/dpdump/

Here Physical path for DATA_PUMP_DIR is "/etc/oracle/admin/orcl/dpdump/".

Now, DBA can use unix scp command for moving dump file from source to destination database.

Syntax: scp old-filename username@host:/path/to/other/destination/directory/new-filename

Example:

[oracle@ dba]$ scp /etc/oracle/admin/orcl/dpdump/full_database.dmp oracle@192.168.4.50:/etc/oracle/admin/orcl/dpdump


2. Create Same Directory Structure for Datafile at Destination Server: AS we Know, Importing database will full = yes will recreate whole database at destination server. This will also create the tablespase and datafiles on destination server, but to accomplish that basic OS directory structure of database must exists at destination server.

Let's take an example: Suppose DBA has a tablespace TBS1 and it's datafiles at "/etc/oracle/oradata/orcl/example01.dbf" on source database. Now Database administrator has to make sure "/etc/oracle/oradata/orcl" path also exists on the destination server. So that, Impdp can make example01.dbf at this location. Absolute path creation "/etc/oracle/oradata/orcl" is not done by Impdp automatically. So DBA has to do this manually but only for Datafile.


3. Check Destination database is in open mode: Use same process as given for database export #3.


4. Assigning IMP_FULL_DATABASE Role to User: I would recommend to use "system" user for importing Database which has IMP_FULL_DATABASE Role by default given to it. If you wish to use any other user, use below steps to give IMP_FULL_DATABASE Role and verify.
SQL> grant IMP_FULL_DATABASE to scott;
Grant succeeded.

SQL> select grantee,GRANTED_ROLE,DEFAULT_ROLE from dba_role_privs where grantee='SCOTT';
GRANTEE                        GRANTED_ROLE                   DEF
------------------------------ ------------------------------ ---
SCOTT                          RESOURCE                       YES
SCOTT                          DATAPUMP_EXP_FULL_DATABASE     YES
SCOTT                          IMP_FULL_DATABASE              YES
SCOTT                          CONNECT                        YES


5. Import Database using Impdp: This is final step for importing database. Below is the syntax and Example of import database in Oracle 11g.

Syntax for Export command:

impdp username/password directory={database directory} dumpfile={Dump file name} full=yes logfile={logfile name}

"impdp" is the command has to execute on command prompt, "username/password" are database usename and passwords usually "system". "Directory" is database directory created into database for dump creation default is "data_pump_dir". Database Administrator can list existing directories using.

SQL> select * from dba_directories;

"Full=yes" is parameter defined for database full import, default value for this parameter is no."Logfile" parameter has value for log file name generated during database import. The location of log file is same as directory given into this impdp command.

Example for database Import.

[oracle@ dba]$ impdp system/sys directory=data_pump_dir dumpfile=full_database.dmp logfile=full_database_import.log full=y
Import: Release 11.2.0.2.0 - Production on Wed Jun 26 22:29:21 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=data_pump_dir dumpfile=full_database.dmp logfile=full_database_import.log full=y
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"SYSAUX" already exists
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-31684: Object type TABLESPACE:"USERS" already exists
--------------------------------------------------------
--------------------------------------------------------
--------------------------------------------------------
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 50 error(s) at 22:34:13


I expect few errors during this import command as shown above like "ORA-31684: Object type TABLESPACE:"SYSAUX" already exists". This is because at destination server SYSAUX tablespace already exists. You can also see same kind of error for Database users, roles and other objects. I wold suggest DBA's to cross check database import log file thoroughly to make sure everything is imported properly and act accordingly.

DBA can also take only a schema backup using How to Take schema backup using Expdp ? and He can also Restore Schema backup using How to Restore schema using IMPDP ? 

Are you doing an Database export and import ? Please share if this post helps or we need further improvement on this.

No comments:

Post a Comment