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 then you will get a prompt to type username which should be: sys as sysdba

Create Dump

Use 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

By default the directory is 'data_pump_dir' but you can check the available directories with the following command:

SELECT owner, directory_name, directory_path
  FROM all_directories

If you would like to create a directory you can do that with the following command:

CREATE DIRECTORY <directory_name> AS 'absolute_path';

You can find the dump files in the <data_pump_dir>.

Import Dump

The oracle dump contains not only the data but 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:

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

It will create a tablespace which initial size is 100 megabyte and maximum size is 31000 megabyte.

Schema

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

Create a new schema

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

Use the following command from command line:

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 statistic

It is necessary to refresh index information of the schema.

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

WARNING: THE FOLLOWING PROCESS WILL MODIFY THE DATA IN THE DATABASE AND IT IS NOT POSSIBLE TO REVERT MODIFICATIONS. DO NOT USE IT ON PRODUCTION SYSTEM!

1. Import your data into a new database.
2. For Codebeamer version beforre 10.1 execute the following script:

     sqlplus <username>/<password>@<SID> @<absolutePath>/obfuscate_data_oracle_before_101.sql

3. Since 20.11-final you should execute the following:

     sqlplus <username>/<password>@<SID> @<absolutePath>/obfuscate_data_oracle.sql

4. For Codebeamer version 10.1 you should execute the following:

    sqlplus <username>/<password>@<SID> @<absolutePath>/obfuscate_data_oracle_101.sql

After the script execution it is not possible to login to codeBeamer because all user names and passwords are removed.

You can use the following script to set 007 as password for all user:

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

Note: if autocommit is not active in sqlplus, commit may be needed after user password update:

commit;

Now you can login to codeBeamer with user-<user id>/007. For example: user-1/007

obfuscate_data_oracle_before_101.sql

obfuscate_data_oracle_101.sql

obfuscate_data_oracle.sql