how to perform full database export import during upgrade , migrate , copy or move of a database





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]
Enterprise Manager for Oracle Database – Version 10.1.0.2 to 12.1.0.5.0 [Release 10.1 to 12.1]
Oracle Database – Standard Edition – Version 10.1.0.2 to 12.1.0.1 [Release 10.1 to 12.1]
Oracle Database – Enterprise Edition – Version 10.1.0.2 to 12.1.0.1 [Release 10.1 to 12.1]
Information in this document applies to any platform.

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.
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:

$ sqlplus /nolog

CONNECT system/manager
SET lines 120 NUMWIDTH 12 PAGES 10000 LONG 2000000000
ALTER SESSION SET nls_date_format=’YYYY-MM-DD HH24:MI:SS’;


TARGET DATABASE

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 Summary
Note 756671.1 Oracle Recommended Patches — Oracle Database
Note 454507.1 ALERT: Oracle 11g Release 1 (11.1) Support Status and Alerts
Note 880782.1 ALERT: Oracle 11g Release 2 (11.2) Support Status and Alerts
Note 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

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:
Note 161818.1 Oracle Server (RDBMS) Releases Support Status Summary
Note 756671.1 Oracle Recommended Patches — Oracle Database
Note 189908.1 ALERT: Oracle9i Release 2 (9.2) Support Status and Alerts
Note 263719.1 ALERT: Oracle 10g Release 1 (10.1) Support Status and Alerts
Note 316900.1 ALERT: Oracle 10g Release 2 (10.2) Support Status and Alerts
Note 454507.1 ALERT: Oracle 11g Release 1 (11.1) Support Status and Alerts
Note 880782.1 ALERT: Oracle 11g Release 2 (11.2) Support Status and Alerts
Note 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

S2. In the source database, check which components were installed:

– Oracle Text – schema: CTXSYS
– Oracle OLAP – schema: OLAPSYS
– Oracle Spatial – schema: MDSYS
– Oracle Data Mining – schema: DMSYS
– Oracle Ultra Search – schema: WKSYS
– Oracle Label Security – schema: LBACSYS
– Oracle JVM ? – object_types: ‘JAVA CLASS’, ‘JAVA DATA’, ‘JAVA RESOURCE’
– Oracle interMedia – schema: ORDSYS
– Oracle XML Database – schema: XDB
– Oracle Rules Manager / Expression Filter – schema: EXFSYS
– Oracle Warehouse Builder – schema: OWBSYS
– Oracle Workspace Manager – schema: WMSYS
– Oracle Application Express – schema: APEX_0xxxxx

For details about the different database components and schema’s, see also:
Note 472937.1 Information On Installed Database Components and Schemas

Possible ways to find out which components are in use:

  1. Oracle9i 9.2.0. and above: use the query:

COL version FORMAT a12
COL comp_id FORMAT a8
COL schema LIKE version
COL comp_name FORMAT a35
COL status FORMAT a12
SELECT comp_id,schema,status,version,comp_name
FROM dba_registry
ORDER BY 1;

  1. Use the Database Configuration Assistant (modify database)
  2. Run queries:

SELECT username FROM dba_users
WHERE username IN (‘CTXSYS’, ‘OLAPSYS’, ‘MDSYS’, ‘DMSYS’, ‘WKSYS’, ‘LBACSYS’,
‘ORDSYS’, ‘XDB’, ‘EXFSYS’, ‘OWBSYS’, ‘WMSYS’)
OR username LIKE ‘APEX%’
ORDER BY 1;

SELECT object_type, COUNT(*) FROM dba_objects
WHERE  object_type LIKE ‘JAVA%’
GROUP  BY object_type;

 

S3. In the source database, check the characterset of the database:

COL value FORMAT a15
SELECT * FROM nls_database_parameters
WHERE  parameter LIKE ‘%SET’
ORDER  BY 1;

S4. In the source database, create a spooled output file and check details about the the redo logfiles:

SPOOL redo.out
SELECT group#,bytes,blocksize,members,status
FROM v$log
ORDER BY 1;

SELECT * FROM v$logfile
ORDER BY 1,3;

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 file_name FORMAT a80
SELECT tablespace_name, bytes, status, online_status, file_name
FROM dba_data_files
ORDER BY 1,5;

COL ddl FORMAT a100
SELECT dbms_metadata.get_ddl(‘TABLESPACE’,’USERS’) “DDL” FROM dual;

— 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:
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

T9. Check whether all objects in the target database are valid:

COL status FORMAT a9
COL object_type FORMAT a20;
COL owner.object FORMAT a50
SELECT status, object_id, object_type, owner||’.’||object_name “OWNER.OBJECT”
FROM dba_objects
WHERE status != ‘VALID’ AND object_name NOT LIKE ‘BIN$%’
ORDER BY 4,2;

If there are invalid objects, recompile them. For details, see also:
Note 213600.1 How to Compile Invalid Objects in SYS Schema after RDBMS 8.0

T10. For Oracle9i Release 2 (9.2.0) and higher release databases, check the status of the data dictionary:

COL version FORMAT a12
COL comp_id FORMAT a8
COL schema LIKE version
COL comp_name FORMAT a35
COL status FORMAT a12
SELECT comp_id,schema,status,version,comp_name
FROM dba_registry
ORDER BY 1;

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.:

SPOOL obj_source.out
COL status FORMAT a9
COL object_type FORMAT a20;
COL owner.object FORMAT a50
SELECT status, object_id, object_type, owner||’.’||object_name “OWNER.OBJECT”
FROM dba_objects
WHERE status != ‘VALID’ AND object_name NOT LIKE ‘BIN$%’
ORDER BY 4,2;

COL owner FORMAT a30
select owner, object_type, status, count(*)
from dba_objects
where object_name not like ‘BIN$%’
group by owner, object_type, status order by 1,2;

SPOOL off

S11. Create a spooled output file containing a list of triggers owned by SYS, e.g.:

SPOOL trig.out
COL triggering_event FORMAT a35
SELECT owner, trigger_name, trigger_type, triggering_event, table_owner, base_object_type, status
FROM dba_triggers
WHERE owner=’SYS’;

COL ddl FORMAT a100
SELECT dbms_metadata.get_ddl(‘TRIGGER’,’LOGMNRGGC_TRIGGER’,’SYS’) “DDL” FROM dual;

— do this for all manually created triggers in the SYS schema

SPOOL off

S12. Compatibility:

  • When upgrading:
    • Source database is Oracle9i or lower release:
      • Run a full database export with the original export client.
      • Use the export client that matches the version of the source database.
    • Source database is Oracle10g or higher release:
      • Run a full database export with the Export Data Pump utility.
      • Use any export Data Pump client (the client version that matches the version of the source database is recommended).
  • When downgrading:
    • Target database is Oracle9i or lower release:
      • Run a full database export with the original export client.
      • Use the export client that matches the version of the target database.
    • Target database is Oracle10g or higher release:
      • Run a full database export with the Export Data Pump utility.
      • Use any export Data Pump client (the client version that matches the version of the source database is recommended) and specify the export Data Pump parameter VERSION=<value>

References:
Note 132904.1 – Compatibility Matrix for Export And Import Between Different Oracle Versions [Video]
Note 345187.1 – Feature Obsolescence – Original Export 10.2
Note 553337.1 – Export/Import DataPump Parameter VERSION – Compatibility of Data Pump Between Different Oracle Versions [Video]

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
CREATE OR REPLACE DIRECTORY my_dir AS ‘/tmp’;
GRANT read, write ON DIRECTORY my_dir TO system;

$ 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
CREATE OR REPLACE DIRECTORY my_dir AS ‘/tmp’;
GRANT read, write ON DIRECTORY my_dir TO system;

$ 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
COL owner FORMAT a30
select owner, object_type, status, count(*)
from dba_objects
where object_name not like ‘BIN$%’
group by owner, object_type, status order by 1,2;

SPOOL off

Compare this list with the output of Source Database step S10 above.
Determine the root cause of any inconsistencies and resolve them if needed.

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
COL object_type FORMAT a20;
COL owner.object FORMAT a50
SELECT status, object_id, object_type, owner||’.’||object_name “OWNER.OBJECT”
FROM dba_objects
WHERE status != ‘VALID’ AND object_name NOT LIKE ‘BIN$%’
ORDER BY 4,2;

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).

CONNECT / AS SYSDBA
SHUTDOWN immediate

— 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]
NOTE:756671.1 – Oracle Recommended Patches — Oracle Database
NOTE:135090.1 – Managing Rollback/Undo Segments in AUM (Automatic Undo Management)
NOTE:132904.1 – Compatibility Matrix for Export And Import Between Different Oracle Versions [Video]
NOTE:345187.1 – Feature Obsolescence – Original Export 10.2
NOTE:77441.1 – Steps to Create a New Database With a Character Set Other Than US7ASCII
NOTE:105120.1 – Advantages of Using Locally Managed vs Dictionary Managed Tablespaces
NOTE:249664.1 – Pfile vs SPfile
NOTE:472937.1 – Information On Installed Database Components and Schemas
NOTE:277650.1 – How To Use Export And Import When Transferring Data Across Platforms Or Across 32-bit And 64-bit Servers
NOTE:133920.1 – Complete Upgrade Checklist for Manual Upgrades from 8.x to 8.x
NOTE:112479.1 – ORA-01552 Error Creating a Rollback Segment in a Locally-Managed Tablespace
NOTE:159657.1 – Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9iR2 (9.2.0)
NOTE:204015.1 – Export/Import Process for Oracle Applications Release 11i Database Instances
NOTE:230627.1 – 9i Export/Import Process for Oracle Applications Release 11i
NOTE:161818.1 – Oracle Database (RDBMS) Releases Support Status Summary
NOTE:176311.1 – Good practices applying patches and patchsets
NOTE:228516.1 – How to copy (export/import) Portal database schemas of IAS 9.0.2 to another database
NOTE:213600.1 – How to Compile Invalid Objects in SYS Schema after RDBMS 8.0

 

Author: admin