Performing a Full Accela Database Migration – Oracle

Topics:

Exporting the Data

In this migration, you export a copy of the database from the source environment and you import the database as new or as a replacement of the target environment. For instance, a database administrator (DBA) exports the Accela and Jetspeed schemas that uses either the exp or expdp utilities provided by Oracle. Though an example of this for each utility follows, please consult the Oracle documentation for further explanation of any parameters.

exp system/password@tns file=exportSource.dmp log=exportSource.log owner=(ACCELA,JETSPEED) 
expdp system/password@tns directory=DATA_PUMP_DIR dumpfile=exportSource.dpdmp logfile=exportSource.log schemas=(ACCELA,JETSPEED)

Both utilities produce a logical export of the Accela and Jetspeed schemas that you can transfer, by file copy commands, to the target database.

Preparing the Target Database

Prior to importing the data, install Civic Platform (Installing Civic Platform), disable the foreign key (Disabling the Foreign Key), and delete all system configuration data and agency configuration data (Deleting System and Agency Configuration Data).

Topics:

Installing Civic Platform

The Civic Platform installer creates the database tables, into which you migrate the configuration data and transaction data, in the target database.

  1. Install Civic Platform to the target database (see the Civic Platform Installation Guide).

    Note:

    The Civic Platform version for the target environment must match the SP version number of the Civic Platform version for the source environment.

Disabling the Foreign Key

Run the following script.

set pagesize 0 feedback off linesize 1000 trimspool on 
spool disable_fk.lst 
select 'alter table accela.'||table_name||' disable constraint '||constraint_name||';' 
from dba_constraints where owner='ACCELA' and constraint_type='R' and status='ENABLED' 
union 
select 'alter table jetspeed.'||table_name||' disable constraint '||constraint_name||';' 
from dba_constraints where owner='JETSPEED' and constraint_type='R' and status='ENABLED'; 
spool off 
-- run the spooled result 
set feedback on 
@disable_fk.lst

Deleting System and Agency Configuration Data

Run the following script.

set pagesize 0 feedback off linesize 1000 trimspool on 
 spool delete_aa.lst 
 select 'delete accela.'||table_name||';'||' 
 commit;' 
 from dba_tables where owner='ACCELA'  
 union 
 select 'delete jetspeed.'||table_name||';'||' 
 commit;' 
 from dba_tables where owner='JETSPEED'  
 order by 1 desc; 
 spool off 
 -- run sql script file 
 set feedback on echo on 
 @delete_aa.lst

Importing the Data

If importing into an existing Civic Platform environment, record the environment settings before you import or restore into the target database (Copying and Setting Accela Environment Parameters).

Use the imp or impdp commands to import the data to the target database (depending on whether you created the export with the exp or expdp commands, respectively). Though an example of this for each utility follows, please consult the Oracle documentation for further explanation of any parameters.

imp system/password@tns file=exportSource.dmp log=importTarget.log fromuser=(ACCELA,JETSPEED) touser=(ACCELA,JETSPEED)ignore=y 
impdp system/password@tns directory=DATA_PUMP_DIR dumpfile=exportSource.dpdmp logfile=importTarget.log remap_schema=ACCELA:ACCELA remap_schema=JETSPEED:JETSPEED

Enabling the Foreign Key

Run the following script.

set pagesize 0 feedback off linesize 1000 trimspool on 
spool enable_fk.lst 
select 'alter table accela.'||table_name||' enable constraint '||constraint_name||';' 
from dba_constraints where owner='ACCELA' and constraint_type='R' and status='DISABLED' 
union 
select 'alter table jetspeed.'||table_name||' enable constraint '||constraint_name||';' 
from dba_constraints where owner='JETSPEED' and constraint_type='R' and status='DISABLED'; 
spool off 
-- run the spooled result 
set feedback on 
@enable_fk.lst