TABLE REFRESH steps in ORACLE

Steps for TABLE REFRESH:-
-----------------------
1. Capture the SOURCE database TABLE ROW COUNT which are to be refreshed.
2.Take EXPORT of required TABLE(s) at SOURCE Database.
3. Copy the EXPORT DUMPFILE(s) to DESTINATION Database Server.(SCP)
4. Take EXPORT of required TABLE(s) at DESTINATION Database.(Recommended)
5. TRUNCATE the required TABLE(s) to be restored at DESTINATION Database.(Recommended)

6. IMPORT the Copied SOURCE EXPORT DUMPFILE(s) to the DESTINATION Database.
7. THE TARGET TABLE ROW COUNT SHOULD BE SAME AS THE SOURCE TABLE ROW COUNT CAPTURED.

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

DESTINATION
------
Hostname       :
Database Name  :
Table(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

Check for the space availability for taking backup
--------------------------------------------------
df -h or df -gt
df -h /source/backup/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
 
Schema Status
-------------
select username from dba_users where username=upper('&username');

Table Status
------------
select count(*) from SCHEMANAME.TABLENAME;

EXPDP
-----
col DIRECTORY_PATH for a80;
select * from dba_directories;

create or replace directory DATAPUMP_DIR as '/source/backup/location/';
grant read,write ON DIRECTORY DATAPUMP_DIR 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_TABLENAME_EXPDP.par

userid="/ as sysdba"
directory=DATAPUMP_DIR
dumpfile=SOURCE_TABLENAME_EXPDP_%U.dmp
logfile=SOURCE_TABLENAME_EXPDP.log
tables=SOURCE_SCHEMANAME.TABLENAME1,SOURCE_SCHEMANAME.TABLENAME2
JOB_NAME=SOURCE_TABLE_EXPORT
parallel=6
COMPRESSION=ALL 
STATUS=60
EXCLUDE=CONSTRAINT,INDEX,TRIGGER

filesize=4G

:wq

nohup expdp  parfile=SOURCE_TABLENAME_EXPDP.par &

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


EXP
---
If the size is less better to go for EXP/IMP
nohup exp userid=\'/ as sysdba \' file=SOURCE_TABLENAME_EXP.dmp log=SOURCE_TABLENAME_EXP.log tables=OWNER.TABLENAME &
-----------------------------------------------------------------------------------------------------
SCP SOURCE DUMP FILES TO DESTINATION 
----------------------------------------------------------
@ Source
--------
scp source_tablename_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_tablename_expdp*.dmp .

pwd
/target/backup/location/EXPORT_REF_NO/
ls -lrth source_tablename_expdp*.dmp

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

@ 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

Check for the space availability for taking backup
--------------------------------------------------
df -h
df -h /target/backup/location/

cd /target/backup/location/

mkdir EXPORT_REF_NO
chmod 777 EXPORT_REF_NO
cd EXPORT_REF_NO

pwd
/target/backup/location/EXPORT_REF_NO

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

Table Status
------------
select count(*) from SCHEMANAME.TABLENAME;

EXPDP
-----
col DIRECTORY_PATH for a80;
select * from dba_directories;

create or replace directory DATAPUMP_DIR as '/target/backup/location/EXPORT_REF_NO/';
grant read,write ON DIRECTORY DATAPUMP_DIR to system;

cd /target/backup/location/EXPORT_REF_NO/
ls -lrth SOURCE_TABLENAME_EXP_%U.dmp --------> scp files are here

-----------------------------------------------------------------------------------------------------------
@ Target
--------

TAKE BACKUP (EXPORT) OF TABLES BEING RESTORED AT DESTINATION DATABASE  

PAR file
--------
vi SOURCE_TABLENAME_EXPDP.par -----> same as above PAR file at SOURCE DATABASE
:wq

nohup expdp  parfile=SOURCE_TABLENAME_EXPDP.par &

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

EXP
---
If the size is less better to go for EXP/IMP
nohup exp userid=\'/ as sysdba \' file=TARGET_TABLENAME_EXP.dmp log=TARGET_TABLENAME_EXP.log tables=OWNER.TABLENAME &


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

@ Target
--------

PAR file
--------
vi IMPORT_SOURCE_TABLENAME_EXP.par

userid="/ as sysdba"
directory=DATAPUMP_DIR
dumpfile=SOURCE_TABLENAME_EXPDP_%U.dmp  logfile=IMPORT_SOURCE_TABLENAME_EXP.log
tables=SOURCE_SCHEMANAME.TABLENAME1, SOURCE_SCHEMANAME.TABLENAME2
SCHEMAS=SOURCE_SCHEMANAME1,SOURCE__SCHEMANAME2
JOB_NAME=SOURCE_TABLE_IMPORT
PARALLEL=6

IF REQUIRED:
-----------
TABLE_EXISTS_ACTION=TRUNCATE
REMAP_SCHEMA=SOURCE_SCHEMANAME1:DESTINATION_SCHEMANAME1,
SOURCE_SCHEMANAME2:DESTINATION_SCHEMANAME2
REMAP_TABLESPACE=SOURCE_SCHEMA_TABLESPACE1: DESTINATION_SCHEMA_TABLESPACE1,
SOURCE_SCHEMA_TABLESPACE2: DESTINATION_SCHEMA_TABLESPACE2
EXCLUDE=CONSTRAINT,REF_CONSTRAINT,INDEX
EXCLUDE=TABLE:in('<TABLE_NAME1>','<TABLE_NAME2>')
:wq

nohup impdp parfile = IMPORT_SOURCE_TABLENAME_EXP.par &


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


Validation of Import of tables
------------------------------
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 object_name in ('&object_name') and owner='&owner' order by object_type, object_name desc
/

NOTE:
THE TARGET TABLE ROW COUNT SHOULD BE SAME AS THE SOURCE TABLE ROW COUNT CAPTURED

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