Schema Level Refresh Using EXPDP/IMPDP and EXP/IMP

Description:

Best practice to refresh a schema
                                                               
                                                                 
In PROD DB

1. First Check The Default Tablespace OF BTM User,and Default Temporary Tablespace In PROD Side:

SQL> SELECT owner,tablespace_name,sum(bytes/1024/1024) FROM dba_segments WHERE OWNER='BTM' GROUP BY owner, tablespace_name;
OWNER            TABLESPACE_NAME                SUM(BYTES/1024/1024)
-------------------------------------------------------------------
BTM              BTM                                         64691

SQL> SELECT username, temporary_tablespace FROM dba_users where USERNAME='BTM';
USERNAME                 TEMPORARY_TABLESPACE
---------------------------------------------
BTM                      TEMP1

2. Check The Default Tablespace OF BTM User,and Default Temporary Tablespace In TEST Side:

SELECT owner,tablespace_name,sum(bytes/1024/1024) FROM dba_segments WHERE OWNER='BTM' GROUP BY owner, tablespace_name;
OWNER                    TABLESPACE_NAME        SUM(BYTES/1024/1024)
--------------------------------------------------------------------
BTM                      BTM                    50905

SELECT username, temporary_tablespace FROM dba_users where USERNAME='BTM';
USERNAME                 TEMPORARY_TABLESPACE
---------------------------------------------
BTM                      TEMP1

3. In Prod Side create a directory to export dumpfile

SQL> CREATE OR REPLACE DIRECTORY EXPDP_DIR AS '/w_data3/oradata/EXPDP';
SQL> GRANT READ, WRITE ON DIRECTORY EXPDP_DIR TO system;

4. Exstimate the dumpsize and Export The schema in prod side as below using expdp

expdp schemas=BTM estimate_only=Y(To estimate the schema size)
expdp system/manager directory=EXPDP_DIR dumpfile=BTM.dmp logfile=BTM.log schemas=BTM exclude=db_links
Export The schema in prod side as below using    or
Exp file=BTM.dmp log=BTM.log owner=BTM system/manager(in exp only no need to create directory dump will create present working directory)

5. Copy the dump file from prod to test

scp BTM01.dmp 11gtest@192.11.20.133:/Data/oradata/DATA_PUMP_DIR
Note:here 11gtest means login username
     192.168.20.133 mean testhostnam
     /Data/oradata/DATA_PUMP_DIR means directory path

6.TEST DB

SQL> select * from dba_directories(if directory exists no need to create directory otherwise create directory as like prod);

SQL> CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/Data/oradata/DATA_PUMP_DIR';

SQL> GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO system;

7. Drop user already existing in test

SQL> Drop user BTM cascade;

8. Check Tablespaces availability and sufficient free space

impdp system/manager directory=DATA_PUMP_DIR dumpfile=BTM.dmp logfile=BTM.log  remap_schema=BTM:BTM exclude=DB_LINK;
   or
import the dump file in test imp
imp file=BTM.dmp log=BTM.log fromuser=BTM touser=BTM

9. Remove the jobs of BTM user at the time of import for that run below four scripts in test then it will show the scripts to remove jobs and run that scripts
select 'exec dbms_ijob.broken('|| job ||',true);' from dba_jobs where broken = 'N' and schema_user IN ('BTM');
select 'exec dbms_ijob.remove('|| job ||');' from dba_jobs where broken = 'Y' and schema_user IN ('BTM');
select 'exec dbms_ijob.broken('|| job ||',true);' from dba_jobs where broken = 'N' and schema_user IN ('BTM');
select 'exec dbms_ijob.remove('|| job ||');' from dba_jobs where broken = 'Y' and schema_user IN ('BTM');
---Execute the output script to broken the imported jobs
---Remove the jobs permanently from db (Very imp)

10. After import is complete compare all objects in test and prod by using below query

SQL> Select OWNER, OBJECT_TYPE, count(*) from dba_objects where OWNER='BTM' group by OWNER,OBJECT_TYPE;

12. Change password of schema in test DB as per  developer team.

SQL> Alter user BTM identified by apps;

13. Find all privileges on PROD db and rung the output scripts in test.
select 'grant ' || PRIVILEGE || ' on '|| OWNER || '.' || TABLE_NAME || ' to ' || GRANTEE ||';' from dba_tab_privs where GRANTEE='BTM';
select 'grant ' || PRIVILEGE || ' on '|| OWNER || '.' || TABLE_NAME || ' to ' || GRANTEE ||';' from dba_tab_privs where owner='BTM';

14. Execute above output on TEST db

15. Find invalid objects both in PRODUCTION and TEST db then compare.
      col OBJECT_NAME for a30
      set lines 200
      select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where OWNER='BTM' and status='INVALID';

16. Please run utlrp.sql to compile invalid objects.

@$ORACLE_HOME/rdbms/admin/utlrp.sql

18. Count all the objects in source db as well as in Test db

Select OWNER,OBJECT_TYPE,count(*) from dba_objects where OWNER='BTM' group by OWNER,OBJECT_TYPE;

OWNER                    OBJECT_TYPE           COUNT(*)
-------------------------------------------------------
BTM                      SEQUENCE                    46
BTM                      FUNCTION                    24
BTM                      MATERIALIZED VIEW            5
BTM                      TYPE                         1
BTM                      INDEX                      144
BTM                      PACKAGE                      7
BTM                      VIEW                        21
BTM                      TRIGGER                      5
BTM                      DATABASE LINK                2
BTM                      TABLE                      220
BTM                      PROCEDURE                  300
BTM                      PACKAGE BODY                 3
BTM                      FUNCTION                    24
BTM                      MATERIALIZED VIEW            5
BTM                      TYPE                         1
BTM                      INDEX                      144
BTM                      PACKAGE                      7
BTM                      VIEW                        21
BTM                      TRIGGER                      5
BTM                      TABLE                      220
BTM                      PROCEDURE                  300
BTM                      PACKAGE BODY                 3
19. If any objects are not available in test take meta database for those objects from PROD and Execute metadata in test(Toad, PL/SQL Better tools to get meta data)
20. Compare Finally All object and Release the Test DB for Dev's team

###############################################  All The Best ###############################################