Oracle Database Schema Refresh

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