dbLinkScript.sh

#!/bin/bash

export ORACLE_HOME=/home/u01/app/oracle/product/12.1.0/dbhome_1;
export PATH=$ORACLE_HOME/bin:/usr/bin:/usr/local/bin:/bin;


function SyncPrimarySecondaryDBAll() {
    ######## Run time variables for Destination
    PRIMARY_SCHEMA=$1	
    SECONDARY_SCHEMA=$2
    Destination_Username=$3
    Destination_Password=$4
    Destination_Env=$5
    SourceSID=$6
    DestinationSID=$7
    SourceIP=$8
    DestinationIP=$9
    DB_Link=${10}

    METADATA_PATH=/home/oracle/$Destination_Env/automation
    VAR_DBlog=$METADATA_PATH/SyncPrimarySecondaryDBAll_Logs.txt
    TABLE_DIFF_SUMMARY=$METADATA_PATH/table_diff_summary.txt
    TABLE_DIFF_TABLE=$METADATA_PATH/table_diff_tables.txt
    TABLE_DIFF_PROC=$METADATA_PATH/table_diff_procs.txt
    TABLE_DIFF_TEMP=$METADATA_PATH/temp.txt
    TABLE_DIFF_TEMP1=$METADATA_PATH/temp1.txt
    TEMP_SQL_FILE=$METADATA_PATH/temp.sql
    TEMP_SQL_FILE1=$METADATA_PATH/temp1.sql
    FINAL_SQL_TABLE=$METADATA_PATH/final_sql_table.sql
    MODIFIED_TABLE=$METADATA_PATH/modified.sql
    ADDED_TABLE=$METADATA_PATH/added.sql
    DROPPED_TABLE=$METADATA_PATH/dropped.sql
    DROP_TABLE_LIST=$METADATA_PATH/drop_table_list.sql

    ############### Variables for Packages, Procedure, Functions, Constraints ######### 
    METADATA_PATH2=/home/oracle/$Destination_Env/automation2
    INDEX_DIFF_TEMP=$METADATA_PATH2/temp.sql
    INDEX_DIFF_TEMP1=$METADATA_PATH2/temp1.sql
    FILE_PACKAGE=$METADATA_PATH2/package.sql
    FILE_PACKAGE_BODY=$METADATA_PATH2/package_body.sql
    FILE_PROC=$METADATA_PATH2/procedure.sql
    FILE_FUNC=$METADATA_PATH2/function.sql
    FILE_TRIGGER=$METADATA_PATH2/trigger.sql
    FILE_SEQUENCE=$METADATA_PATH2/sequence_diff.sql
    FILE_VIEW=$METADATA_PATH2/view_diff.sql
    FILE_MATE_VIEW=$METADATA_PATH2/mate_view.sql
    FILE_DROP_MATE_VIEW=$METADATA_PATH2/drop_mate_view.sql
    FILE_INDEX=$METADATA_PATH2/index_diff.sql
    FILE_DATA_TYPE=$METADATA_PATH2/data_type.sql
    FILE_CONS_P=$METADATA_PATH2/primary.sql
    FILE_CONS_F=$METADATA_PATH2/reference.sql
    FILE_CONS_P_FOR_READ=$METADATA_PATH2/primary_for_read.sql
    FILE_CONS_F_FOR_READ=$METADATA_PATH2/reference_for_read.sql
    FILE_INDEX_FOR_READ=$METADATA_PATH2/index_diff_for_read.sql
    TEMP_FILE_CONS_P_FOR_READ=$METADATA_PATH2/temp_primary_for_read.sql
    TEMP_FILE_CONS_F_FOR_READ=$METADATA_PATH2/temp_reference_for_read.sql
    TEMP_FILE_INDEX_FOR_READ=$METADATA_PATH2/temp_index_diff_for_read.sql


    var_Output_File=$METADATA_PATH2/output.txt


    USERNAME=sys
    PASS=Spice_123

    >$VAR_DBlog
    >$FILE_PACKAGE
>$FILE_PACKAGE_BODY
>$FILE_PROC
>$FILE_FUNC
>$FILE_TRIGGER
>$FILE_SEQUENCE
>$FILE_VIEW
>$FILE_MATE_VIEW
>$FILE_INDEX
>$FILE_DATA_TYPE
>$FILE_CONS_P
>$FILE_CONS_F
>$FILE_CONS_P_FOR_READ
>$FILE_CONS_F_FOR_READ
>$FILE_INDEX_FOR_READ
>$var_Output_File
>$TEMP_FILE_CONS_F_FOR_READ
>$TEMP_FILE_CONS_P_FOR_READ
>$TEMP_FILE_INDEX_FOR_READ
>$DROP_TABLE_LIST
>$FILE_DROP_MATE_VIEW

####### Killing Locked DB Objects ##########
sh /home/oracle/killLockedDBobjects.sh $DestinationIP $DestinationSID

########## Take Backup of Database ############
#sh /home/oracle/backupDB.sh $Destination_Username

/usr/local/bin/sqlplus -s $USERNAME/$PASS@$SourceIP/$SourceSID as sysdba <<EOF
whenever sqlerror exit sql.sqlcode;
set echo off
set heading off
set linesize 2000
set lin 2000
set pagesize 0
set long 100000
column HEAD format A2000

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true);

