If you are busy with a team developing all kinds of GTechnology enhancements, you will find yourself most of the time in Oracle, configuring metadata, creating PL/SQL code or otherwise. When all developers are sharing the same database, it is very likely that things get messed up and I therefore always like to have a local database to do my development. I do not need all the data in it, but only the structure of the database and the contents of the metadata-tables. Once this is set up, I can create my scripts completely independent of the real database without disturbing collegues. If I feel like being ready, I can even drop my database and recreate it to see if my scripts are really doing what they are supposed to. It is only after that that I run my scripts against the real database.

Here is how to setup such an environment.

1 : Export the GTECHDIAG-schema

expdp gtsys/***@SERVER DUMPFILE=gtechdiag.dmp LOGFILE=gtechdiag.log SCHEMAS=GTECHDIAG

You need to replace SERVER with your own server- tnsnames entry.

Since the GTECHDIAG-schema is locked, you need to export it using a different account. In this case, the GTech-installation schema GTSYS is used.

2 : Export the GTech schema without data

expdp gtsys/****@SERVER DUMPFILE=gt_struct.dmp  CONTENT=METADATA_ONLY LOGFILE=gt_struct.log SCHEMAS=gtsys

In this step the whole GTSYS-schema is exported without any data in it. This export also includes all the packages which was not possible using the oracle exp-command.

3 : Export the GTech schema with metadata

expdp gtsys/****@SERVER parfile=g3e.par

The parameter file g3e.par includes legend-tables unique to my GTech-installation. You should replace them with the legend-tables used in your installation. You can retrieve them with the following statement :

select g3e_definitiontable, g3e_settingstable, g3e_displaycontroltable from g3e_legend;

4 : Create local database schemas gtechdiag and gtsys and grant neccesary rights

Use regular oracle statements to add a schema with the neccesary rights. The schema name GTECHDIAG is the same in each GTech installation, user GTSYS is the schema used for your GTech installation. You can find an example over here.

5 : Import the gtechdiag schema

impdp gtechdiag/****@XE DUMPFILE=gtechdiag.dmp LOGFILE=gtechdiag.log

6 : Import the GTech schema without data

impdp gtsys/****@XE DUMPFILE=gt_struct.dump LOGFILE=gt_struct.log

The purpose of this is to create the whole structure of your GTECH-schema without all the facility data in it. If you need facility data in your local environment, you can later copy some rows via a database link or simply add some features.

7 : Import the GTech schema with metadata

impdp gtsys/****@XE dumpfile=gt-metadata.dmp logfile=gt-metadata.log TABLE_EXISTS_ACTION=REPLACE full=y

In this step you import the data you need for development, the metadata tables including legend tables unique to your GTech-installation. The filename in the dumpfile argument should match the filename specified in the oracle parameter file used in step 3.

8 : Recompile all invalid objects in the GTECHDIAG-schema

The objects in the GTECHDIAG-schema rely on objects in the GTSYS-schema which was not present during the import, so there will be invalid objects. You need to recompile them.

9 : Recompile all invalid objects in the GTSYSG-schema

Ojects in the GTSYS-schema rely on objects in the GTECHDIAG-schema which were invalid at the time of import. You need to recompile them after recompiling invalid objects in the GTECHDIAG-schema. Once every object is fine, you should be able to publish metadata.

10 : Publish metadata

If you can publish metadata, then you have succeeded.

Notes :

• These statements assume the GTech-schema is contained in gtsys, your development database can be accessed via the ‘SERVER’ tnsnames entry, and you have setup a local Oracle 11 database called XE
• You only need to transfer the GTECHDIAG-schema once since this normally doesn’t change
• Changing the initial extend of tables : If you are transferring a database with many tables, you may run out of diskspace because the import process is reserving an intial extent for each table. I recently needed to import a database with more then 3000 tables, needing much more then 20G just for importing the structure. To avoid this problem, invoke impdp with the ‘TRANSFORM=segment_attributes:n’  flag. Using this flag you can tell the import-datapump to change or ignore size.
• Changing tablespace specifications:The source database may have other tablespaces then the destination database. You can remap tablespaces using the ‘REMAP_TABLESPACE’ flag
• If you need to import the gtech-schema again, you can do so by dropping the local ‘gtsys’-schema and repeating steps 6 – 10.

An example for changing the initial extend and remapping tablespaces :

$impdp VMESDEV/*****@ORCL DUMPFILE=VMESDEV.DMP LOGFILE=VMESDEV.DMP.log TRANSFORM=segment_attributes:n REMAP_TABLESPACE=GC_DATA_FIBRE2:VM, GC_DATA_MISC2:VM

This command will map tablespaces ‘GC_DATA_FIBRE’ and ‘GC_DATA_MISC2′ to tablespace VM’.