Export: Release 11.2.0.4.0 – Production on Sat Dec 12 18:22:08 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Automatic Storage Management and OLAP options
Starting “SYSTEM”.”SYS_EXPORT_FULL_02″: system/******** directory=data_pump_dir dumpfile=ods_dec12%U.dmp parallel=4 full=y compression=all
Estimate in progress using BLOCKS method…
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
How to Perform A Full Database Export Import During Upgrade, Migrate, Copy Or Move Of A Database (Doc ID 286775.1) | To Bottom |
Modified:Feb 11, 2014Type:BULLETIN |
|
APPLIES TO:
Oracle Database – Personal Edition – Version 10.1.0.2 to 12.1.0.1 [Release 10.1 to 12.1] PURPOSE 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. 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: 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: $ sqlplus /nolog CONNECT system/manager
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: See the README file of the patchset how to install the patchset and how to run the required post-installation steps. See also: SOURCE DATABASE S1. To eliminate any known issue during the full database export and for which a fix already exists, it is recommended to apply the latest patchset in the $ORACLE_HOME of the source database. For a summary of the latest Oracle Server releases, see: See the README file of the patchset how to install the patchset and how to run the required post-installation steps. See also: S2. In the source database, check which components were installed: – Oracle Text – schema: CTXSYS For details about the different database components and schema’s, see also: Possible ways to find out which components are in use:
COL version FORMAT a12
SELECT username FROM dba_users SELECT object_type, COUNT(*) FROM dba_objects
S3. In the source database, check the characterset of the database: COL value FORMAT a15 S4. In the source database, create a spooled output file and check details about the the redo logfiles: SPOOL redo.out SELECT * FROM v$logfile SPOOL off S5. In the source database, create a spooled output file containing a list of all tablespaces, datafiles, and the DDL of the tablespaces. E.g.: SPOOL tbsp.out COL ddl FORMAT a100 — do this for all tablespaces SPOOL off S6. In the source database, check which users can connect AS SYSBDA: SELECT * FROM v$pwfile_users; 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: T4. For Oracle9i and higher, determine how the undo management should take place: Manual, or Automatic. See also: 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: 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: 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. 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: T9. Check whether all objects in the target database are valid: COL status FORMAT a9 If there are invalid objects, recompile them. For details, see also: T10. For Oracle9i Release 2 (9.2.0) and higher release databases, check the status of the data dictionary: COL version FORMAT a12 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 — 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 — 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.: SPOOL obj_source.out COL owner FORMAT a30 SPOOL off S11. Create a spooled output file containing a list of triggers owned by SYS, e.g.: SPOOL trig.out COL ddl FORMAT a100 — do this for all manually created triggers in the SYS schema SPOOL off S12. Compatibility:
References: E.g.: $ exp system/manager file=exp_f1.dmp,exp_f2.dmp,exp_f3.dmp,exp_f4.dmp,exp_f5.dmp log=exp_f.log full=y filesize=10g consistent=y compress=n — OR — CONNECT / AS SYSDBA $ expdp system/manager directory=my_dir dumpfile=expdp_f%U.dmp logfile=expdp_f.log full=y filesize=10g parallel=8 flashback_time=\”to_timestamp(to_char(sysdate, ‘YYYY-MM-DD HH24:MI:SS’),’YYYY-MM-DD HH24:MI:SS’)\” TARGET DATABASE 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.: $ imp system/manager file=exp_f1.dmp,exp_f2.dmp,exp_f3.dmp,exp_f4.dmp,exp_f5.dmp log=imp_f.log full=y filesize=10g — OR — CONNECT / AS SYSDBA $ impdp system/manager directory=my_dir dumpfile=expdp_f%U.dmp logfile=impdp_f.log full=y filesize=10g parallel=8 T15. Create the triggers that were manually created in the source database. For details, see Source Database step S11. T16. Create a spooled output file containing a list with the amount of schema objects in the target database, e.g.: SPOOL obj_target.out SPOOL off Compare this list with the output of Source Database step S10 above. T17. Recompile the invalid objects, and check which objects remain invalid in the target database: — recompile all invalid objects: @?\rdbms\admin\utlrp.sql COL status FORMAT a9 Manually compile the objects that are invalid in the target database and were valid in the source database (see Source Database step S10 above). 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 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). CONNECT / AS SYSDBA — create a full backup of the complete database STARTUP T21. Make the new target database available.
REFERENCES NOTE:553337.1 – Export/Import DataPump Parameter VERSION – Compatibility of Data Pump Between Different Oracle Versions [Video] |