spool '$TEMP_FILE_CONS_P_FOR_READ'
select dbms_metadata.get_ddl( 'CONSTRAINT', constraint_name,'$SECONDARY_SCHEMA') AS HEAD from
(
    select constraint_name from
        (
           SELECT constraint_name, constraint_type, table_name, status FROM all_constraints@$DB_Link where owner='$SECONDARY_SCHEMA'
           and constraint_type = 'P' AND table_name like 'T_%'
           MINUS
           SELECT constraint_name, constraint_type, table_name, status FROM all_constraints where owner='$PRIMARY_SCHEMA'
           and constraint_type = 'P' AND table_name like 'T_%'
        )
);
spool off;


spool '$TEMP_FILE_CONS_F_FOR_READ'
SELECT  dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', table_name,'$SECONDARY_SCHEMA') AS HEAD
FROM
  (
    SELECT DISTINCT table_name
    FROM all_constraints t
    WHERE table_name IN (select TABLE_NAME from ALL_TABLES@$DB_Link WHERE OWNER = '$SECONDARY_SCHEMA')
    AND OWNER = '$SECONDARY_SCHEMA' 
    AND EXISTS (SELECT 1 FROM all_constraints@$DB_Link WHERE table_name = t.table_name AND OWNER = '$SECONDARY_SCHEMA' AND constraint_type = 'R')
MINUS
    SELECT DISTINCT table_name FROM all_constraints t
    WHERE table_name IN (select TABLE_NAME from ALL_TABLES WHERE OWNER = '$PRIMARY_SCHEMA')
    AND OWNER = '$PRIMARY_SCHEMA'
    AND EXISTS (SELECT 1 FROM all_constraints WHERE table_name = t.table_name AND OWNER = '$PRIMARY_SCHEMA' AND constraint_type = 'R')
  );
spool off;


spool '$TEMP_FILE_INDEX_FOR_READ'
SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type, ' ', '_'), object_name, owner) AS HEAD
FROM all_OBJECTS@$DB_Link WHERE object_name IN
(
        select object_name from all_objects@$DB_Link where owner='$SECONDARY_SCHEMA'
        MINUS
        select object_name from all_objects where owner='$PRIMARY_SCHEMA'
)
AND object_type IN ('INDEX') AND owner='$SECONDARY_SCHEMA';

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', false);

spool off;
exit 0;
EOF

cat $TEMP_FILE_CONS_P_FOR_READ | grep -v "^$" | tr -s " " " " | grep -v "rows selected" | grep -v "^ $" >$FILE_CONS_P_FOR_READ
cat $TEMP_FILE_CONS_F_FOR_READ | grep -v "^$" | tr -s " " " " | grep -v "rows selected" | grep -v "^ $" >$FILE_CONS_F_FOR_READ
cat $TEMP_FILE_INDEX_FOR_READ | grep -v "^$" | tr -s " " " " | grep -v "rows selected" | grep -v "^ $" >$FILE_INDEX_FOR_READ

sed -i 's/ADD CONSTRAINT/DROP CONSTRAINT/g' $FILE_CONS_P_FOR_READ
sed -i 's/ADD CONSTRAINT/DROP CONSTRAINT/g' $FILE_CONS_F_FOR_READ
sed -i 's/CREATE/DROP/g' $FILE_INDEX_FOR_READ


echo "### Primary Constraints present at destination, Missing at Primary ###" >> $var_Output_File
cat $FILE_CONS_P_FOR_READ >> $var_Output_File

echo "### Foreign Constraints present at destination, Missing at Primary ###" >> $var_Output_File
cat $FILE_CONS_F_FOR_READ >> $var_Output_File

echo "### Indexes present at destination, Missing at Primary ###" >> $var_Output_File
cat $FILE_INDEX_FOR_READ >> $var_Output_File



######### START - TO SHOW TABLE CHANGE SUMMARY ##############

/usr/local/bin/sqlplus -s <<EOF> $TABLE_DIFF_TEMP $USERNAME/$PASS@$SourceIP/$SourceSID as sysdba
whenever sqlerror exit sql.sqlcode;
set echo off
set heading off
set linesize 2000
set lin 2000
set pagesize 0
set long 100000
column HEAD format A2000

spool '$ADDED_TABLE'

SELECT TABLE_NAME FROM all_tables WHERE OWNER = '$PRIMARY_SCHEMA' 
minus
SELECT TABLE_NAME FROM all_tables@$DB_Link WHERE OWNER = '$SECONDARY_SCHEMA'
minus
SELECT object_name FROM all_objects WHERE  object_type IN ('MATERIALIZED VIEW') AND OWNER='$PRIMARY_SCHEMA';

spool off;

spool '$DROPPED_TABLE'

select TABLE_NAME from all_tables@$DB_Link where owner='$SECONDARY_SCHEMA'
minus
select TABLE_NAME from all_tables where owner='$PRIMARY_SCHEMA';

spool off;

spool '$MODIFIED_TABLE'

col table_name format a50
col column_name format a50
col data_type format a50

(SELECT table_name,column_name,data_type,data_length FROM all_tab_columns@$DB_Link WHERE owner='$SECONDARY_SCHEMA' AND table_name IN (select object_name from all_objects@$DB_Link where object_type in 'TABLE' minus
SELECT object_name FROM all_objects@$DB_Link WHERE  object_type IN ('MATERIALIZED VIEW')) 
MINUS
SELECT table_name,column_name,data_type,data_length FROM all_tab_columns WHERE owner='$PRIMARY_SCHEMA' AND table_name IN (select object_name from all_objects where object_type in 'TABLE' minus
SELECT object_name FROM all_objects WHERE  object_type IN ('MATERIALIZED VIEW')))
UNION
(SELECT table_name,column_name,data_type,data_length FROM all_tab_columns WHERE owner='$PRIMARY_SCHEMA' AND table_name IN (select object_name from all_objects where object_type in 'TABLE' minus
 SELECT object_name FROM all_objects WHERE  object_type IN ('MATERIALIZED VIEW'))
MINUS
SELECT table_name,column_name,data_type,data_length FROM all_tab_columns@$DB_Link WHERE owner='$SECONDARY_SCHEMA' AND table_name IN (select object_name from all_objects@$DB_Link where object_type in 'TABLE' minus
 SELECT object_name FROM all_objects@$DB_Link WHERE  object_type IN ('MATERIALIZED VIEW')));

