database refresh by using oracle copy command .

UAT-REFRESH

UAT  COPY AND CLEAN
------------------------------
TAKE LIST OUT ALL THE TABLE AND RELATED SEQUENCE-
FOR SEQUENCE-RELATED TO TABLE.
>SELECT SEQUENCE_NAME FROM USER_SEQUENCES WHERE SEQUENCE_NAME LIKE’%TAB_NAM%’;

select type, name, line, text
from all_source
where owner = 'MYSCHEMA'
and upper(text) like '%MYSEQ.NEXTVAL%';

select ut.table_name
       , ud.referenced_name as sequence_name
from   user_dependencies ud
       join user_triggers ut on (ut.trigger_name = ud.name)
where ud.type='TRIGGER'
and ud.referenced_type='SEQUENCE'


select trigger_body
from user_triggers
where triggering_event = 'INSERT'  /* OR USE IN (‘INSERT’,’UPDATE’,’DELETE’)
and table_name = 'YOUR_TABLE';



1-TAKE SEQUENCE BACKUP
--SET PAGE SEZE
--SET COL SIZE
SELECT DBMS_METADATA.GET_DDL(‘SEQUENCE’,TRIM(‘SEQ_NAME’)) TXT FROM DUAL;

2-DROP SEQUENCE  SQL>DROP SEQUENCE SEQ_NAME;

3-TAKE TABLE BACKUP(UAT)
CREATE TABLE TAB_NAME_BKUP SELECT * FROM TAB_NAME;
4-DIS-ABLE ALL THE TABLE REFERENCIAL CONSTRAINTS ELSE WE CANT TRUNCATE TABLES WHICH HAVE ‘R’ CONSTRAINTS

SELECT * FROM  USER_CONSTRAINTS
WHERE
R_CONSTRAINT_NAME IN
(SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME=’TAB_NAME’);

--AND DISABLE THE CONSTRAINT
ALTER TABLE TABLE_NAME_REF  DISABLE CONSTRAINT CONSTRAINT_REF_NAME ;

5—TRUNCATE CONFIGURATION TABLE, MASTER TABLE AND CHILD TABLE THEN

TRUNCATE TABLE TAB_NAME;

6- AFTER TABLE TRUNCATE IMMEDIATELY ENABLE THE CONSTRAINTS.

ALTER TABLE TABLE_NAME_REF  ENABLE CONSTRAINT CONSTRAINT_REF_NAME ;


7-CHECK ALL TABLES TRUNCATE PROPERLY—SELECT COUNT(*) FROM TAB_NAM;

8-BECAUSE OF SEQUENCE DROP  , RELATED  TABLE TRIGGERS BECOMES DISABLES . AND THIS DISABLE TRIGGERS NOT ALLOW TO INSERT DATA IN FEATURE . SO  DISABLE ALL TRIGGERS FOR YOUR TABLES.

SQL>ALTER TABLE TAB_NAM DISABLE ALL TRIGGERS;

PROD DATA COPY
------------------------------------
NOW OUR UAT TABLE  IS EMPTY AND SEQ IS DROPPED .WE HAVE TO RUN COPY COMMAND FROM UAT. SO BEFORE RUNNING THIS COMMAND YOU SHOULD BE SURE ORACLE CIENT IS INSTALLED IN YOUR MACHINE AND YOU HAVE CONFIGURED TNSNAMES.ORA FILE FOR YOUR UAT AND PROD (DR) SERVERS.

TNSPING SID_NAME ( FROM COMMAND PROMPT)

IF EVERY THING FINE THE  USE COPPY COMMAND  IN UAT ONLY .

NOTE- IMPORTANT WHILE LOADING OR COPYING DATA , REMEMBER YOU CAN LOAD CONFIGURATION TABLE FIRST THEN MASTER TABLES AND THEN DETAILS TABLE. SO IT WILL NOT RAISE ANY CONSTRAINT ERROR.

SQL>COPY FROM  USER_NAME/PASS_WORD@SID_uat_NAME TO USER_NAME/PASS_WORD@SID_PROD_NAME
INSERT TAB_NAM USING SELECT * FROM TAB_NAM;

CHECK IN PROD AND UAT NO OF ROWS AND DATA ARE SAME .>SELECT COUNT(*) FROM TAB_NAM;

NOW LOGIN TO PROD OR DR TAKE COPY OF SEQUENCE DDL AND SAVE IN A FILE.

SELECT DBMS_METADATA.GET_DDL(‘SEQUENCE’,TRIM(‘SEQ_NAME’)) TXT FROM DUAL;
-    WE HAVE TO KEEP SEQ CURRENT VALUE IN UAT SAME AS PROD SO

-    NOW RUN THIS SEQUENCE DDL IN UAT TO SYNCRONISE THE SEQUNCE TOO.


POST-REFRESH

NOW TIME  TO ENABLE TRIGGER TRIGGERS FOR EACH TABLE;

SQL>ALTER TABLE TAB_NAM ENABLE ALL TRIGGERS;

NOW RECOMPILE ALL UAT PROCEDURE,FUNCTION,PACKAGE AND TRIGGER OR TYPES.

SQL>ALTER TRIGGER myTrigger COMPILE

ALTER PACKAGE emp_mgmt
   COMPILE PACKAGE;
ALTER PACKAGE hr.emp_mgmt
   COMPILE BODY;
CHECK FROM FRONT-END FOR UAT AND PROD IF RECORDS ARE COMING FINE ..