SCHEMA REFRESH in ORACLE

Steps for SCHEMA REFRESH:-
------------------------
1. Capture the SOURCE database SCHEMA OBJECTS COUNT which are to be refreshed.
2. Take EXPORT of required SCHEMA(s) at SOURCE Database.
3. Copy the EXPORT DUMPFILE(s) to DESTINATION Database Server.(SCP)
4. Take EXPORT of required SCHEMA(s) at DESTINATION Database.(Recommended)
5. DROP the SCHEMA(s) objects at DESTINATION Database except DATABASE LINK (DB LINK).
6. IMPORT the Copied SOURCE EXPORT DUMPFILE(s) to the DESTINATION Database.
7. THE TARGET SCHEMA OBJECT COUNT SHOULD BE SAME AS THE SOURCE SCHEMA OBJECT COUNT CAPTURED.
8. Gather Schema statistics at DESTINATION Database after schema refresh.(Recommended)

SOURCE
------
Hostname       :
Database Name  :
Schema(s) Name :

DESTINATION
------
Hostname       :
Database Name  :
Schema(s) Name :


@ SOURCE
--------
Database Details:
----------------
http://select-star-from.blogspot.in/2013/08/oracle-database-details.html
Table Space Report
------------------
http://select-star-from.blogspot.in/2013/08/tablespace-utilization-script-in-oracle.html
ASM Space Report
----------------
http://select-star-from.blogspot.in/2013/03/asm-tablespace-utilization-scripts.html

Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');

select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;

select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type; 

FILE_NAME
---------
/FILE/LOCATION/

Check for the space availability for taking backup
--------------------------------------------------
df -h or df -gt
df -h /FILE/LOCATION/

cd /source/backup/location/

mkdir EXPORT_REF_NO
chmod 755 EXPORT_REF_NO
cd EXPORT_REF_NO

pwd
/source/backup/location/EXPORT_REF_NO

EXPDP
-----

col DIRECTORY_PATH for a80;
select * from dba_directories;

create or replace directory <EXPDPDIR> as '/source/backup/location/EXPORT_REF_NO';
grant read,write ON DIRECTORY <EXPDPDIR> to <SYSTEM>;
alter user <username> quota unlimited on <tablespace_name>; ---> If required


col DIRECTORY_PATH for a80;
select * from dba_directories;

PAR file
--------
vi SOURCE_SCHEMA_EXPDP.par

userid="/ as sysdba"
directory=DATAPUMP_DIR
dumpfile=source_schemaname_expdp_%U.dmp
logfile=source_schemaname_expdp.log
schemas=schemaname1,schemaname2
exclude=statistics

if required,
PARALLEL=6
JOB_NAME=SOURCE_SCHEMA_EXPDP
COMPRESSION=ALL
STATUS=60
FLASHBACK_TIME="TO_TIMESTAMP('24-APR-2013 16:00:00','DD-MON-YYYY HH24:MI:SS')"
--OR--
FLASHBACK_TIME="TO_TIMESTAMP('24-APR-2013 16:00:00','DD-MM-YYYY HH24:MI:SS')"

:wq

nohup expdp parfile=SOURCE_SCHEMA_EXPDP.par &

Check the status of EXPORT
--------------------------
http://select-star-from.blogspot.in/2013/06/longops-export.html


## COMPRESSION - Reduce size of dumpfile contents, where valid keyword values are: (METADATA_ONLY) and NONE.

## You can export backup of database which was like certain months ago. This you can do using the FLASHBACK_TIME or FLASHBACK_SCN option.

## FLASHBACK_TIME - Time used to get the SCN closest to the specified time.The export operation is performed with data that is consistent as of this SCN.

EXP
---
If the size of the schema is less better to go for EXP/IMP
nohup exp <user>/`pwd` file=source_schemaname_exp.dmp log=source_schemaname_exp.log owner=schemaname statistics=NONE resumable=y resumable_name=continue resumable_timeout=18000

-----------------------------------------------------------------------------------------------------

SCP SOURCE DUMP FILES TO DESTINATION 
----------------------------------------------------------
@ Source
--------
scp source_schemaname_expdp*.dmp oracle@target_hostname:/target/backup/location/EXPORT_REF_NO/

(OR)