spool off;

exit 1;
EOF
cat $ADDED_TABLE |grep -v "^$" | tr -s " " " " | grep -v "rows selected" | grep -v "^ $" | sed 's/$/\t\t\t\t\t\t\tADDED/g'  >$TABLE_DIFF_TABLE
cat $DROPPED_TABLE |grep -v "^$" | tr -s " " " " | grep -v "rows selected" | grep -v "^ $" | sed 's/$/\t\t\t\t\t\t\tDROPPED/g'  >>$TABLE_DIFF_TABLE
cat $MODIFIED_TABLE | grep -v "^$" |tr -s " " " " | grep -v "rows selected" |awk -F " " '{print $1"\t\t\t\t\t\t\tMODIFIED"}' | sort -u >>$TABLE_DIFF_TABLE

######### START - PREPARE CONSOLIDATED SHEET ##############
cat $TABLE_DIFF_TABLE | awk -F " " '{print $1}' |sort -u >$TABLE_DIFF_TEMP

>$TABLE_DIFF_TEMP1
cat $TABLE_DIFF_TEMP | while read line
do
        cat $TABLE_DIFF_TABLE | grep -w "$line" | sort | head -1 >>$TABLE_DIFF_TEMP1
done
mv $TABLE_DIFF_TEMP1 $TABLE_DIFF_TABLE
######### END - PREPARE CONSOLIDATED SHEET ##############
cat $TABLE_DIFF_TABLE
######### END - TO SHOW CHANGE TABLE SUMMARY ##############


######### START - TO CREATE SQL FOR ADDED TABLES ##############
>$FINAL_SQL_TABLE
>$TEMP_SQL_FILE
cat $TABLE_DIFF_TABLE | grep "ADDED$" | while read line
do
        echo $line |awk -F " " '{print "SELECT DBMS_METADATA.GET_DDL(\x27TABLE\x27,\x27"$1"\x27, \x27""'"${PRIMARY_SCHEMA}"'""\x27) as HEAD from dual;"}' >>$TEMP_SQL_FILE
done
########### START - CREATE SQL FOR ADDED TABLE ##########
>$TABLE_DIFF_TEMP1
/usr/local/bin/sqlplus -s <<EOF> $TABLE_DIFF_TEMP $USERNAME/$PASS@$SourceIP/$SourceSID as sysdba
whenever sqlerror exit sql.sqlcode;
set echo off
set heading off
set lin 200
set pagesize 200
set long 100000
column HEAD format A2000

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true);

spool '$TABLE_DIFF_TEMP1'
@'$TEMP_SQL_FILE'
spool off

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS',true);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',true);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', false);


exit 1;
EOF
echo "-- These tables need to be created in $SECONDARY_SCHEMA" >>$FINAL_SQL_TABLE
cat $TABLE_DIFF_TEMP1 | grep -v "^$" | tr -s " " " " |  sed -e 's/"'$PRIMARY_SCHEMA'"/"'$SECONDARY_SCHEMA'"/g' >>$FINAL_SQL_TABLE
echo " "  >>$FINAL_SQL_TABLE
######### END - TO CREATE SQL FOR ADDED TABLES ##############

######### START - TO CREATE SQL FOR MODIFIED TABLES ##############
>$TEMP_SQL_FILE
cat $TABLE_DIFF_TABLE | grep "MODIFIED$" | while read line
do
	echo $line
        echo $line |awk -F " " '{print "select dbms_metadata_diff.compare_alter( \x27TABLE\x27, \x27"$1"\x27, \x27"$1"\x27, \x27""'"$SECONDARY_SCHEMA"'""\x27, \x27""'"$PRIMARY_SCHEMA"'""\x27, \x27""'"$DB_Link"'""\x27, NULL ) as HEAD from dual;"}' >>$TEMP_SQL_FILE
done
################################################################# New For Script 2 ##############################################################################

>$TABLE_DIFF_TEMP1
/usr/local/bin/sqlplus -s <<EOF> $TABLE_DIFF_TEMP $USERNAME/$PASS@$SourceIP/$SourceSID as sysdba
whenever sqlerror exit sql.sqlcode;
set echo off
set heading off
set lin 20000
set pagesize 20000
set long 100000
column HEAD format A2000

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true);

spool '$TABLE_DIFF_TEMP1'
@'$TEMP_SQL_FILE'
spool off

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS',true);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',true);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', false);

exit 1;
EOF
echo "-- These column needs to be modified in $SECONDARY_SCHEMA" >>$FINAL_SQL_TABLE
cat $TABLE_DIFF_TEMP1 | tr -s " " " " | grep -v "^ $" |  grep -v "^$" | sed -e 's/"'$PRIMARY_SCHEMA'"/"'$SECONDARY_SCHEMA'"/g' | sed -e 's/$/;/g' >>$FINAL_SQL_TABLE
echo " " >>$FINAL_SQL_TABLE
######### END - TO CREATE SQL FOR MODIFIED TABLES ##############

