Step to refresh schema from Production to TEST server in Oracle database by EXPDP

Step performed on Production Database

1. Check the User/Schema which you want to export from production for refresh activity.

Select username,account_status from dba_users where username = 'HR';


2. Create a directory on production environment and db directory object.

-- On Server
Mkdir D:\databasebackup


--On SQLPLUS
Create directory dbbackup as 'D:\databasebackup';


3. Backup the Schema “HR” from production through EXPDP logical backup.
–Provide dba username/password on prompt if you donot provide hr username and password but HR has permission of EXPDP in database.

expdp schemas=HR directory=dbbaackup dumpfile=HRdump.dmp logfile=HRLOGfile.log


4. It will generate HRdump.dmp file at location D:\databasebackup

Step performed on TEST Database
1. FTP the dump from production to Test Environment.

2. Check the user present in TEST environment.

Select username,account_status from dba_users where username = 'HR';


3. If “HR schema” present on test then you need to take backup by performing following steps then Drop the Schema.
IF “HR schema” is not present, then simple go to step 3 for creating user HR

-- On Test Server
Mkdir D:\databasebackup

--On TEST SQLPLUS, Create directory
Create directory dbbackup as 'D:\databasebackup';

--On Test Server, run expdp command for backup test HR schema.
expdp schemas=HR directory=dbbaackup dumpfile=HRTESTdump.dmp logfile=HRTESTLOGfile.log

--If you know the password of HR in TEST environment then good otherwise take backup with following commands:
select 'CREATE USER '||username||' IDENTIFIED BY VALUES '''||PASSWORD||''' DEFAULT TABLESPACE ' ||DEFAULT_TABLESPACE||' TEMPORARY TABLESPACE '||TEMPORARY_TABLESPACE||' PROFILE '||PROFILE|| ' ACCOUNT '||ACCOUNT_STATUS||';' from dba_users where username='HR';
SELECT name,spare4 FROM sys.user$ WHERE name = 'HR';

--Finally Drop the HR schema in TEST Environment
Drop user HR cascade;


4. Need to Create HR schema if its present then use password got in 2nd steps, otherwise create new HR user with your own password.

--Create schema with spare4 as value in-between quotes got in 2nd steps.
create user HR identified by values 'spare4-value';
--Example:
create user HR identified by values 'S:47C79473D7A25ACFAA887FB62F9D8BDBDC20D7C7788130828983C8E66394;T:0DFF44FFED7C6983BEAF819921A432DA4C39C8E81C876726A1E07F5341D5CD0BF43B3B9D9141532C1C2498E6D64B0A1729B5ABE2ED9F87FFBBED1C64381DDB06894BAE0272D4E0357E91366CE36CD9B7';


--If "HR" user in not present, then create user with your own password in TEST environment.
Create user HR identified by Password1;


5. Grant the access to users.
Grant depend upon you need like create table, create view, create index etc. I am providing DBA grant which having all privileges to user for testing purpose.

Grant connect to HR;
Grant dba to HR;


6. Start the IMPDP process.
–Copy the dump from production to test environment at pointer location DBBACKUP in TEST database.
–Dumpfile parameter use the file which is copied from production server placed at ‘D:\databasebackup’

impdp schemas=HR directory=dbbackup dumpfile=HRdump.dmp logfile=IMPHRLOGfile.log


6. After complete check the invalid objects and run the ultrp.sql for validate the objects in Oracle database.

--Run utlrp.sql for make invalid object to valid.
@%ORACLE_HOME%\rdbms\admin\utlrp.sql;
@?\rdbms\admin\utlrp.sql;


--Check invalid objects
Select object_name,object_type from dba_objects where status='INVALID' and owner = 'HR';