Import and Export codebeamer Database with Oracle

The easiest way to create a dump from the codebeamer schema is the Oracle Data Pump tool.

If the admin user is a sysdba, a prompt appears for the username which should be: sys as sysdba.

Create Dump

Run the following command from command line:

expdp <adminUserName>/<adminPassword> schemas=<schemaName> parallel=<numberOfCPUCores-1> directory=<data_pump_dir> dumpfile=CB_DUMP_%U.dmp logfile=DB_DUMP_exp.log

codebeamer supports Oracle 19.3. If there is a newer version in use, the VERSION parameter should be configured too: VERSION = 19.3

By default, the value of the directory is data_pump_dir. Users can check the available directories by running the following command:

SELECT owner, directory_name, directory_path
  FROM all_directories

To create a directory, run the following command:

CREATE DIRECTORY <directory_name> AS 'absolute_path';

The dump files can be found in the <data_pump_dir>.

Import Dump

The oracle dump contains not only the data but also the structure and storage place of the data.

Tablespace

Oracle dump contains tablespace information of objects. It is necessary to remap to an existing or a new tablespace.

Create a New Tablespace

Sample SQL:

CREATE TABLESPACE <name of new tablespace> DATAFILE '<absolute path of data directory>' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 31000M;

It creates a tablespace with an initial size of 100 megabytes, and with a maximum size of 31000 megabytes.

Schema

Oracle dump contains schema information of objects. It is necessary to remap to an existing or a new schema.

Create a New Schema

To create a new database schema, run the following command:

CREATE USER C##CBROOT IDENTIFIED BY CBROOT DEFAULT TABLESPACE <name of tablespace for the schema> QUOTA UNLIMITED ON <name of tablespace for the schema>;
GRANT CREATE SESSION TO C##CBROOT;
GRANT CREATE TYPE TO C##CBROOT ;
GRANT CREATE TABLE TO C##CBROOT ;
GRANT CREATE CLUSTER TO C##CBROOT ;
GRANT CREATE TRIGGER TO C##CBROOT ;
GRANT CREATE OPERATOR TO C##CBROOT ;
GRANT CREATE SEQUENCE TO C##CBROOT ;
GRANT CREATE INDEXTYPE TO C##CBROOT ;
GRANT CREATE PROCEDURE TO C##CBROOT ;
GRANT CREATE VIEW TO C##CBROOT ;
GRANT EXECUTE ON CTXSYS.CTX_DDL TO C##CBROOT ;
GRANT EXECUTE ON DBMS_LOB TO C##CBROOT ;

/*The following permissions is necessary to import the dump but it is not necessary to use codebeamer. If there is an admin user who can import the dump then it is not necessary to execute the following permissions*/
GRANT IMPORT FULL DATABASE TO C##CBROOT;
GRANT READ, WRITE ON DIRECTORY data_pump_dir TO C##CBROOT;

Import Dump

To import a database dump, run the following command:

impdp <adminUserName>/<adminPassword> directory=<data_pump_dir> schemas=<schemaName> dumpfile=<dump file name>_%U.dmp logfile=DB_DUMP_imp.log job_name=job1 PARALLEL=<numberOfCPUCores-1>

Optional parameters:
REMAP_SCHEMA=<schema name in dump>:<schema name in database>
REMAP_TABLESPACE=<table space name in dump>:<table space name in database>

Gather Schema Statistics

It is necessary to refresh index information of the schema. To gather statistical information about the database schema, run the following command:

BEGIN
	DBMS_STATS.GATHER_SCHEMA_STATS (
		ownname => '<schema name>',
		estimate_percent => 100,
		method_opt => 'FOR ALL COLUMNS SIZE AUTO',
		degree => 1,
		granularity => 'ALL',
		cascade => TRUE,
		options => 'GATHER'
	) ;
END ;

Obfuscate Sensitive Data

THE FOLLOWING PROCESS MODIFIES THE DATA IN THE DATABASE, AND IT IS NOT POSSIBLE TO REVERT MODIFICATIONS. DO NOT USE IT ON PRODUCTION ENVIRONMENT!

1. Import the data into a new database.
2. Download the script and execute the command upon the codebeamer version:
   • The script: obfuscate_data_oracle.sql
   • The command: sqlplus <username>/<password>@<SID> @<absolutePath>/obfuscate_data_oracle.sql

After executing the script, it is not possible to login to codebeamer because all user credentials (usernames, passwords) are removed.

To set 007 as password for all users, run the following script:

UPDATE users
SET registrydate = null, passwd = LOWER(rawtohex(sys.dbms_crypto.hash(UTL_I18N.STRING_TO_RAW ('007'|| id ||'700101010000','AL32UTF8'), 6)));

commit;

Now it is possible login to codebeamer with user-<user-id>/007. For example: user-1/007