######### START - TO CREATE SQL FOR DROPPED TABLES ##############
echo "-- These table need to be dropped in $SECONDARY_SCHEMA" >>$DROP_TABLE_LIST
cat $TABLE_DIFF_TABLE | grep "DROPPED$" | while read line
do
        echo $line |awk -F " " '{print "drop table '"$SECONDARY_SCHEMA"'."$1";"}' >>$DROP_TABLE_LIST
done
echo " " >>$DROP_TABLE_LIST
######### END - TO CREATE SQL FOR DROPPED TABLES ##############


######### Generating Packages in PRIMARY_SCHEMA ##############
/usr/local/bin/sqlplus -s <<EOF> $INDEX_DIFF_TEMP1 $USERNAME/$PASS@$SourceIP/$SourceSID as sysdba
#whenever sqlerror exit sql.sqlcode;
set echo off
set heading off
set lin 4000
set linesize 4000
set pagesize 0
set long 1000000
column HEAD format A2000


exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true);

spool '$FILE_VIEW'
SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type, ' ', '_'), object_name, owner) AS HEAD
FROM all_OBJECTS WHERE object_name IN
(
        select object_name from all_objects where owner='$PRIMARY_SCHEMA'
)
AND object_type IN ('VIEW') AND owner='$PRIMARY_SCHEMA' order by LAST_DDL_TIME;

spool off;

spool '$FILE_MATE_VIEW'
SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type, ' ', '_'), object_name, owner) AS HEAD
FROM ALL_OBJECTS WHERE object_name IN
(
        select object_name from all_objects where owner='$PRIMARY_SCHEMA'
)
AND object_type IN ('MATERIALIZED VIEW') AND owner='$PRIMARY_SCHEMA';

spool off;

spool '$FILE_INDEX'
SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type, ' ', '_'), object_name, owner) AS HEAD
FROM all_OBJECTS WHERE object_name IN
(
        select object_name from all_objects where owner='$PRIMARY_SCHEMA'
        MINUS
        select object_name from all_objects@$DB_Link where owner='$SECONDARY_SCHEMA'
)
AND object_type IN ('INDEX') AND owner='$PRIMARY_SCHEMA';

spool off;

spool '$FILE_PACKAGE'
SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type, ' ', '_'), object_name, owner) AS HEAD
FROM all_OBJECTS WHERE object_name IN
(
        select object_name from all_objects where owner='$PRIMARY_SCHEMA'
)
AND object_type IN ('PACKAGE') AND owner='$PRIMARY_SCHEMA';

spool off;

spool '$FILE_PACKAGE_BODY'
SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type, ' ', '_'), object_name, owner) AS HEAD
FROM all_OBJECTS WHERE object_name IN
(
	select object_name from all_objects where owner='$PRIMARY_SCHEMA'
)
AND object_type IN ('PACKAGE BODY') AND owner='$PRIMARY_SCHEMA';

spool off;

spool '$FILE_FUNC'
SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type, ' ', '_'), object_name, owner) AS HEAD
FROM all_OBJECTS WHERE object_name IN
(
        select object_name from all_objects where owner='$PRIMARY_SCHEMA'
)
AND object_type IN ('FUNCTION') AND owner='$PRIMARY_SCHEMA';

spool off;

spool '$FILE_SEQUENCE'
SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type, ' ', '_'), object_name, owner) AS HEAD
FROM all_OBJECTS WHERE object_name IN
(
        select object_name from all_objects where owner='$PRIMARY_SCHEMA'
        MINUS
        select object_name from all_objects@$DB_Link where owner='$SECONDARY_SCHEMA'
)
AND object_type IN ('SEQUENCE') AND owner='$PRIMARY_SCHEMA';

spool off;

spool '$FILE_PROC'
SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type, ' ', '_'), object_name, owner) AS HEAD
FROM all_OBJECTS WHERE object_name IN
(
        select object_name from all_objects where owner='$PRIMARY_SCHEMA'
)
AND object_type IN ('PROCEDURE') and owner='$PRIMARY_SCHEMA';

spool off;

spool '$FILE_TRIGGER'
SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type, ' ', '_'), object_name, owner) AS HEAD
FROM all_OBJECTS WHERE object_name IN
(
        select object_name from all_objects where owner='$PRIMARY_SCHEMA'
)
AND object_type IN ('TRIGGER') AND owner='$PRIMARY_SCHEMA';

spool off;

spool '$FILE_DATA_TYPE'
SELECT dbms_metadata_diff.compare_alter( 'TABLE',table_name,table_name,'$SECONDARY_SCHEMA', '$PRIMARY_SCHEMA', \x27""'"$DB_Link"'""\x27, NULL )
from
 (
 SELECT table_name,column_name,data_type
  FROM all_tab_columns
  WHERE owner='$PRIMARY_SCHEMA'
  AND table_name IN (select object_name from all_objects where object_type in 'TABLE'  AND owner='$PRIMARY_SCHEMA')
 MINUS
 SELECT table_name,column_name,data_type
  FROM all_tab_columns@$DB_Link
  WHERE owner='$SECONDARY_SCHEMA'
  AND table_name IN (select object_name from all_objects@$DB_Link where object_type in 'TABLE'  AND owner='$SECONDARY_SCHEMA')
 );

spool off;

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS',true);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',true);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', false);
exit 1;
EOF

