Duplicate database (Schema only)

Copying schemas w/o user passwords

This scenario is used when different passwords are used in source and target databases and requestor does not want to replace them. If passwords has to be copied too check 2nd part of this document.

Check for paths with enough free space

  • you can use several paths for export
  • parameter FILESIZE can be used to limit the size of export file
  • during export: if the size of export is bigger than FILESIZE you will be prompted to enter name of next export file
  • all of these files will be puted in one folder
  • in case you do not have enough space you can move already processed export files to other locations
  • during import: if there are more export files and one of them is processed you will be prompted to enter name of next file in row
  • all of these files must be in same folder
  • if you have files in different folders you can use links (tested and working fine)

Create export parameters file

Example of UNIX command for creating parameter file to export FAF_DWH_HIS schema:

vi exp_faf_dwh_his.par### exp_faf_dwh_his.par ###
FILESIZE=10G
FILE=exp_faf_dwh_his.dmp
CONSISTENT=Y
OWNER=FAF_DWH_HIS
GRANTS=Y
INDEXES=Y
FEEDBACK=1000
LOG=exp_faf_dwh_his.log

Export data

Use previously created parameters file. Example of UNIX command:

exp \'/ as sysdba\' PARFILE=exp_faf_dwh_his.par

Create SQL script for index creation

This script will be used later to create indexes. Creating indexes is faster then import. Example of UNIX command:

TBD

Drop tables

Drop tables which will be imported. You can find these tables in export log:

Example of SQL command which drops table:

drop table FAF_DWH_HIS.PLAN_TABLE;

Enlarge parameter sort_area_size

After data are imported tables are sorted and this consumes much time if this parameter is small. Therefore check parameter sort_area_size which should be at least 1048576. Example SQL command to check sort_area_size parameter:

show parameter sort_area_size;

Example SQL command to set new value:

alter database set sort_area_size=1048576 deferred;

Create import parameters files

Example UNIX command for creating parameter file to import FAF_DWH_HIS schema:

vi imp_faf_dwh_his1.par

You should create several parameters files as each file can be used in parallel import session. Using more sessions increases the import performance (up to 8 import sessions is fine). In each import file define different tables all together with cca same number of rows. Example of files used to import FAF_DWH_HIS schema:

### imp_faf_dwh_his1.par ###
FILE=exp_faf_dwh_his.dmp
FROMUSER=FAF_DWH_HIS
TOUSER=FAF_DWH_HIS
TABLES=(PLAN_TABLE,TEMP_D_CLIENT_LOCATION,TEMP_SK_TRANSACTION,TEMP_TO_DELETE,T_D_CLIENT,T_D_CLIENT_CONTRACT_ELEMENT,T_D_CLIENT_COSTCENTRE,T_D_CLIENT_INTERFACE,T_D_CLIENT_LOCATION,T_D_CLIENT_SERVICELINE,T_D_CLIENT_TIME,T_D_CLIENT_USAGE_CLASS,T_D_CONTRACT,T_D_CONTRACT_ELEMENT,T_D_COSTCENTRE,T_D_EMPLOYEE,T_D_EMPLOYEE_ROLE,T_D_INV_CUSTOMER,T_D_JC_ENTITY,T_D_LOB,T_D_PAYROLL_ELEMENT,T_D_PROJECT,T_D_SERVICELINE,T_D_TIME,T_D_TRANSACTION_BALMOV,T_D_USAGE_CLASS,T_D_WO,T_F_FINANCE_BACKUP,T_F_FINANCE_BALMOV,T_D_GLACCOUNT,T_D_SUPPLIER)
INDEXES=N
COMMIT=Y
LOG=imp_faf_dwh_his.log
### imp_faf_dwh_his2.par ###
FILE=exp_faf_dwh_his.dmp
FROMUSER=FAF_DWH_HIS
TOUSER=FAF_DWH_HIS
TABLES=T_D_PO
INDEXES=N
COMMIT=Y
LOG=imp_faf_dwh_his.log
### imp_faf_dwh_his3.par ###
FILE=exp_faf_dwh_his.dmp
FROMUSER=FAF_DWH_HIS
TOUSER=FAF_DWH_HIS
TABLES=T_D_TRANSACTION
INDEXES=N
COMMIT=Y
LOG=imp_faf_dwh_his.log
### imp_faf_dwh_his4.par ###
FILE=exp_faf_dwh_his.dmp
FROMUSER=FAF_DWH_HIS
TOUSER=FAF_DWH_HIS
TABLES=T_D_TRANSACTION_BACKUP
INDEXES=N
COMMIT=Y
LOG=imp_faf_dwh_his.log
### imp_faf_dwh_his5.par ###
FILE=exp_faf_dwh_his.dmp
FROMUSER=FAF_DWH_HIS
TOUSER=FAF_DWH_HIS
TABLES=T_F_FINANCE
INDEXES=N
COMMIT=Y
LOG=imp_faf_dwh_his.log

Import files

Example of UNIX commands used to import in 5 parallel sessions:

imp \'/ as sysdba\' PARFILE=imp_faf_dwh_his1.par
imp \'/ as sysdba\' PARFILE=imp_faf_dwh_his2.par
imp \'/ as sysdba\' PARFILE=imp_faf_dwh_his3.par
imp \'/ as sysdba\' PARFILE=imp_faf_dwh_his4.par
imp \'/ as sysdba\' PARFILE=imp_faf_dwh_his5.par

Create indexes

TBD

Copying schemas with user passwords

This scenario is used when requestor wants to have same passwords as in source database.

Copy schemas

TBD

Copy passwords

TBD

Leave a Reply