This article describes how to run a full database export on a source database and how to import the export dump file into a target database.

SCOPE

The article is intended for the Database Administrator of the Oracle7, Oracle8, Oracle8i, Oracle9i, Oracle10g, Oracle11g, and Oracle12c databases who wish to use DataPump or the EXPORT and IMPORT utilities to perform a full database export from an Oracle database release x and import this data with a full database import into an Oracle database release y.
The steps provided in this article do not apply to the export and import or Oracle Applications databases, nor to the export and import of the Portal Database schema’s. See the references at the end of this document for links to documents that describe the steps for those databases.

DETAILS

Introduction

For information about the concepts how the Oracle export and import utilities can be used to transfer data, schemas, tablespaces, databases across platforms and across 32-bit/64-bit processor servers, see:Note 277650.1 How to Use Export and Import when Transferring Data Across Platforms or Across 32-bit and 64-bit Servers

When migrating or upgrading a database, ensure that you have read the referenced notes at the bottom of this article.

Test the complete procedure at least once and eliminate any errors during export and import, or understand why the remaining warnings and errors are reported and what additional actions are needed from you to resolve them, before you run a full database export and import on your production/development database. For testing purposes, use a recent clone of the production source database.

All the queries mentioned below, are based on a connection in SQL*Plus:

T1. If there are any other Oracle databases already installed on the target machine, issue a clean shutdown of those databases (SHUTDOWN IMMEDIATE) and create a full database backup of those databases. This ensures that if anything goes wrong during the full database import (e.g. ORACLE_SID was set to the wrong database by mistake), you can restore that database from this backup.

T2. On the target machine, install the Oracle software. To eliminate any known issues during the full database import and for which a fix already exists, it is recommended to apply the latest patchset in this $ORACLE_HOME. For a summary of the latest Oracle Server releases, see:Note 161818.1 Oracle Server (RDBMS) Releases Support Status SummaryNote 756671.1 Oracle Recommended Patches — Oracle DatabaseNote 454507.1 ALERT: Oracle 11g Release 1 (11.1) Support Status and AlertsNote 880782.1 ALERT: Oracle 11g Release 2 (11.2) Support Status and AlertsNote 1565065.1 ALERT: Oracle 12c Release 1 (12.1) Support Status and Alerts

See the README file of the patchset how to install the patchset and how to run the required post-installation steps. See also:Note 176311.1 Good practices applying patches and patchsets

S7. Open the file TNSNAMES.ORA file in $ORACLE_HOME/network/admin and make a note (or copy) the entries that are used for a local access to the database and for the database links from the source database to the remote databases.

TARGET DATABASE

T3. On the target machine, determine what the characterset should be for the database. If the database characterset does not need to be changed, use the same characterset. See also Source Database step S3 above, and:Note 77441.1 Steps to Create a New Database With a Character Set Other Than US7ASCII

T4. For Oracle9i and higher, determine how the undo management should take place: Manual, or Automatic. See also:Note 135090.1 Managing Rollback/Undo Segments in AUM (Automatic Undo Management)

T5. For Oracle8i and higher, determine how the space management in the tablespaces should take place. For Oracle9i and higher, it is recommended to use locally managed tablespaces. See also:Note 105120.1 Advantages of Using Locally Managed vs Dictionary Managed Tablespaces

T6. On the target machine, create a new parameter file with the initialization parameters. For Oracle9i and higher, determine whether the initialization parameters should be stored in the old-style pfile (init.ora) or in the new style server parameter file (spfile). For details, see:Note 249664.1 Pfile vs SPfile

T7. On the target machine, create the new target database with the Database Configuration Assistant. When using the Database Configuration Assistant (dbca), you can specify which options need to be installed. Choose the same components that were installed in the source database, unless you are absolutely sure that although the component was installed in the source database, it was not being used (see also Source Database step S2 above). When downgrading be aware that certain features and components that were used in the source database may not be available or compatible with the lower release target database.
For the REDO LOG files, see Source Database step S4 above. It is also possible to create the new database with an existing script file or with a script file that you created yourself. In that case, ensure that this script file calls all the other scripts that are required to to add the options and components that are needed in this target database.

T8. When using the old-style (pre-Oracle9i) manual undo management (see Target Database step T4 above), create an extra rollback segment in the SYSTEM tablespace and put it online. For details, see:Note 112479.1 ORA-01552 Error Creating a Rollback Segment in a Locally-Managed Tablespace

Ensure that data dictionary components such as CATALOG and CATPROC have the same version as the version of the Oracle executable:

SELECT * FROM v$version;

T11. If the directory structure for the datafiles of the tablespaces is different from the source database, then pre-create all the tablespaces in the target database (except SYSTEM, SYSAUX, UNDO). For the original DDL, see Source Database step S5 above.

T12. Do a clean shutdown of the target database, and create a full backup of the database with RMAN or by Operating System (datafiles, controlfiles, redolog files). In case you need to re-run the database import later on, then you can restore from this backup.

CONNECT / AS SYSDBA
SHUTDOWN immediate

— create a full backup of the complete database

STARTUP

T13. Review the TNSNAMES.ORA file in $ORACLE_HOME/network/admin and ensure the entries are added which are used for local access to the database and for the database links from the new target database to the remote databases (see Source Database step S7 above).

SOURCE DATABASE

S8. Do a clean shutdown of the source database, and create a full backup of the database with RMAN or by Operating System (datafiles, controlfiles, redolog files). In case you need to re-run the full database export later on, then you can restore from this backup.

CONNECT / AS SYSDBA
SHUTDOWN immediate

— create a full backup of the complete database

STARTUP

S9. Stop the listener for the source database and ensure no users or applications can connect to the database.

S10. Create a spooled output file containing a list with the amount of schema objects in the source database, e.g.:

T14. Run a full database import with the original import client (if Source Database step S12 was done with EXP), or the Import Data Pump utility (if Source Database step S12 was done with EXPDP). Use the import client that matches the version of the target database, e.g.:

Manually compile the objects that are invalid in the target database and were valid in the source database (see Source Database step S10 above).
If objects remain invalid, determine the root cause and resolve it if needed.

T18. When coming from a lower release than the importing database (e.g. migration from 9.2.0.8 to 11.2.0.3), using either datapump or traditional export/import, then post-import upgrade steps should be performed as outlined in section ‘Upgrading the Database Using Data Pump Export/Import’ of the Upgrade Guide (http://docs.oracle.com/cd/E11882_01/server.112/e23633/expimp.htm#UPGRD12565).

T19. Check whether users and applications can connect to the database.

SOURCE DATABASE

S13. Shutdown the old source database.

CONNECT / AS SYSDBA
SHUTDOWN IMMEDIATE

TARGET DATABASE

T20. Do a clean shutdown of the target database, and create a full backup of the database with RMAN or by Operating System (datafiles, controlfiles, redolog files).