cat $FILE_VIEW | grep -v "^$" | tr -s " " " " | grep -v "rows selected" | grep -v "^ $" | sed 's/"'$PRIMARY_SCHEMA'"/"'$SECONDARY_SCHEMA'"/g' >$INDEX_DIFF_TEMP
mv $INDEX_DIFF_TEMP $FILE_VIEW
cat $FILE_MATE_VIEW | grep -v "^$" | tr -s " " " " | grep -v "rows selected" | grep -v "^ $" | sed 's/"'$PRIMARY_SCHEMA'"/"'$SECONDARY_SCHEMA'"/g' >$INDEX_DIFF_TEMP
mv $INDEX_DIFF_TEMP $FILE_MATE_VIEW
cat $FILE_INDEX | grep -v "^$" | tr -s " " " " | grep -v "rows selected" | grep -v "^ $" | sed 's/"'$PRIMARY_SCHEMA'"/"'$SECONDARY_SCHEMA'"/g' >$INDEX_DIFF_TEMP
mv $INDEX_DIFF_TEMP $FILE_INDEX
cat $FILE_PACKAGE | grep -v "^$" | tr -s " " " " | grep -v "rows selected" | grep -v "^ $" | sed 's/"'$PRIMARY_SCHEMA'"/"'$SECONDARY_SCHEMA'"/g' >$INDEX_DIFF_TEMP
mv $INDEX_DIFF_TEMP $FILE_PACKAGE
cat $FILE_PACKAGE_BODY | grep -v "^$" | tr -s " " " " | grep -v "rows selected" | grep -v "^ $" | sed 's/"'$PRIMARY_SCHEMA'"/"'$SECONDARY_SCHEMA'"/g' >$INDEX_DIFF_TEMP
mv $INDEX_DIFF_TEMP $FILE_PACKAGE_BODY
cat $FILE_PROC | grep -v "^$" | tr -s " " " " | grep -v "rows selected" | grep -v "^ $" | sed 's/"'$PRIMARY_SCHEMA'"/"'$SECONDARY_SCHEMA'"/g' >$INDEX_DIFF_TEMP
mv $INDEX_DIFF_TEMP $FILE_PROC
cat $FILE_FUNC | grep -v "^$" | tr -s " " " " | grep -v "rows selected" | grep -v "^ $" | sed 's/"'$PRIMARY_SCHEMA'"/"'$SECONDARY_SCHEMA'"/g' >$INDEX_DIFF_TEMP
mv $INDEX_DIFF_TEMP $FILE_FUNC
cat $FILE_TRIGGER | grep -v "^$" | tr -s " " " " | grep -v "rows selected" | grep -v "^ $" | sed 's/"'$PRIMARY_SCHEMA'"/"'$SECONDARY_SCHEMA'"/g' >$INDEX_DIFF_TEMP
mv $INDEX_DIFF_TEMP $FILE_TRIGGER
cat $FILE_SEQUENCE | grep -v "^$" | tr -s " " " " | grep -v "rows selected" | grep -v "^ $" | sed 's/"'$PRIMARY_SCHEMA'"/"'$SECONDARY_SCHEMA'"/g' >$INDEX_DIFF_TEMP
mv $INDEX_DIFF_TEMP $FILE_SEQUENCE
cat $FILE_DATA_TYPE | grep -v "^$" | tr -s " " " " | grep -v "rows selected" | grep -v "^ $" | sed 's/"'$PRIMARY_SCHEMA'"/"'$SECONDARY_SCHEMA'"/g' >$INDEX_DIFF_TEMP
mv $INDEX_DIFF_TEMP $FILE_DATA_TYPE

######### Generating Constraints in SCHEMA ##############
/usr/local/bin/sqlplus -s <<EOF> $INDEX_DIFF_TEMP1 $USERNAME/$PASS@$SourceIP/$SourceSID as sysdba
set echo off
set heading off
set lin 200
set linesize 200
set pagesize 0
set long 1000000
column HEAD format A2000

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true);

spool '$FILE_CONS_P'
select dbms_metadata.get_ddl( 'CONSTRAINT', constraint_name,'$PRIMARY_SCHEMA') AS HEAD from
(
        select constraint_name from
        (
                SELECT constraint_name, constraint_type, table_name, status FROM all_constraints where owner='$PRIMARY_SCHEMA'
                and constraint_type = 'P' AND table_name like 'T_%'
                MINUS
                SELECT constraint_name, constraint_type, table_name, status FROM all_constraints@$DB_Link where owner='$SECONDARY_SCHEMA'
                and constraint_type = 'P' AND table_name like 'T_%'
                        )
);
spool off;

spool '$FILE_CONS_F'

SELECT  dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', table_name,'$PRIMARY_SCHEMA') AS HEAD
FROM
        (
                SELECT DISTINCT table_name
                FROM all_constraints t
                WHERE table_name IN (select TABLE_NAME from ALL_TABLES WHERE OWNER = '$PRIMARY_SCHEMA')
                AND OWNER = '$PRIMARY_SCHEMA'
                AND EXISTS (SELECT 1 FROM all_constraints WHERE table_name = t.table_name AND OWNER = '$PRIMARY_SCHEMA' AND constraint_type = 'R')
MINUS
                SELECT DISTINCT table_name FROM all_constraints@$DB_Link t
                WHERE table_name IN (select TABLE_NAME from ALL_TABLES@$DB_Link WHERE OWNER = '$SECONDARY_SCHEMA')
                AND OWNER = '$SECONDARY_SCHEMA'
                AND EXISTS (SELECT 1 FROM all_constraints@$DB_Link WHERE table_name = t.table_name AND OWNER = '$SECONDARY_SCHEMA' AND constraint_type = 'R')
    );
