Setup Menus in Admin Panel

DBATalent

Upgrading a database using exp/imp and datapump utilities

Knowledge Base > Oracle Database > Upgrading a database using exp/imp and datapump utilities

in Oracle Database

The following document describes how you can use export/import or datapump utilities to upgrade a database.

Contents:

(i) Using Datapump utility:

Introduction:

Oracle Data Pump is made up of three distinct parts:

  • The command-line clients, expdp and impdp
  • The DBMS_DATAPUMP PL/SQL package (also known as the Data Pump API)
  • The DBMS_METADATA PL/SQL package (also known as the Metadata API)

The Data Pump clients, expdp and impdp, invoke the Data Pump Export utility and Data Pump Import utility, respectively. They provide a user interface that closely resembles the original export (exp) and import (imp) utilities. They use the procedures provided in the DBMS_DATAPUMP PL/SQL package to execute export and import commands, using the parameters entered at the command-line. These parameters enable the exporting and importing of data and metadata for a complete database or subsets of a database.

All Data Pump Export and Import processing, including the reading and writing of dump files, is done on the system (server) selected by the specified database connect string. This means that, for non-privileged users, the database administrator (DBA) must create directory objects for the Data Pump files that are read and written on that server file system. For privileged users, a default directory object is available. When data is moved, Data pump automatically uses either direct path load (or unload) or the external tables’ mechanism, or a combination of both. When metadata is moved, Data Pump uses functionality provided by the DBMS_METADATA PL/SQL package.

The new Data Pump Export and Import utilities have a similar look and feel to the original Export (exp) and Import (imp) utilities, but they are completely separate. Dump files generated by the new Data Pump Export utility are not compatible with dump files generated by the original Export utility. Therefore, files generated by the original Export (exp) utility cannot be imported with the Data Pump Import (impdp) utility.

Data Pump offers the following benefits:

  • Supports filtering the metadata that is exported and imported based upon objects and object types, using INCLUDE and EXCLUDE parameters.
  • Supports different modes for unloading/loading portions of the database including: full database mode, schema mode, table mode, tablespace mode, and transportable tablespace mode.
  • Provides support for the full range of data types.

Upgrading a database using datapump:

Assuming our database is running Oracle 11g (11.2.0.1) and we want to upgrade to Oracle 12c (12.1.0.2) using data pump utility. Before starting the upgrade process, make sure both 11.2.0.1 and 12.1.0.2 RDBMS softwares are installed on your system and the database is running on 11.2.0.1 RDBMS software. The basic procedure of upgradation is to export data from the source database before you install the new Oracle Database software, and then import the data into the target upgraded database. If the new database has the same name as the current database, then shut down the current database before creating the new database. Use the Import utility of the new database to import the objects exported from the current database. Include a parameter ‘logfile’ to save the informational  messages and error messages from the import session to a file.

  • Startup the database
  • Create a directory for storing the dump files of the database as shown below

$ cd /u01/dev5

$ mkdir dpump

dp_upgr_dbatalent_a

  • Now register the directory in SQL*PLUS using below command

SQL> create directory datapump as ‘/u01/dev5/dpump’;

Also grant read, write permissions on that directory to public or a specific user using following command,

SQL> GRANT READ,WRITE ON DIRECTORY datapump TO PUBLIC;

dp_upgr_dbatalent_b

You can view the directory name and location in the following table,

SQL> SELECT * FROM DBA_DIRECTORIES;

If the directory you created is listed there, proceed with the next step.

  • Exit SQL*PLUS client. Invoke the following command to take dump of the database. If you are taking exports or imports as a non-privileged user, you must have EXP_FULL_DATABASE and IMP_FULL_DATABASE privileges to perform the task.

$ expdp dumpfile=full.dmp full=y logfile=full.log userid=rcat/rcat directory=datapump

dp_upgr_dbatalent_c

dp_upgr_dbatalent_d

  • Now create a new database with 12.1.0.2 RDBMS software by creating a parameter file and database creation script. After creation, run catalog.sql, catproc.sql and pupbld.sql. and open the database. Also create a new user and assign following privileges.

    SQL> CREATE USER expadmin IDENTIFIED BY password;

    SQL> GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE, EXP_FULL_DATABASE TO expadmin;

