How-To - ArcFM - Export an Oracle Database for Transfer to Technical Support

Version 2

    Solution

     

    This document describes the steps required to take an Oracle export using the Oracle Data Pump utility when your data is requested by a Schneider Electric Technical Support Engineer. These steps are applicable for Oracle versions 10g, 11g, and 12c. In all cases a full export is preferred, but if that is not feasible or practical, then a schema level export is acceptable as described below. Every attempt has been made to make these steps generic and applicable to all system configurations. If you experience any issues following these steps, please contact the Support Engineer assigned to your case.

    NOTE: In ALL cases, there must be NO active edit sessions open in the database when the export is performed. If possible, ensure that there are no connections to the database, but if not, at least ensure that no one is actively editing the database.

    Full Export

    1. If your Oracle database is NOT version 11.2.0.1 or 11.2.0.2, please proceed to step 2. If your Oracle database is version 11.2.0.1 or 11.2.0.2, you must take this additional step to verify that the deferred_segment_creation initialization parameter is set to FALSE. If it is not, execute the following dynamic SQL statement as a privileged user to generate the SQL statements that will allocate an extent to all relevant tables having zero rows:

     

    select 'alter table ' || owner || '.' || table_name || ' allocate extent;'

    from dba_tables

    where (owner in (select distinct(owner) from sde.table_registry) or owner = 'PROCESS' or owner = 'RESPONDER' or owner = 'RXARCHIVE') and num_rows = 0

    order by owner, table_name;

     

    These statements will allocate an extent to the relevant empty tables in your database so that they can be imported into a database at Schneider Electric. It is strongly suggested that for SDE geodatabases, the initialization parameter deferred_segment_creation be set to FALSE.

     

      2.  Perform a full export of the database using the Oracle Data Pump utility.

     

    Schema Level Export

    1. If your Oracle database is NOT version 11.2.0.1 or 11.2.0.2, please proceed to step 2. If your Oracle database is version 11.2.0.1 or 11.2.0.2, you must take this additional step to verify that the deferred_segment_creation initialization parameter is set to FALSE. If it is not, execute the following dynamic SQL statement as a privileged user to generate the SQL statements that will allocate an extent to all relevant tables having zero rows:

     

    select 'alter table ' || owner || '.' || table_name || ' allocate extent;'

    from dba_tables

    where (owner in (select distinct(owner) from sde.table_registry) or owner = 'PROCESS' or owner = 'RESPONDER' or owner = 'RXARCHIVE') and num_rows = 0

    order by owner, table_name;

     

    These statements will allocate an extent to the relevant empty tables in your database so that they can be imported into a database at Schneider Electric. It is strongly suggested that for SDE geodatabases, the initialization parameter deferred_segment_creation be set to FALSE.

     

      2.   Determine the required schemas to be exported.

     

    select distinct(owner) from sde.table_registry order by owner;

     

    In addition, export the PROCESS, RESPONDER, and RXARCHIVE schemas, if they exist in your database.

     

      3.  Perform a schema-level export of the database which includes all the schemas identified in step 2.

     

      4.  Execute the following queries:

     

    -- Generate create tablespace statements

    select 'create tablespace ' || tablespace_name || ' datafile ' || '''' || 'c:\oracle\oradata\orca\' || tablespace_name || '_01.dbf' || '''' || ' size 100M autoextend on next 100M maxsize unlimited;'

    from dba_tablespaces

    where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','USERS','TEMP')

    order by tablespace_name

     

    -- Create users that have privileges on tables in the sde.table_registry table

    select 'create user ' || grantee || ' identified by ' || grantee || ' default tablespace users;'

    from dba_tab_privs

    where owner in (select distinct(owner) from sde.table_registry)

    and grantee in (select username from dba_users)

    group by grantee

    order by grantee;

     

    -- Create roles that have privileges on tables in the sde.table_registry table

    select 'create role ' || grantee || ';'

    from dba_tab_privs

    where owner in (select distinct(owner) from sde.table_registry)

    and grantee in (select role from dba_roles)

    group by grantee

    order by grantee;

     

      5.  Compress the export file. Add the export log file (and par file, if applicable) to the archive. Add the text file containing the results of the above queries to the archive.