spool off;

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', false);

exit 1;
EOF

cat $FILE_CONS_P | grep -v "^$" | tr -s " " " " | grep -v "rows selected" | grep -v "^ $" | sed 's/"'$PRIMARY_SCHEMA'"/"'$SECONDARY_SCHEMA'"/g' >$INDEX_DIFF_TEMP
mv $INDEX_DIFF_TEMP $FILE_CONS_P
cat $FILE_CONS_F | grep -v "^$" | tr -s " " " " | grep -v "rows selected" | grep -v "^ $" | sed 's/"'$PRIMARY_SCHEMA'"/"'$SECONDARY_SCHEMA'"/g' >$INDEX_DIFF_TEMP
mv $INDEX_DIFF_TEMP $FILE_CONS_F

/usr/local/bin/sqlplus -s $USERNAME/$PASS@$DestinationIP/$DestinationSID as sysdba << EOF
spool '$VAR_DBlog'
@$FINAL_SQL_TABLE
@$FILE_VIEW
@$FILE_INDEX
@$FILE_SEQUENCE
@$FILE_PACKAGE
@$FILE_PACKAGE_BODY
@$FILE_PROC
@$FILE_FUNC
@$FILE_CONS_P
@$FILE_CONS_F
spool off;
exit 1;
EOF

