Using Oracle Export/Import to Load Existing User Data

 

 

 

You can populate the empty Oracle database by using the Export and Import options in EDM. The topics covered are:

Importing Database

The Importing Database allows you to import data from an existing database dump.

  • The passwords for the SYS and SYSTEM users in the pre-Oracle10g database might be different from the ones used in the Oracle10g database during the database import. In such cases, the passwords of the Oracle10g database get changed to the passwords used in the pre-Oracle10g database. To change the password manually, login to SQL*PLUS as CONNECT/AS SYSDBA and enter the following commands:

    • SQL> ALTER USER SYS IDENTIFIED BY <SYS password>;

    • SQL> ALTER USER SYSTEM IDENTIFIED BY <SYSTEM password>;
      (These passwords should be the ones specified in the Oracle10g database.)

This task explains how to import database:

  1. In the navigation pad, select Administration > Import an Explore Database.

    You are prompted to enter the passwords.

  2. Enter the passwords, and then click OK.

    The Import an Explore Database window appears
    .

     

  3. Database export of CES is available in the form of a dump (SDMGOLD.dmp) file under the "<OS>_a/resources/sdm/sdmdb/oracle" directory, where OS refers to distribution for given Operating System (e.g.: For windows, it is intel and aix for IBM AIX).  This dump file needs to be imported into the newly created database.

     

    This table explains fields displayed in the Import an Explore Database window:

    ORACLE_SID

    The ORACLE_SID of the database being imported

    NLS_LANG

    This parameter sets the language, territory and character set used by the database for both the server session and the client application. The default value displayed here is based on the entry specified in the Database Setup window, and can be changed.

    Import File

    This is the file (database dump) from which the data is imported.

    Log File

    Name of the file on which the import status is to be written.

    Import Mode

    Entire Database

    CISDBA Schema

    Table

    Buffer Size

    This parameter determines the size of the buffer used to transfer the data from the export dump to the database. The import will be faster if bigger buffer size is given. The memory for the buffer is taken from the operating system. This
    parameter is an editable field and defaulted to 1 MB. The UOM for this parameter is KB and MB.

    Commit

    This indicates the import utility how often the imported data should be reported. If commit=n, the data is committed after each table. If commit=y, the data is committed after each buffer.

    Ignore

    This parameter decides whether the object creation errors should be reported. If set to ignore=y, the object creation errors will not be shown in the import log file/EDM status window.

    Statistics

    This determines if optimizer statistics (if existing in the export dump) should be loaded into the database or not. The available options are NONE, ALWAYS, RECALCULATE and SAFE.

  4. Click Import to start the import process.

    The bottom panel indicates the progress and when the database import is successfully completed.

  5. Click Cancel to return to the EDM main menu.

    You can check the Log File to see the import status.

    If your import mode is CISDBA Schema or Table, you are prompted whether you want to split the import. The following two choices are provided.

  • Import Data and Indexes together.

  • Import Data then create indexes (recommended for large imports).
     

Select any one of the preceding and click OK.

  • If you select Import Data and Indexes together, EDM imports everything together.

  • If you select Import Data then create indexes, EDM imports in three steps process:

    Generate the index-creation statements in the script ${EDS_HOME}/log/imp_cr_indexes_<ORACLE_SID>_<TIMESTAMP>.sql for UNIX, and %EDS_HOME%\log\imp_cr_indexes_%ORACLE_SID%_<TIMESTAMP>.sql for Windows.

    If any error occurs while generating the script, the import process is aborted with the error message.

  1. Import the data. Run the script generated by step 1 to create the indexes.

Exporting Database

The Exporting Database option allows you to export a database, or table or tables.

This task explains how to export database:

  1. In the navigation pad, select Administration > Export Database.

    You are prompted to enter the passwords.

  2. Enter the passwords, and then click OK.

    The Export Database window appears
    .

    This table explains fields displayed in the Export Database window:

    Field

    Description

    ORACLE_SID

    The ORACLE_SID of the database being exported.

    NLS_LANG

    This parameter sets the language, territory and character set used by the database for both the server session and the client application. The default value displayed here is based on the entry specified in the Database Setup window, and can be changed.

    Export File

    Name of the export dump file.

    Buffer Size

    This parameter specifies the size of the buffer used to fetch the rows from the table. It is an editable field and is defaulted to 1 MB. The UOM for this parameter is KB and MB.
     

    Compress

    Used to consolidate all table data into one extent upon import.

    Export

    Entire database

    CISDBA schema

    Table

    Statistics

    This determines the type of optimizer statistics to generate. The available options are COMPUTE, ESTIMATE and NONE.

  3. After specifying your options, click Export to start the database export process.

    The bottom panel indicates the progress, and when the export is successfully completed.

  4. Click Cancel to return to the EDM main menu.

    You can check the Log File to see the export status.