dp_upgr_dbatalent_e

  • Here we can use the same directory in which we saved the dumps of the 11g database. Now register the directory and grant permissions using following commands.

    SQL> CREATE DIRECTORY datapump AS ‘/u01/backups/dev2/’;

    SQL> GRANT READ, WRITE ON DIRECTORY datapump TO PUBLIC;

dp_upgr_dbatalent_f

  • Now just import the data from the dump file using below mentioned command to finish the upgradation.

    $ impdp file=full.dmp directory=datapump userid=system/password

dp_upgr_dbatalent_g

dp_upgr_dbatalent_h

After the import, check the import log file for information about the imports of specific objects that completed successfully. If there were failures, check for information about any objects that failed. You can use the Data Pump Import utility with a database link to perform a full database import from a source database to a destination database without intermediate dump files. To use a network link with the Data Pump Import utility, impdp, Ensure that the exporting user at the source database has the DATAPUMP_EXP_FULL_DATABASE role. This user must be specified when you create the database link. Also Ensure that the importing user at the destination database has the DATAPUMP_IMP_FULL_DATABASE role. Then create and test a database link between the source and destination databases.

Run the following command, where import_user is the username for the importing user, and db_link is the name of the database link owned by the exporting user:

$ impdp import_user NETWORK_LINK=db_link FULL=Y;

A log file for the import operation writes to the DATA_PUMP_DIR directory. You can discover the location of this directory by running the following command.

SQL> select * from dba_directories where DIRECTORY_NAME like ‘DATA_PUMP_DIR’;

(ii) Using exports and imports:

Introduction:

The Export and Import utilities provide a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations. When you run Export against an Oracle database, objects (such as tables) are extracted, followed by their related objects (such as indexes, comments, and grants), if any. The extracted data is written to an export dump file. The Import utility reads the object definitions and table data from the dump file. An export file is an Oracle binary-format dump file that is typically located on disk or tape. The dump files can be transferred using FTP or physically transported (in the case of tape) to a different site. The files can then be used with the Import utility to transfer data between databases that are on systems not connected through a network. The files can also be used as backups in addition to normal backup procedures. Export dump files can only be read by the Oracle Import utility. The version of the Import utility cannot be earlier than the version of the Export utility used to create the dump file.

To use Export and Import, you must run the script ‘catexp.sql’ or ‘catalog.sql’ (which runs catexp.sql) after the database has been created. This creates necessary export and import views in the data dictionary, creates ‘IMP_FULL_DATABASE’ and ‘EXP_FULL_DATABASE’ roles and assigns necessary privileges to those roles, then assigns these privileges to ‘DBA’ role and records version of ‘catexp.sql’ that’s installed.

Before you run Export, ensure that there is sufficient disk or tape storage space to write the export file. If there is not enough space, Export terminates with a write-failure error. You can use table sizes to estimate the maximum space needed. You can find table sizes in the USER_SEGMENTS view of the Oracle data dictionary. The following query displays disk usage for all tables.

 SQL> SELECT SUM(BYTES) FROM USER_SEGMENTS WHERE SEGMENT_TYPE=’TABLE’;

exp_upgr_dbatalent_1

Upgrading a database using exp/imp utility:

  • Startup the database
  • Exit SQL*PLUS client. Invoke the following command to take dump of the database. If you are taking exports or imports as a non-privileged user, you must have EXP_FULL_DATABASE and IMP_FULL_DATABASE privileges to perform the task

$ exp file=full.dmp log=log.dmp full=y

exp_upgr_dbatalent_2

exp_upgr_dbatalent_3

This will create the dump files in the location from where you are invoking the command.

  • Now create a new database with 12.1.0.2 RDBMS software by creating a parameter file and database creation script. After creation, run catalog.sql, catproc.sql and pupbld.sql. and open the database. Then create a new user assign privileges as shown below.

SQL> CREATE USER impadmin IDENTIFIED BY password;

SQL> GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE, IMP_FULL_DATABASE TO impadmin;

Or you can import the database using administrative account “SYSTEM”.

  • Now just import the data from the dump file using below mentioned command to finish the upgradation.

$ imp file=/u01/dev5/full.dmp log=/u01/dev5/imp.dmp full=yes

exp_upgr_dbatalent_4

exp_upgr_dbatalent_5

This completes importing the data to a database running on higher version.

0