/usr/local/bin/sqlplus -s $USERNAME/$PASS@$DestinationIP/$DestinationSID as sysdba << EOF
spool '$FILE_DROP_MATE_VIEW'
select 'DROP MATERIALIZED VIEW ''' || object_name || ''';' "MATERIALIZED VIEW" from all_objects where owner='$SECONDARY_SCHEMA' and object_type='MATERIALIZED VIEW';
spool off;
exit 1;
EOF

sed -i '1,3d' $FILE_DROP_MATE_VIEW
sed -i "s/'//g" $FILE_DROP_MATE_VIEW
sed -i '/selected/d' $FILE_DROP_MATE_VIEW

/usr/local/bin/sqlplus -s $Destination_Username/$Destination_Password@$DestinationIP/$DestinationSID << EOF
spool '$VAR_DBlog' append
@$FILE_DROP_MATE_VIEW
@$FILE_MATE_VIEW
@$FILE_TRIGGER
spool off;
exit 1;
EOF

/usr/local/bin/sqlplus -s $USERNAME/$PASS@$DestinationIP/$DestinationSID as sysdba << EOF
EXEC DBMS_UTILITY.compile_schema(schema => '$SECONDARY_SCHEMA');
exit 1;
EOF


echo "### Tables present at destination, Missing at Primary ###" >> $var_Output_File
cat $DROP_TABLE_LIST >> $var_Output_File

cat $var_Output_File

}


case $1 in 
"DEV2QA_ALL")
    SyncPrimarySecondaryDBAll  MFS MFS mfs spice#123 MFS_ALL_DEV2QA_MFS dev qa 192.168.2.4 192.168.2.4 DEV2QA_MFS
    SyncPrimarySecondaryDBAll  PAYPRO PAYPRO paypro spice#123 MFS_ALL_DEV2QA_PAYPRO dev qa 192.168.2.4 192.168.2.4 DEV2QA_PAYPRO
    SyncPrimarySecondaryDBAll  CAMP CAMP camp spice#123 MFS_ALL_DEV2QA_CAMP dev qa 192.168.2.4 192.168.2.4 DEV2QA_CAMP
    SyncPrimarySecondaryDBAll  ARCH ARCH arch spice#123 MFS_ALL_DEV2QA_ARCH dev qa 192.168.2.4 192.168.2.4 DEV2QA_ARCH
    SyncPrimarySecondaryDBAll  REPORT REPORT report spice#123 MFS_ALL_DEV2QA_REPORT dev qa 192.168.2.4 192.168.2.4 DEV2QA_REPORT
    SyncPrimarySecondaryDBAll  BILLERGW BILLERGW billergw spice#123 MFS_DEV2QA_BILLERGW dev qa 192.168.2.4 192.168.2.4 DEV2QA_BILLERGW
    if grep "ERROR" $VAR_DBlog
    then
     exit 1
    else
      exit 0
    fi

;;
"DEV2QA_MFS")
    SyncPrimarySecondaryDBAll  MFS MFS mfs spice#123 MFS_DEV2QA_MFS dev qa 192.168.2.4 192.168.2.4 DEV2QA_MFS
    if grep "ERROR" $VAR_DBlog
    then
     exit 1
    else
      exit 0
    fi
;;
"DEV2QA_PAYPRO")
    SyncPrimarySecondaryDBAll  PAYPRO PAYPRO paypro spice#123 MFS_DEV2QA_PAYPRO dev qa 192.168.2.4 192.168.2.4 DEV2QA_PAYPRO
    if grep "ERROR" $VAR_DBlog
    then
     exit 1
    else
      exit 0
    fi

;;
"DEV2QA_CAMP")
    SyncPrimarySecondaryDBAll  CAMP CAMP camp spice#123 MFS_DEV2QA_CAMP dev qa 192.168.2.4 192.168.2.4 DEV2QA_CAMP
    if grep "ERROR" $VAR_DBlog
    then
     exit 1
    else
      exit 0
    fi

;;
"DEV2QA_ARCH")
    SyncPrimarySecondaryDBAll  ARCH ARCH arch spice#123 MFS_DEV2QA_ARCH dev qa 192.168.2.4 192.168.2.4 DEV2QA_ARCH
    if grep "ERROR" $VAR_DBlog
    then
     exit 1
    else
      exit 0
    fi

;;
"DEV2QA_REPORT")
    SyncPrimarySecondaryDBAll  REPORT REPORT report spice#123 MFS_DEV2QA_REPORT dev qa 192.168.2.4 192.168.2.4 DEV2QA_REPORT
    if grep "ERROR" $VAR_DBlog
    then
     exit 1
    else
      exit 0
    fi

;;
"DEV2QA_BILLERGW")
   SyncPrimarySecondaryDBAll  BILLERGW BILLERGW billergw spice#123 MFS_DEV2QA_BILLERGW dev qa 192.168.2.4 192.168.2.4 DEV2QA_BILLERGW
;;
"TEST")
    SyncPrimarySecondaryDBAll  MFS MFS mfs spice#123 MFS_TEST dev qa 192.168.2.4 192.168.2.4 DEV2QA_MFS
;;
"QA2QASIT_ALL")
    SyncPrimarySecondaryDBAll  MFS MFS mfs spice#123 MFS_ALL_QA2QASIT_MFS qa qasit 192.168.2.4 192.168.2.5 QA2QASIT_MFS
    SyncPrimarySecondaryDBAll  PAYPRO PAYPRO paypro spice#123 MFS_ALL_QA2QASIT_PAYPRO qa qasit 192.168.2.4 192.168.2.5 QA2QASIT_PAYPRO
    SyncPrimarySecondaryDBAll  CAMP CAMP camp spice#123 MFS_ALL_QA2QASIT_CAMP qa qasit 192.168.2.4 192.168.2.5 QA2QASIT_CAMP
    SyncPrimarySecondaryDBAll  ARCH ARCH arch spice#123 MFS_ALL_QA2QASIT_ARCH qa qasit 192.168.2.4 192.168.2.5 QA2QASIT_ARCH
    SyncPrimarySecondaryDBAll  REPORT REPORT report spice#123 MFS_ALL_QA2QASIT_REPORT qa qasit 192.168.2.4 192.168.2.5 QA2QASIT_REPORT
    SyncPrimarySecondaryDBAll  BILLERGW BILLERGW billergw spice#123 MFS_ALL_QA2QASIT_BILLERGW qa qasit 192.168.2.4 192.168.2.5 QA2QASIT_BILLERGW
    if grep "ERROR" $VAR_DBlog
    then
     exit 1
    else
      exit 0
    fi
;;

"QASIT2SIT_ALL")
    SyncPrimarySecondaryDBAll  MFS MFS mfs spice#123 MFS_ALL_QASIT2SIT_MFS qasit sit 192.168.2.5 192.168.2.5 QASIT2SIT_MFS 
    SyncPrimarySecondaryDBAll  PAYPRO PAYPRO paypro spice#123 MFS_ALL_QASIT2SIT_PAYPRO qasit sit 192.168.2.5 192.168.2.5 QASIT2SIT_PAYPRO
    SyncPrimarySecondaryDBAll  CAMP CAMP camp spice#123 MFS_ALL_QASIT2SIT_CAMP qasit sit 192.168.2.5 192.168.2.5 QASIT2SIT_CAMP
    SyncPrimarySecondaryDBAll  ARCH ARCH arch spice#123 MFS_ALL_QASIT2SIT_ARCH qasit sit 192.168.2.5 192.168.2.5 QASIT2SIT_ARCH
    SyncPrimarySecondaryDBAll  REPORT REPORT report spice#123 MFS_ALL_QASIT2SIT_REPORT qasit sit 192.168.2.5 192.168.2.5 QASIT2SIT_REPORT
    SyncPrimarySecondaryDBAll  BILLERGW BILLERGW billergw spice#123 MFS_ALL_QASIT2SIT_BILLERGW qasit sit 192.168.2.5 192.168.2.5 QASIT2SIT_BILLERGW
    if grep "ERROR" $VAR_DBlog
    then
     exit 1
    else
      exit 0
    fi
;;
"SIT2DEMO_ALL")
    SyncPrimarySecondaryDBAll  MFS MFS mfs spice#123 MFS_ALL_SIT2DEMO_MFS sit demo 192.168.2.5 192.168.2.5 SIT2DEMO_MFS 
    SyncPrimarySecondaryDBAll  PAYPRO PAYPRO paypro spice#123 MFS_ALL_SIT2DEMO_PAYPRO sit demo 192.168.2.5 192.168.2.5 SIT2DEMO_PAYPRO
    SyncPrimarySecondaryDBAll  CAMP CAMP camp spice#123 MFS_ALL_SIT2DEMO_CAMP sit demo 192.168.2.5 192.168.2.5 SIT2DEMO_CAMP
    SyncPrimarySecondaryDBAll  ARCH ARCH arch spice#123 MFS_ALL_SIT2DEMO_ARCH sit demo 192.168.2.5 192.168.2.5 SIT2DEMO_ARCH
    SyncPrimarySecondaryDBAll  REPORT REPORT report spice#123 MFS_ALL_SIT2DEMO_REPORT sit demo 192.168.2.5 192.168.2.5 SIT2DEMO_REPORT
    SyncPrimarySecondaryDBAll  BILLERGW BILLERGW billergw spice#123 MFS_ALL_SIT2DEMO_BILLERGW sit demo 192.168.2.5 192.168.2.5 SIT2DEMO_BILLERGW
    if grep "ERROR" $VAR_DBlog
    then
     exit 1
    else
      exit 0
    fi
;;
"DEV2LIVE_ALL")
    SyncPrimarySecondaryDBAll  MFS MFS mfs spice#123 MFS_ALL_DEV2LIVE_MFS dev live 192.168.2.4 192.168.2.5 DEV2LIVE_MFS
    SyncPrimarySecondaryDBAll  PAYPRO PAYPRO paypro spice#123 MFS_ALL_DEV2LIVE_PAYPRO dev live 192.168.2.4 192.168.2.5 DEV2LIVE_PAYPRO
    SyncPrimarySecondaryDBAll  CAMP CAMP camp spice#123 MFS_ALL_DEV2LIVE_CAMP dev live 192.168.2.4 192.168.2.5 DEV2LIVE_CAMP
    SyncPrimarySecondaryDBAll  ARCH ARCH arch spice#123 MFS_ALL_DEV2LIVE_ARCH dev live 192.168.2.4 192.168.2.5 DEV2LIVE_ARCH
    SyncPrimarySecondaryDBAll  REPORT REPORT report spice#123 MFS_ALL_DEV2LIVE_REPORT dev live 192.168.2.4 192.168.2.5 DEV2LIVE_REPORT
    SyncPrimarySecondaryDBAll  BILLERGW BILLERGW billergw spice#123 MFS_ALL_DEV2LIVE_BILLERGW dev live 192.168.2.4 192.168.2.5 DEV2LIVE_BILLERGW
    if grep "ERROR" $VAR_DBlog
    then
     exit 1
    else
      exit 0
    fi

;;

"QASIT2DEMO_ALL")
  SyncPrimarySecondaryDBAll  MFS MFS mfs spice#123 MFS_ALL_QASIT2DEMO_MFS qasit demo 192.168.2.5 192.168.2.5 QASIT2DEMO_MFS
  SyncPrimarySecondaryDBAll  PAYPRO PAYPRO paypro spice#123 MFS_ALL_QASIT2DEMO_PAYPRO qasit demo 192.168.2.5 192.168.2.5 QASIT2DEMO_PAYPRO
  SyncPrimarySecondaryDBAll  CAMP CAMP camp spice#123 MFS_ALL_QASIT2DEMO_CAMP qasit demo 192.168.2.5 192.168.2.5 QASIT2DEMO_CAMP
  SyncPrimarySecondaryDBAll  ARCH ARCH arch spice#123 MFS_ALL_QASIT2DEMO_ARCH qasit demo 192.168.2.5 192.168.2.5 QASIT2DEMO_ARCH
  SyncPrimarySecondaryDBAll  REPORT REPORT report spice#123 MFS_ALL_QASIT2DEMO_REPORT qasit demo 192.168.2.5 192.168.2.5 QASIT2DEMO_REPORT
  SyncPrimarySecondaryDBAll  BILLERGW BILLERGW billergw spice#123 MFS_ALL_QASIT2DEMO_BILLERGW qasit demo 192.168.2.5 192.168.2.5 QASIT2DEMO_BILLERGW
    if grep "ERROR" $VAR_DBlog
    then
     exit 1
    else
      exit 0
    fi
;;

"DEMO2QASIT_ALL")
SyncPrimarySecondaryDBAll  MFS MFS mfs spice#123 MFS_ALL_DEMO2QASIT_MFS demo qasit 192.168.2.5 192.168.2.5 DEMO2QASIT_MFS
SyncPrimarySecondaryDBAll  PAYPRO PAYPRO paypro spice#123 MFS_ALL_DEMO2QASIT_PAYPRO demo qasit 192.168.2.5 192.168.2.5 DEMO2QASIT_PAYPRO
SyncPrimarySecondaryDBAll  CAMP CAMP camp spice#123 MFS_ALL_DEMO2QASIT_CAMP demo qasit 192.168.2.5 192.168.2.5 DEMO2QASIT_CAMP
SyncPrimarySecondaryDBAll  ARCH ARCH arch spice#123 MFS_ALL_DEMO2QASIT_ARCH demo qasit 192.168.2.5 192.168.2.5 DEMO2QASIT_ARCH
SyncPrimarySecondaryDBAll  REPORT REPORT report spice#123 MFS_ALL_DEMO2QASIT_REPORT demo qasit 192.168.2.5 192.168.2.5 DEMO2QASIT_REPORT
SyncPrimarySecondaryDBAll  BILLERGW BILLERGW billergw spice#123 MFS_ALL_DEMO2QASIT_BILLERGW demo qasit 192.168.2.5 192.168.2.5 DEMO2QASIT_BILLERGW
    if grep "ERROR" $VAR_DBlog
    then
     exit 1
    else
      exit 0
    fi
;;

"QA2DEMO_PAYPRO")
    SyncPrimarySecondaryDBAll  PAYPRO PAYPRO paypro spice#123 MFS_QA2DEMO_PAYPRO qa demo 192.168.2.4 192.168.2.5 QA2DEMO_PAYPRO
    if grep "ERROR" $VAR_DBlog
    then
     exit 1
    else
      exit 0
    fi

;;

"QA2DEMO_BILLERGW")
    SyncPrimarySecondaryDBAll  BILLERGW BILLERGW billergw spice#123 MFS_QA2DEMO_BILLERGW qa demo 192.168.2.4 192.168.2.5 QA2DEMO_BILLERGW
    if grep "ERROR" $VAR_DBlog
    then
     exit 1
    else
      exit 0
    fi

;;

*)
    echo "Do Nothing"
;;
esac