SCHEMA REFRESH USING DATA PUMP

1. Check for default tablespace and schema size

SELECT owner,tablespace_name,sum(bytes/1024/1024) FROM dba_segments WHERE OWNER='LGDMS_NEW'    GROUP BY owner, tablespace_name;

SELECT username, temporary_tablespace FROM  dba_users where USERNAME='LGDMS_NEW';

2. Check for objects

Select  count(*) from DBA_OBJECTS where OWNER='LGDMS_NEW';

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

3.  Export Schema to the desired location using expdp

CREATE OR REPLACE DIRECTORY expdp_dir AS '/webapp_arch/arch/expdp/';

GRANT READ, WRITE ON DIRECTORY expdp_dir TO system;

expdp system/manager schemas=lgdms_new directory=expdp_dir dumpfile=lgdms_new.dmp logfile=expdplgdms_new.log

4. Transfer dmp file to the desired location

scp lgdms_new.dmp 11gtest@192.168.20.133:/w_data2/oradata/EXP_IMP/.

5. Drop user to be refreshed

  drop user lgdms_new cascade;

6. No need to create user when importing using impdp

7. Check Space on destination DB

8. Import dmp file using impdp

CREATE OR REPLACE DIRECTORY expdp_dir AS '/w_data2/oradata/EXP_IMP/';

GRANT READ, WRITE ON DIRECTORY expdp_dir TO system;

impdp system/manager schemas=lgdms_new directory=expdp_dir dumpfile=lgdms_new.dmp logfile=impdplgdms_new.log

9. Compare Objects from both schema

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

============================================================================

TO UPDATE  SEQUENCES ON TEST SERVER

1. To drop sequences on test server run output of the following command

SELECT  'DROP SEQUENCE '    || sequence_name  || ';'

FROM dba_sequences where SEQUENCE_OWNER='LGDMS_NEW'

ORDER BY sequence_name;

2. TO recreate sequences on test server do the following steps

*run the following query on production server

SELECT 'CREATE SEQUENCE '  || sequence_name ||

        ' INCREMENT BY '    || increment_by  ||

        ' START WITH  ' ||TO_CHAR(LAST_NUMBER+1) ||

        ' MINVALUE '        || MIN_VALUE     ||

        ' MAXVALUE '        || MAX_VALUE     ||

          CASE cycle_flag WHEN 'N' THEN ' NOCYCLE ' ELSE ' CYCLE ' END ||

          CASE order_flag WHEN 'N' THEN ' NOORDER ' ELSE ' ORDER ' END ||

        ' CACHE ' || CACHE_SIZE || ';'

  FROM dba_sequences where SEQUENCE_OWNER='LGDMS_NEW'

 ORDER BY sequence_name;

*Output of the above query need to be run on test server

3. Compare Objects from both schema

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