Schema refresh is regular activity in DBA’s life that needs to carried out carefully as most of the time production data is getting copied in non-prod environments
Definition: Copying data from one database schema to other database schema or copying data from one schema to other schema in same database. Before proceeding we should be aware of below details
Source DB, schema and server name–> DB: prod1 schema: prod1_data prod server->prod_srvr
Target DB, schema and server name –> DB: test1 schema: test1_data test server->test_srvr
We will describe schema refresh with different cases depending on versions of source and target database
CASE I: Databases with same version >=10g
Step 1: Take export backup using datapump method of source and target database
Create physical directory on server and logical directory in database; make sure we have sufficient space on server to take backups
mkdir -p /backup/db
CREATE OR REPLACE DIRECTORY datapump_dir AS '/backup/db';
expdp userid=prod11g/Prod#342 directory=datapump_dir dumpfile=expdp_prod1_data.dmp, logfile=expdp_prod1_data.log schemas=prod1_data
Once backup is completed check logfile for any errors, Perform same steps on target database if target schema exists
mkdir -p /backup/db
CREATE OR REPLACE DIRECTORY datapump_dir AS '/backup/db';
expdp userid=test11g/Test#342 directory=datapump_dir dumpfile=expdp_test1_data.dmp, logfile=expdp_test1_data.log schemas=test1_data
Identify tablespaces associated with schema in source and target database
select distinct tablespace_name from dba_segments where owner='PROD1_DATA';
select distinct tablespace_name from dba_segments where owner='TEST1_DATA';
Step 2: Transfer source dumpfile to target dumpfile using SCP utility as below
scp expdp_prod1_data.dmp username@test_srvr:/backup/db
Step 3: Drop schema in target database which needs to be refreshed and import source dumpfile into target database
drop user test1_data cascade;
CREATE OR REPLACE DIRECTORY import_dir AS '/backup/db';
impdp userid=test11g/Test#342 directory=import_dir dumpfile=expdp_prod1_data.dmp logfile=impdp_test1_data.log remap_schema=PROD1_DATA:TEST1_DATA remap_tablespace=<source tablespace>:<target tablespace>
Note: If name of source schema and target schema is same then remap_schema parameter is not required.
Verify the object count in source and target database
Select OWNER, OBJECT_TYPE, count (object_name) from dba_objects where OWNER='TEST1_DATA' group by OWNER, OBJECT_TYPE;
Compile invalid objects using below script
select 'alter '||object_type||' ' ||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and owner in ('TEST1_DATA');
CASE 2: Source DB version lower than target DB version (>=10g)
Datapump support this type of refresh without any issues. Follow same process as Case 1
CASE 3: Databases with version lower than 10g
In such cases we need to use traditional export-import method
Step 1: Take exp backup of source and target database schemas (target if required)
exp userid=prod9i/Prod#234 file=expdp_PROD1_DATA.dmp log=expdp_PROD1_DATA.log owner=expdp_PROD1_DATA compress=N consistent=Y statistics=none buffer=20000000
Step 2: Copy dump file to target location using scp as above mentioned.
Step 3: Take backup of create user statement in target database before dropping user then drop user and run create user command as imp do not
Step 4: If associated tablespaces are different in source and target database then generate indexfile (i.e. DDL script) using below command
imp userid=test9i/Test#342 file=expdp_PROD1_DATA.dmp indexfile=file_ddl.sql
Once indexfile is generated replace all tablespace names with new tablespace name as per in target and run script in order to create objects under schema
Step 5: Import the source dump in target
imp userid=” '/ as sysdba' ” file=expdp_PROD1_DATA.dmp, log=imp_expdp_TEST1_DATA.log fromuser=PROD1_DATA touser=TEST1_DATA ignore=y
Verify objects in source and target and compile if required