How To Create Oracle Database Dump File

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

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

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 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 initial size of 100 megabytes and wiith a maximum size is 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 WILL MODIFY THE DATA IN THE DATABASE AND IT IS NOT POSSIBLE TO REVERT MODIFICATIONS. DO NOT USE IT ON PRODUCTION ENVIRONMENT!

Import your data into a new database.
Download the script and execute the command upon the Codebeamer version:
- Earlier than codeBeamer 10.1
  - The script obfuscate_data_oracle_before_101.sql
  - The command: sqlplus <username>/<password>@<SID> @<absolutePath>/obfuscate_data_oracle_before_101.sql
- codeBeamer 10.1
  - The script: obfuscate_data_oracle_101.sql
  - The command: sqlplus <username>/<password>@<SID> @<absolutePath>/obfuscate_data_oracle_101.sql
- Later than codeBeamer 10.1
  - 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)));

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

LDAP/AD authentication must be disabled even if Fallback option is activated.