@ Target
--------
cd /target/backup/location/EXPORT_REF_NO/
scp oracle@source_hostname:/source/backup/location/EXPORT_REF_NO/source_schemaname_expdp*.dmp .

pwd
/target/backup/location/EXPORT_REF_NO/
ls -lrth source_schemaname_expdp*.dmp --------> scp files are here

-----------------------------------------------------------------------------------------------------

@ Target
--------
Database Details:
----------------
http://select-star-from.blogspot.in/2013/08/oracle-database-details.html
Table Space Report
------------------
http://select-star-from.blogspot.in/2013/08/tablespace-utilization-script-in-oracle.html
ASM Space Report
----------------
http://select-star-from.blogspot.in/2013/03/asm-tablespace-utilization-scripts.html

Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');

select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;

select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type; 


Check for the space availabilty for taking backup
-------------------------------------------------
df -h or df -gt
df -h /target/backup/location/

cd /target/backup/location/

mkdir EXPORT_REF_NO
chmod 755 EXPORT_REF_NO
cd EXPORT_REF_NO

pwd
/target/backup/location/EXPORT_REF_NO

EXPDP
-----

col DIRECTORY_PATH for a80;
select * from dba_directories;

PAR file
--------

vi TARGET_SCHEMA_EXPDP.par &  -----> same as above PAR file at SOURCE DATABASE
:wq
nohup expdp parfile=TARGET_SCHEMA_EXPDP.par &

Check the status of EXPORT
--------------------------
http://select-star-from.blogspot.in/2013/06/longops-export.html

EXP
---
If the size of the schema is less better to go for EXP/IMP
nohup exp userid=\'/ as sysdba \' file=target_schemaname_exp.dmp log=target_schemaname_exp.log owner=schemaname statistics=NONE resumable=y resumable_name=continue resumable_timeout=18000 &

------------------------------------------------------------------------------------------------

Drop Schema(s) Objects
http://select-star-from.blogspot.in/2013/05/drop-objects.html

Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');

select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;

select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type; 

exit

NOTE:-
If required,To remove all dropped objects from the recyclebin (current user)

$sqlplus "/as sysdba"

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

-----------------------------------------------------------------------------------------------------

pwd
/target/backup/location/EXPORT_REF_NO/

ls -lrth source_schemaname_expdp*.dmp

Restore schema backup
---------------------

IMPDP
-----
 vi import_source_schemaname_expdp.par

 userid="/ as sysdba" 
directory=EXPDPDIR
dumpfile=source_schemaname_expdp_%U.dmp

logfile=import_source_schemaname_expdp.log
schemas=schemaname1,schemaname2
PARALLEL=6
JOB_NAME=IMPORT_SCHEMA_EXPDP

If required,
remap_schema=<source_schema1>:<target_schema1>,<source_schema2>:<target_schema2>
remap_tablespace=<source_tablespace>:<target_tablespace>
exclude=constraint,ref_constraint,index

nohup impdp parfile=import_source_schemaname_expdp.par &


Check the status of IMPORT
--------------------------
http://select-star-from.blogspot.in/2013/06/longops-import.html

Validation of Import of Schemas
-------------------------------
set pages 50000 lines 32767
col owner format a15
col object_type format a20
col object_name format a30
select owner, object_name, object_type,status,created,last_ddl_time from dba_objects where owner in ('&owner')  group by object_type,owner order by object_type
/

IMP
---
nohup imp userid=\'/ as sysdba \' file=source_schemaname_exp.dmp log=source_schemaname_exp_import.log fromuser=schemaname touser=schemaname ignore=y statistics=NONE commit=y resumable=y resumable_name=continue resumable_timeout=18000 &

-----------------------------------------------------------------------------------------------------

Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');

select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;

select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type; 

NOTE:
THE TARGET SCHEMA OBJECT COUNT SHOULD BE SAME AS THE SOURCE SCHEMA OBJECT COUNT CAPTURED

-----------------------------------------------------------------------------------------------------

Gather schema stats after schema refresh
----------------------------------------
set timing on
exec dbms_stats.gather_schema_stats(ownname=>'&schema_name', CASCADE=>TRUE,ESTIMATE_PERCENT=>30,degree =>4);

-----------------------------------------------------------------------------------------------------
NOTE: ONCE TASK COMPLETED AT PRODUCTION SERVER IMMEDIATLY DO EXIT.