DBdeployProduction.sh

#!/bin/bash

function SyncPrimarySecondaryDBAll() {
######## Run time variables for Destination
	DB_Source=$1
	DB_Destination=$2
	User_Source=$3
	User_Destination=$4
	Flag=$5
	TAG=$6
	RollBack=$7

	if [ $DB_Source == "DEV" -o $DB_Source == "DEVBUGFIX" -o $DB_Source == "QA" ]
	then
		SourceIP='192.168.2.4'
		SourceSID=`echo $DB_Source | tr '[:upper:]' '[:lower:]'`
	elif [ $DB_Source == "PROD" ]
	then
		SourceIP='172.16.8.65'
		SourceSID='proddb.500017366.id1.internal'
	else
		SourceIP='192.168.2.5'
		SourceSID=`echo $DB_Source | tr '[:upper:]' '[:lower:]'`
	fi
	if [ $DB_Destination == "DEV" -o $DB_Destination == "DEVBUGFIX" -o $DB_Destination == "QA" ]
	then
		DestinationIP='192.168.2.4'
		export ORACLE_HOME=/home/u01/app/oracle/product/12.1.0/dbhome_1;
		export PATH=$ORACLE_HOME/bin:/usr/bin:/usr/local/bin:/bin;
		DestinationSID=`echo $DB_Destination | tr '[:upper:]' '[:lower:]'`
	elif [ $DB_Destination == "PROD" ]
	then
   		DestinationIP='172.16.8.65'
		export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1;
		export PATH=$ORACLE_HOME/bin:/usr/bin:/usr/local/bin:/bin;
		DestinationSID='proddb.500017366.id1.internal'	
	else
		DestinationIP='192.168.2.5'
		export ORACLE_HOME=/home/u01/app/oracle/product/12.1.0/dbhome_1;
		export PATH=$ORACLE_HOME/bin:/usr/bin:/usr/local/bin:/bin;
		DestinationSID=`echo $DB_Destination | tr '[:upper:]' '[:lower:]'`
	fi


	#if [ $User_Source == "ALL" -o $User_Destination == "ALL" ]
        DB_Link="$DB_Source"2"$DB_Destination"_"$User_Source"
	if [ $Flag == 1 ]
	then
		Destination_Env=MFS_ALL_"$DB_Link"
	else
		Destination_Env=MFS_"$DB_Link"
	fi
	
	PRIMARY_SCHEMA=$User_Source	
    SECONDARY_SCHEMA=$User_Destination
    Destination_Username=`echo $User_Destination | tr '[:upper:]' '[:lower:]'`
    Destination_Password='spice#123'
    	
	
    METADATA_PATH=/home/oracle/$Destination_Env/automation
    [ -d $METADATA_PATH ] || mkdir -p $METADATA_PATH
    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
    FINAL_STATUS=/home/oracle/finalStatus.txt
    ############### Variables for Packages, Procedure, Functions, Constraints ######### 
    METADATA_PATH2=/home/oracle/$Destination_Env/automation2
    [ -d $METADATA_PATH2 ] || mkdir -p $METADATA_PATH2
    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
expdp system/Spice_123@$DestinationIP/$DestinationSID schemas=$SECONDARY_SCHEMA directory=DATA_PUMP_DIR dumpfile=DataBaseDump_using_build_$TAG.dmp logfile=DataBaseDump_using_build_$TAG.log

/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

## Condition for Rollback ##
ErrorCount=`grep "ERROR" $VAR_DBlog | wc -l`
if [ $ErrorCount > 0 -a $RollBack == "Yes" ] 
then
	echo "$User_Destination" >> $FINAL_STATUS
 	sh /home/oracle/DBRollback.sh $User_Destination $DestinationIP $DestinationSID $TAG
elif [ $ErrorCount > 0 -a $RollBack == "No" ]
then
	echo "$User_Destination" >> $FINAL_STATUS
else
	echo "Do Nothing"
fi

} ## Function definition ends


if [ $3 == "ALL" -a $4 == "ALL" ]
then
	Flag=1
	SyncPrimarySecondaryDBAll $1 $2 MFS MFS $Flag $5 $6
	SyncPrimarySecondaryDBAll $1 $2 PAYPRO PAYPRO $Flag $5 $6
	SyncPrimarySecondaryDBAll $1 $2 CAMP CAMP $Flag $5 $6
	SyncPrimarySecondaryDBAll $1 $2 ARCH ARCH $Flag $5 $6
	SyncPrimarySecondaryDBAll $1 $2 REPORT REPORT $Flag $5 $6
	SyncPrimarySecondaryDBAll $1 $2 BILLERGW BILLERGW $Flag $5 $6
elif [ $3 == $4 -a $3 != "ALL" -a $4 != "ALL" ]
then
	Flag=0
	SyncPrimarySecondaryDBAll $1 $2 $3 $4 $Flag $5 $6
        

else
	echo "Wrong Parameters! Kindly choose correct conbination"
        exit 1
fi
if [ -s /home/oracle/finalStatus.txt ]
then
	echo "DB Build Error in:"
	cat /home/oracle/finalStatus.txt 
	>/home/oracle/finalStatus.txt 
	exit 1
else
	exit 0
fi