t.sh

#!/bin/sh

#
# Variables and constants
#
SCRIPT=`basename $0`
HOST_NAME=`hostname`
WHOAMI=`whoami`
TODAY=`date +%Y%m%d`
NOW=`date +%H%M`
FAILURE=1
SUCCESS=0
USAGE1="${SCRIPT}"
USAGE2="e.g. ${SCRIPT}"
SEPARATOR="********************************************************************************"
PARA_NUM=$#
WARNING="-----------------------------------------------------[WARNING]"
INFOLINE="-------------------------------------------------INFO"
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK


## Path of file to be generated and find
NAS_PATH=`pwd`
#d $NAS_PATH



S_LOG_PATH=${NAS_PATH}/source/log
S_SCRIPT_PATH=${NAS_PATH}/source/script
T_LOG_PATH=${NAS_PATH}/target/log
T_SCRIPT_PATH=${NAS_PATH}/target/script
S_XTTS_PATH=${NAS_PATH}/source/xtts
T_XTTS_PATH=${NAS_PATH}/target/xtts
EXPDP_PATH=${NAS_PATH}/dmp
IMPDP_PATH=${NAS_PATH}/dmp
T_RMAN_PATH=${T_XTTS_PATH}/rman
S_BACKUP_DIR=${NAS_PATH}/source/backup
T_BACKUP_DIR=${NAS_PATH}/target/backup
mkdir -p ${T_BACKUP_DIR}
mkdir -p ${S_LOG_PATH}
mkdir -p ${S_SCRIPT_PATH}
mkdir -p ${T_LOG_PATH}
mkdir -p ${T_SCRIPT_PATH}
mkdir -p ${T_RMAN_PATH}
mkdir -p ${S_XTTS_PATH}
chmod +x $NAS_PATH/bin/rsqlgb.sh

mkdir -p ${T_XTTS_PATH}/tmpdir

main ()
{
tmp="^^^^^^^^^^^"
while true
do
echo " "
echo " "
echo "$tmp LBS MIGRATION $tmp"
main_menu
done

}


main_menu ()
{
#declare -a menu
IFS=~~
menu[1]="xtts_getfiles"
menu[2]="check_xtts_getfiles"
menu[3]="apply_incr_backup"
menu[4]="check_apply_incr_backup"
menu[5]="backup_target_db"
menu[6]="check_backup_target_db"
menu[7]="chk_db"
menu[8]="create_users"
menu[9]="check_users"
menu[10]="grant_user_role_sys_privilege"
menu[11]="check_user_role_sys_privilege"
menu[12]="restart_db_disable_audit_dblink"
menu[13]="check_audit_dblink"
menu[14]="check_running_instance"
menu[15]="prepare_impdp_tbsmeta_para"
menu[16]="check_prepare_impdp_tbsmeta_para"
menu[17]="impdp_tbsmeta"
menu[18]="check_impdp_tbsmeta"
menu[19]="check_datafile_missing"
menu[20]="create_BEFORE_FULLDB_IMPDP_restore_point"
menu[21]="check_BEFORE_FULLDB_IMPDP_restore_point"
menu[22]="set_tbs_readwrite"
menu[23]="chk_tbs_readonly"
menu[24]="impdp_fullmeta"
menu[25]="create_impdp_fullmeta_index"
menu[26]="check_impdp_fullmeta"
menu[27]="impdp_trigger"
menu[28]="complie_objects_utlprp"
menu[29]="check_complie_objects_utlprp"
menu[30]="restart_db_enable_audit_dblink"
menu[31]="check_impdp_trigger"
menu[32]="unpack_spm"
menu[33]="import_stat"
menu[34]="check_spm"
menu[35]="check_stat"
menu[36]="gather_dict_fix_stat"
menu[37]="check_fix_stat"
menu[38]="grant_user_role_privilege"
menu[39]="compare_user_role_privilege"
menu[40]="create_problem_indexes_and_manual_obj"
menu[41]="check_problem_indexes"
menu[42]="deploy_rac_kill_procedure"
menu[43]="check_deploy_rac_kill_procedure"
menu[44]="deploy_new_Gath_stat"
menu[45]="check_deploy_new_Gath_stat"
menu[46]="upgrade_audit_package"
menu[47]="check_upgrade_audit_package"
menu[48]="drop_abandom_users"
menu[49]="check_drop_abandom_users"
menu[50]="check_registry"
menu[51]="check_migrate_invalid_objects"
menu[52]="compare_objects"
menu[53]="check_invalid_objects"
menu[54]="fix_mview"
menu[55]="recreate_jobs"
menu[56]="check_recreate_jobs"
menu[57]="check_fix_mview"




menu[100]="Other: Exit"
PS3="Please enter your chioce: "
select MENU_READ in `echo "${menu[*]}"`
do
case $REPLY in
1)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
2)
echo "Your Choice is :$MENU_READ"
confirm $MENU_READ
break
;;
3)
echo "Your Choice is :$MENU_READ"
confirm $MENU_READ
break
;;
4)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
5)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
6)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
7)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
8)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
9)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
10)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
11)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
12)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
13)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
14)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
15)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
16)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
17)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
18)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
19)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
20)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
21)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
22)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
23)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
24)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ

break
;;
25)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
26)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
27)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;

28)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
29)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
30)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
31)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
32)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
33)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;

34)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
35)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
36)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
37)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
38)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
39)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
40)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
41)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
42)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
43)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
44)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
45)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
46)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
47)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
48)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
49)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
50)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
51)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
52)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
53)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
54)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
55)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
56)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
57)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
58)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
59)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
60)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
61)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
62)
echo "Your Choice is : $MENU_READ"
confirm $MENU_READ
break
;;
*)
echo "BYE BYE"
echo "Your Choice is to quit the script: $REPLY"
exit 0
;;


esac
done
}



runsql ()
{
sqlplus -S '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
spool ${T_SCRIPT_PATH}/user_role_privilege.sql
spool ${T_LOG_PATH}/xxx.log
spool off
EOF
}

confirm ()
{
echo "CONFIRM TO <<" $1 ">> ? [Y|N]"
read FLAG

if [ "$FLAG" == 'y' ] || [ "$FLAG" == 'Y' ]; then
$1
fi
}

upgrade_audit_package ()
{
sqlplus -S '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
spool ${T_LOG_PATH}/upgrade_audit_package.log
@${T_SCRIPT_PATH}/PINGAN_AUDIT_UPGRADE.sql
spool off
EOF
}

check_upgrade_audit_package ()
{
sqlplus -S '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
spool ${T_LOG_PATH}/upgrade_audit_package.log
@${T_SCRIPT_PATH}/pa_audit_check.sql
spool off
EOF
}


deploy_rac_kill_procedure ()
{
sqlplus -S '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
spool ${T_LOG_PATH}/deploy_rac_kill_procedure.log
@${T_SCRIPT_PATH}/proc_kill_session_rac.sql
spool off
EOF
check_deploy_rac_kill_procedure
}

check_deploy_rac_kill_procedure ()
{
sqlplus -S '/ as sysdba' << EOF
set echo off
set wrap off
set lines 12000
set pages 999
col owner for a10
col object_name for a30
spool ${T_LOG_PATH}/check_deploy_rac_kill_procedure.log
select oj.OWNER,oj.OBJECT_NAME,oj.status,oj.LAST_DDL_TIME from dba_objects oj where object_name=upper('prc_kill_session_rac');
spool off
EOF
}


drop_abandom_users ()
{
sqlplus -S '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
spool ${T_LOG_PATH}/drop_abandom_users.log
drop user FGLPA cascade;
drop user QUEST cascade;
drop user SPOTLIGHT cascade;
spool off
EOF
}

check_drop_abandom_users ()
{
sqlplus -S '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
spool ${T_LOG_PATH}/check_drop_abandom_users.log
select 'Should be 0: ' from dual;
select count(1) from dba_users where username in ('FGLPA','QUEST','SPOTLIGHT');
spool off
EOF
}


deploy_new_Gath_stat ()
{
sqlplus -s '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
spool ${T_LOG_PATH}/deploy_new_spm_Gath_stat.log
conn /as sysdba
alter user dbmgr identified by yhsyxyz8;
@ ${T_SCRIPT_PATH}/initdb/gathering_statistics/scripts/00_sys_grant.sql
conn dbmgr/yhsyxyz8
@ ${T_SCRIPT_PATH}/initdb/gathering_statistics/scripts/01_dbmgr_create_objects-new.sql
@ ${T_SCRIPT_PATH}/initdb/gathering_statistics/scripts/02_dbmgr_gather_cbo_stats_internal_h.sql
@ ${T_SCRIPT_PATH}/initdb/gathering_statistics/scripts/03_dbmgr_gather_cbo_stats_internal_b.sql
@ ${T_SCRIPT_PATH}/initdb/gathering_statistics/scripts/03_dbmgr_gather_cbo_stats_internal_b_for19c.sql
@ ${T_SCRIPT_PATH}/initdb/gathering_statistics/scripts/04_dbmgr_gather_cbo_stats_auto_h.sql
@ ${T_SCRIPT_PATH}/initdb/gathering_statistics/scripts/05_dbmgr_gather_cbo_stats_auto_b_for19c.sql
@ ${T_SCRIPT_PATH}/initdb/gathering_statistics/scripts/05_dbmgr_gather_cbo_stats_auto_b_for19c_rac.sql
@ ${T_SCRIPT_PATH}/initdb/gathering_statistics/scripts/06_dbmgr_gather_cbo_stats_manual_h.sql
@ ${T_SCRIPT_PATH}/initdb/gathering_statistics/scripts/07_dbmgr_gather_cbo_stats_manual_b.sql
@ ${T_SCRIPT_PATH}/initdb/gathering_statistics/scripts/08_dbmgr_grt_object.sql
@ ${T_SCRIPT_PATH}/initdb/gathering_statistics/scripts/09_dbmgr_delete_zero_stats.sql
@ ${T_SCRIPT_PATH}/initdb/gathering_statistics/scripts/10_sys_set_global_prefs.sql
@ ${T_SCRIPT_PATH}/initdb/gathering_statistics/scripts/10_sys_set_global_prefs_for12c.sql
exec gather_cbo_stats_auto.add_jobs;
column job_type format a10
column what format a50
alter session set nls_date_format='yyyy-mm-dd:hh24:mi:ss';
select a.job,a.job_type,b.what,b.next_date from dbmgr.stats_gather_jobs a inner join dba_jobs b on a.job=b.job;
spool off
EOF

}

check_deploy_new_Gath_stat ()
{
cat ${T_LOG_PATH}/deploy_new_spm_Gath_stat.log


sqlplus -s '/ as sysdba' << EOF
spool ${T_LOG_PATH}/check_deploy_new_Gath_stat.log
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
column job_type format a10
column what format a50
alter session set nls_date_format='yyyy-mm-dd:hh24:mi:ss';
select a.job,a.job_type,b.what,b.next_date from dbmgr.stats_gather_jobs a inner join dba_jobs b on a.job=b.job;
spool off
EOF

}

recreate_jobs ()
{
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
export NLS_DATE_FORMAT='DD-MON-RR'
run_script_backgroup ${T_SCRIPT_PATH}/recreate_jobs.sql ${T_LOG_PATH}/recreate_jobs.log recreate_jobs
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
}

create_impdp_fullmeta_index ()
{
sqlplus -s '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
col log_user for a10;
col priv_user for a10;
col schema_user for a10;
spool ${T_LOG_PATH}/create_impdp_fullmeta_index.log
create index sys.IX_IMPDP_FULLMETA_PPO_KEN on IMPDP_FULLMETA(parent_process_order) online;
begin
dbms_stats.gather_table_stats(ownname => 'SYS' ,tabname =>'IMPDP_FULLMETA' ,cascade => true,method_opt => 'FOR ALL columns size SKEWONLY');
end;
/
select owner,index_name,uniqueness,status from dba_indexes where index_name in ('IX_IMPDP_FULLMETA_PPO_KEN');
spool off
EOF
}



check_recreate_jobs ()
{
tail -300 ${T_LOG_PATH}/recreate_jobs.log

sqlplus -s '/ as sysdba' << EOF
set echo off
set wrap off
set lines 12000
set pages 999
col log_user for a10;
col priv_user for a10;
col schema_user for a10;
spool ${T_LOG_PATH}/check_recreate_jobs.log
select log_user,schema_user,priv_user ,count(1) cnt from dba_jobs group by log_user,schema_user,priv_user;
spool off
EOF

}


check_fix_stat ()
{
sqlplus -S '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
col operation for a30
col start_time for a30
col end_time for a30
col status for a10
spool ${T_LOG_PATH}/check_fix_stat.log
col operation for a50
col start_time for a40
col end_time for a40
col status for a10
select operation,to_char(START_TIME,'yyyy-mm-dd hh24:mi:ss') start_time,to_char(END_TIME,'yyyy-mm-dd hh24:mi:ss') end_time ,status from dba_optstat_operations where operation in ('gather_dictionary_stats','gather_fixed_objects_stats')
and START_TIME > sysdate -1
order by 1 desc;
spool off

EOF
}

backup_target_db ()
{
nohup ./bin/rman_backup.sh ${T_RMAN_PATH} > ${T_RMAN_PATH}/rman_backup.log 2>&1 &
ls -l ${T_RMAN_PATH}
ps -ef |grep rman_backup |grep -v grep
}

check_backup_target_db ()
{

tail -50 ${T_RMAN_PATH}/rman_backup.log
ls -lhrt ${T_RMAN_PATH} |tail -10
du -sh ${T_RMAN_PATH}
}

create_BEFORE_FULLDB_IMPDP_restore_point ()
{
ps -ef |grep poracle |grep YES|grep -v grep |awk '{print $2}'|xargs kill -9
sqlplus '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
alter system switch logfile ;
alter system checkpoint ;
create restore point BEFORE_FULLDB_IMPDP guarantee flashback database;
alter system switch logfile ;
alter system switch logfile ;
alter system switch logfile ;
alter system checkpoint ;
EOF
}

check_BEFORE_FULLDB_IMPDP_restore_point ()
{

sqlplus -s '/ as sysdba' << EOF
set echo off
set feed off
set wrap off
set lines 12000
set pages 999
col name for a30
col GUARANTEE_FLASHBACK_DATABASE for a30
col RESTORE_POINT_TIME for a30
col STORAGE_SIZE for a30
select NAME, t.GUARANTEE_FLASHBACK_DATABASE,t.RESTORE_POINT_TIME,t.STORAGE_SIZE from v\$restore_point t;
EOF
}

check_dg ()
{
sqlplus -s '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
col process for a10
col status for a10
col sequence# for 9999999999999999
col inst_id for 99
col THREAD# for 9
select process,status,sequence# from v\$managed_standby;
select INST_ID,PROCESS,STATUS,THREAD#,SEQUENCE# ,DELAY_MINS from gv\$managed_standby where process like 'MRP%';
EOF
}


restart_db_enable_audit_dblink ()
{
ps -ef |grep `whoami` |grep YES|grep -v grep |awk '{print $2}'|xargs kill -9
sqlplus '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
alter system set open_links=50 scope=spfile;
alter system set audit_trail=DB scope=spfile;
alter system set aq_tm_processes=0 scope=both;
alter system set job_queue_processes=0 scope=both;
shutdown immediate;
startup;
EOF
}

restart_db_disable_audit_dblink ()
{
ps -ef |grep poracle |grep YES|grep -v grep |awk '{print $2}'|xargs kill -9
sqlplus '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
alter system set open_links=0 scope=spfile;
alter system set audit_trail=NONE scope=spfile;
shutdown immediate;
startup;
EOF
}

check_audit_dblink ()
{

sqlplus -S '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
show parameter open_links;
show parameter audit_trail;
EOF
}

check_running_instance ()
{

sqlplus -S '/ as sysdba' << EOF
set echo off
set wrap off
set lines 12000
set pages 999
spool ${T_LOG_PATH}/check_running_instance.log
select inst_id,instance_name,host_name,startup_time,status from gv\$instance;
spool off
EOF
}


create_problem_indexes_and_manual_obj ()
{

run_script_backgroup ${T_SCRIPT_PATH}/create_problem_indexes_and_manual_obj.sql ${T_LOG_PATH}/create_problem_indexes_and_manual_obj.log create_problem_indexes_and_manual_obj


}

check_problem_indexes ()
{
tail -300 ${T_LOG_PATH}/create_problem_indexes_and_manual_obj.log

sqlplus -s '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
spool ${T_LOG_PATH}/check_check_problem_indexes.log
select 'SHOULD BE 3 INDEXESS' from dual;
select owner,index_name,uniqueness,status from dba_indexes where index_name in ('PK_LBKRGNCDERLTION_RGNCODERLTV','PR_N_SAL_INFO_AGENTNO_Y_M','PK_WORK_APPL_POLI_W_A_S');
spool off
EOF


}


xtts_getfiles ()
{
LOG_SUFIX=`date +%Y%m%d`.`date +%H%M`
mkdir -p ${T_XTTS_PATH}/tmpdir
export TMPDIR=${T_XTTS_PATH}/tmpdir
if [ -f ${T_XTTS_PATH}/tmpdir/xttnewdatafiles.txt ]; then
mv ${T_XTTS_PATH}/tmpdir/xttnewdatafiles.txt ${T_XTTS_PATH}/tmpdir/xttnewdatafiles.txt.${LOG_SUFIX}
fi
if [ -f ${T_XTTS_PATH}/tmpdir/getfile.sql ]; then
mv ${T_XTTS_PATH}/tmpdir/getfile.sql ${T_XTTS_PATH}/tmpdir/getfile.sql.${LOG_SUFIX}
fi

cp ${S_XTTS_PATH}/tmpdir/getfile.sql ${T_XTTS_PATH}/tmpdir/
cp ${S_XTTS_PATH}/tmpdir/xttnewdatafiles.txt ${T_XTTS_PATH}/tmpdir/
export XTTDEBUG=1
cd ${T_XTTS_PATH}/xtts
nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl -G >${T_LOG_PATH}/xtts_getfiles.${LOG_SUFIX}.log 2>&1 &
ls -l ${T_LOG_PATH}/xtts_getfiles.${LOG_SUFIX}.log
#tail -f ${T_LOG_PATH}/xtts_incr_backup.${LOG_SUFIX}.log

}

check_xtts_getfiles ()
{
FILE=`ls -ldtr ${T_XTTS_PATH}/tmpdir/getfile_* |tail -1 |awk '{print $9}'`
echo "log file is : "
tail -300 ${FILE}/*.log
ls -l ${FILE}/*.log
echo "TOTAL NUM OF DATAFILE:: "
cat ${FILE}/xttnewdatafiles.txt |grep DESTDIR |wc -l
echo "NUM OF DATAFILE HAVE BEEN TRANSFERED: "
cat ${FILE}/*.log |grep getfile_ |wc -l
}



cat ${FILE}/*.log| grep getfile |wc -l

prepare_impdp_tbsmeta_para ()
{
LOG_SUFIX=`date +%Y%m%d`.`date +%H%M`
mkdir -p ${T_XTTS_PATH}/tmpdir
export TMPDIR=${T_XTTS_PATH}/tmpdir
export XTTDEBUG=1
cd ${T_XTTS_PATH}/xtts
$ORACLE_HOME/perl/bin/perl xttdriver.pl -e > ${T_LOG_PATH}/prepare_impdp_tbsmeta_para.log
ls -l ${T_LOG_PATH}/prepare_impdp_tbsmeta_para.log
#tail -f ${T_LOG_PATH}/xtts_incr_backup.${LOG_SUFIX}.log
ls -l ${TMPDIR}/xttplugin.txt

HEADNUM=`cat -n ${IMPDP_PATH}/impdp_tbsmeta.para.tmp |grep transport_datafiles |awk '{print $1-1}'`
head -${HEADNUM} ${IMPDP_PATH}/impdp_tbsmeta.para.tmp > ${IMPDP_PATH}/impdp_tbsmeta.para
tail -1 ${TMPDIR}/xttplugin.txt >> ${IMPDP_PATH}/impdp_tbsmeta.para
ls -l ${IMPDP_PATH}/impdp_tbsmeta.para
}

check_prepare_impdp_tbsmeta_para ()
{

tail -30 ${T_LOG_PATH}/prepare_impdp_tbsmeta_para.log
export TMPDIR=${T_XTTS_PATH}/tmpdir
ls -l ${TMPDIR}/xttplugin.txt
}


check_fix_mview ()
{

sqlplus -S '/ as sysdba' << EOF
set linesize 1000
col object_name for a30
col LOG_OWNER for a10
col master for a30
col owner for a15
col MVIEW_NAME for a50
select object_name, do.LAST_DDL_TIME from dba_objects do where object_name in (select dl.LOG_TABLE from dba_mview_logs dl );
select 'should be 10', dml.LOG_OWNER,dml.master from dba_mview_logs dml;
select 'should be 4', dm.OWNER,dm.MVIEW_NAME,dm.LAST_REFRESH_TYPE,dm.STALENESS,dm.LAST_REFRESH_DATE,dm.LAST_REFRESH_END_TIME,dm.COMPILE_STATE from dba_mviews dm;
EOF
}

fix_mview ()
{
run_script_backgroup ${T_SCRIPT_PATH}/fix_mview.sql ${T_LOG_PATH}/fix_mview.log fix_mview
}



impdp_fullmeta ()
{

sqlplus -S '/ as sysdba' << EOF
CREATE OR REPLACE DIRECTORY TTS_IMP AS '${IMPDP_PATH}';
EOF

LOG_SUFIX=`date +%Y%m%d`.`date +%H%M`
nohup impdp parfile=${IMPDP_PATH}/impdp_fullmeta.para >${T_LOG_PATH}/impdp_fullmeta.${LOG_SUFIX}.log 2>&1 &
ps -ef |grep impdp_fullmeta.para |grep -v grep
ls -l ${T_LOG_PATH}/impdp_fullmeta.${LOG_SUFIX}.log
sqlplus -S '/ as sysdba' << EOF
select owner_name ,job_name,operation,job_mode from DBA_DATAPUMP_JOBS where state='EXECUTING';
select 'impdp "'' / as sysdba''" attach='||job_name from DBA_DATAPUMP_JOBS where state='EXECUTING';

EOF
}

impdp_trigger ()
{

sqlplus -S '/ as sysdba' << EOF
CREATE OR REPLACE DIRECTORY TTS_IMP AS '${IMPDP_PATH}';
EOF

LOG_SUFIX=`date +%Y%m%d`.`date +%H%M`
nohup impdp parfile=${IMPDP_PATH}/impdp_trigger.para >${T_LOG_PATH}/impdp_trigger.${LOG_SUFIX}.log 2>&1 &
ps -ef |grep impdp_trigger.para |grep -v grep
ls -l ${T_LOG_PATH}/impdp_trigger.${LOG_SUFIX}.log
sqlplus -S '/ as sysdba' << EOF
select owner_name ,job_name,operation,job_mode from DBA_DATAPUMP_JOBS where state='EXECUTING';
select 'impdp "'' / as sysdba''" attach='||job_name from DBA_DATAPUMP_JOBS where state='EXECUTING';
--create index sys.idx_impdp_trigger on sys.impdp_trigger(duplicate,object_type,processing_state) online;
--begin
--dbms_stats.gather_table_stats(ownname => 'SYS' ,tabname =>'IMPDP_TRIGGER' ,cascade => true,method_opt => 'FOR ALL columns size SKEWONLY');
--end;
--/
EOF
}


check_impdp_fullmeta ()
{
FILE=`ls -l ${T_LOG_PATH}/impdp_fullmeta.*.log |tail -1 |awk '{print $9}'`
cat ${IMPDP_PATH}/impdp_fullmeta.log|egrep "Completed|Processing" |tail -300
echo "nohup file is : "${FILE}
ps -ef |grep impdp_fullmeta.para |grep -v grep
sqlplus -S '/ as sysdba' << EOF
set linesize 1000
col owner_name for a15
col job_name for a15
col job_mode for a15
select owner_name ,job_name,operation,job_mode from DBA_DATAPUMP_JOBS where state='EXECUTING';
select 'impdp "'' / as sysdba''" attach='||job_name ATTACH_COMMAND from DBA_DATAPUMP_JOBS where state='EXECUTING';
EOF
}


impdp_tbsmeta ()
{

sqlplus -S '/ as sysdba' << EOF
CREATE OR REPLACE DIRECTORY TTS_IMP AS '${IMPDP_PATH}';
EOF

LOG_SUFIX=`date +%Y%m%d`.`date +%H%M`
ls -l ${IMPDP_PATH}/impdp_tbsmeta.para
nohup impdp parfile=${IMPDP_PATH}/impdp_tbsmeta.para >${T_LOG_PATH}/impdp_tbsmeta.${LOG_SUFIX}.log 2>&1 &
ps -ef |grep impdp_tbsmeta.para |grep -v grep
ls -l ${T_LOG_PATH}/impdp_tbsmeta.${LOG_SUFIX}.log
sqlplus -S '/ as sysdba' << EOF
select owner_name ,job_name,operation,job_mode from DBA_DATAPUMP_JOBS where state='EXECUTING';
select 'impdp "'' / as sysdba''" attach='||job_name from DBA_DATAPUMP_JOBS where state='EXECUTING';

EOF

}


check_impdp_tbsmeta ()
{
FILE=`ls -ltr ${IMPDP_PATH}/impdp_tbsmeta*.log |tail -1 |awk '{print $9}'`

tail -50 ${IMPDP_PATH}/impdp_tbsmeta.log |egrep "Completed|Processing" |tail -300
echo "nohup file is : "${FILE}
ps -ef |grep impdp_tbsmeta.para |grep -v grep
sqlplus -S '/ as sysdba' << EOF
set linesize 1000
col owner_name for a15
col job_name for a15
col job_mode for a15
select owner_name ,job_name,operation,job_mode from DBA_DATAPUMP_JOBS where state='EXECUTING';
select 'impdp "'' / as sysdba''" attach='||job_name ATTACH_COMMAND from DBA_DATAPUMP_JOBS where state='EXECUTING';
EOF
}


check_impdp_trigger ()
{
FILE=`ls -l ${T_LOG_PATH}/impdp_trigger.*.log |tail -1 |awk '{print $9}'`
echo "nohup file is : "${FILE}
tail -50 ${IMPDP_PATH}/impdp_trigger.log
ps -ef |grep impdp_trigger.para |grep -v grep
sqlplus -S '/ as sysdba' << EOF
set linesize 1000
col owner_name for a15
col job_name for a15
col job_mode for a15
select owner_name ,job_name,operation,job_mode from DBA_DATAPUMP_JOBS where state='EXECUTING';
select 'impdp "'' / as sysdba''" attach='||job_name ATTACH_COMMAND from DBA_DATAPUMP_JOBS where state='EXECUTING';
EOF
}


check_apply_incr_backup ()
{

FILE=`ls -ldtr ${T_XTTS_PATH}/tmpdir/rollforward_* |tail -1 |awk '{print $9}'`
echo "log file is : "
ls -l ${FILE}/*.log
tail -30 ${FILE}/*.log
echo "NUM OF DATAFILE APPLIED: "
cat ${FILE}/*.log| grep "datafile name" |wc -l
}


apply_incr_backup ()
{

rman target / <<EOF
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 16;
EOF

LOG_SUFIX=`date +%Y%m%d`.`date +%H%M`
mkdir -p ${T_XTTS_PATH}/tmpdir
export TMPDIR=${T_XTTS_PATH}/tmpdir
if [ -f ${T_XTTS_PATH}/tmpdir/xttplan.txt ]; then
mv ${T_XTTS_PATH}/tmpdir/xttplan.txt ${T_XTTS_PATH}/tmpdir/xttplan.txt.${LOG_SUFIX}
fi
if [ -f ${T_XTTS_PATH}/tmpdir/tsbkupmap.txt ]; then
mv ${T_XTTS_PATH}/tmpdir/tsbkupmap.txt ${T_XTTS_PATH}/tmpdir/tsbkupmap.txt.${LOG_SUFIX}
fi

cp ${S_XTTS_PATH}/tmpdir/xttplan.txt ${T_XTTS_PATH}/tmpdir/
cp ${S_XTTS_PATH}/tmpdir/tsbkupmap.txt ${T_XTTS_PATH}/tmpdir/
export XTTDEBUG=1
cd ${T_XTTS_PATH}/xtts
nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl -r >${T_LOG_PATH}/xtts_incr_backup.${LOG_SUFIX}.log 2>&1 &
ls -l ${T_LOG_PATH}/xtts_incr_backup.${LOG_SUFIX}.log
#tail -f ${T_LOG_PATH}/xtts_incr_backup.${LOG_SUFIX}.log
}


check_registry ()
{
sqlplus -S '/ as sysdba' << EOF
set echo off
set feed off
set wrap off
set lines 12000
set pages 999
col comp_id for 9999
col comp_name for a30
col version for a30
col status for a10
spool ${T_LOG_PATH}/check_registry.log
select comp_id,comp_name,version,status from dba_registry;
spool off

EOF

}

gather_dict_fix_stat ()
{
run_script_backgroup ${T_SCRIPT_PATH}/gather_dict_fix_stat.sql ${T_LOG_PATH}/gather_dict_fix_stat.log gather_dict_fix_stat
ls -l ${T_LOG_PATH}/gather_dict_fix_stat.log
}



import_stat ()
{
run_script_backgroup ${T_SCRIPT_PATH}/import_stat.sql ${T_LOG_PATH}/import_stat.log import_stat
ls -l ${T_LOG_PATH}/import_stat.log
}

check_stat ()
{
sqlplus -S '/ as sysdba' << EOF


select count(1) STAT_DIFF_COUNT
from dba_tab_statistics T
WHERE NOT exists
(select 1
from MIGUPS.S_DBA_TAB_STATISTICS s
where t.owner = s.owner
and t.table_name = s.table_name
and (t.PARTITION_NAME=s.PARTITION_NAME or (t.PARTITION_NAME is null and s.PARTITION_NAME is null))
and (t.SUBPARTITION_NAME=s.SUBPARTITION_NAME or (t.SUBPARTITION_NAME is null and s.SUBPARTITION_NAME is null))
and ((t.last_analyzed is null and s.last_analyzed is null)
or t.last_analyzed = s.last_analyzed)) and t.owner in (select username
from migups.s_user_list) and last_analyzed is null ;
EOF

}

unpack_spm ()
{
run_script_backgroup ${T_SCRIPT_PATH}/unpack_spm.sql ${T_LOG_PATH}/unpack_spm.log unpack_spm
ls -l ${T_LOG_PATH}/unpack_spm.log
}

check_spm ()
{
sqlplus -S '/ as sysdba' << EOF
select count(1) SPM_COUNT from dba_sql_plan_baselines;
EOF

}


check_datafile_missing ()
{
sqlplus -S '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
spool ${T_LOG_PATH}/check_datafile_missing.log
select df.tablespace_name ,count(1) from MIGUPS.S_DBA_DATA_FILES df where df.tablespace_name in( select tbsname from migups.s_tbs_list)
group by df.tablespace_name
minus
select df.tablespace_name ,count(1) from dba_data_files df where df.tablespace_name in( select tbsname from migups.s_tbs_list)
group by df.tablespace_name;
spool off
EOF

MISSING_DATAFILE=`cat ${T_LOG_PATH}/check_datafile_missing.log |grep -v "no rows selected" |grep -v ">"|tr -s '\n'|wc -l`
if [ ${MISSING_DATAFILE} -gt 0 ]; then
echo "[MISSING_DATAFILE] MORE THEN 1 DATAFILE MISSING "${WARNING}
else
echo "NO DATAFILE MISSING "${INFOLINE}
fi

}


chk_db ()
{
sqlplus -S '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 120
set pages 999
col "name" form a30
col "value" form a30
set COLSEP '^'
spool ${T_LOG_PATH}/chk_db.log
select name,value from v\$parameter p where p.NAME in ('open_links','audit_trail','db_files');

spool off

SELECT 'DB_LINK_TO_GL' FROM DUAL@DBLINK_MIGUP_GL ;
--SELECT 'DB_LINK_TO_GM' FROM DUAL@DBLINK_MIGUP_GM ;
--SELECT 'DB_LINK_TO_WGQ' FROM DUAL@DBLINK_MIGUP_WGQ ;
EOF

OPEN_LINKS=`cat ${T_LOG_PATH}/chk_db.log |grep open_links |awk -F"^" '{print $2}'|grep -v "no rows selected" |grep -v ">"|tr -s '\n'`
AUDIT=`cat ${T_LOG_PATH}/chk_db.log |grep audit_trail |awk -F"^" '{print $2}'|grep -v "no rows selected" |grep -v ">"|tr -s '\n'|sed 's/ //g'`
DB_FILES=`cat ${T_LOG_PATH}/chk_db.log |grep db_files |awk -F"^" '{print $2}'|grep -v "no rows selected" |grep -v ">"|tr -s '\n'`
RUNNING_NODE=`srvctl status database -db lugz0 |grep -v "not running" |wc -l`

if [ "${OPEN_LINKS}" -gt 0 ]; then
echo "[open_links] is not set to 0 "${WARNING}
else
echo "[open_links] HAVE BEEN SET TO 0 "${INFOLINE}
fi

if [ "${AUDIT}" != "NONE" ]; then
echo "[AUDIT] HAVE NOT SET SET TO NONE "${WARNING}
else
echo "[AUDIT] HAVE BEEN SET SET TO NONE"${INFOLINE}
fi

if [ "${DB_FILES}" -lt 8000 ]; then
echo "[DB_FILES] IS SET TO LESS THEN 8000 "${WARNING}
else
echo "[DB_FILES] HAVE BEEN SET TO GREATER THEN OR EQUAL TO 8000 "${INFOLINE}
fi

#if [ "${RUNNING_NODE}" -gt 1 ]; then
# echo "[RUNNING_NODE] DB IS RUNNING ON MORE THEN 1 NODE "${WARNING}
#else
# echo "[RUNNING_NODE] DB IS ONLY RUNING ON 1 NODE "${INFOLINE}
#fi

IFS=$SAVEIFS
srvctl status database -db `echo ${ORACLE_SID%1*}`
}



run_script_backgroup ()
{
if [ -z $1 ]; then
echo "USAGE, NEED TO INPUT SCRIPT LOCALTION AND LOG FILE NAME"
elif [ -z $2 ]; then
echo "USAGE, NEED TO INPUT LOG FILE NAME AS SECOND PARAMETER"
elif [ -z $2 ]; then
echo "USAGE： NEED TO INPUT PROCESS IDENTIFIER AS THIRTH PARAMETER"
fi

nohup $NAS_PATH/bin/rsqlgb.sh $1 $2 $3 &
PROCESS=`ps -ef |grep $3 |grep -v grep|awk '{print $2}'`;
echo "---------------------------------------------"
echo "SCRIPT "$1" IS RUNNING AT BACKGROUP AS "${PROCESS}", LOG FILE IS: "$2
echo "PLEASE CHECK LOG FOR DETAIL"
echo "---------------------------------------------"
}




check_invalid_objects ()
{
sqlplus -S '/ as sysdba' << EOF
set echo off
set head on
set feed off
set wrap off
set lines 12000
set pages 999
col OWNER for a30
col object_type for a20
spool ${T_LOG_PATH}/check_invalid_objects.log
select owner,object_type, object_name from dba_objects do where do.status!='VALID' order by 1,2,3;
select owner,object_type, count(1) CNT from dba_objects do where do.status!='VALID' group by owner,object_type order by 1,2,3;
spool off

EOF
ls -l ${T_LOG_PATH}/check_invalid_objects.log
}

check_migrate_invalid_objects ()
{
sqlplus '/ as sysdba' << EOF
set echo off
set head on
set feed off
set wrap off
set lines 12000
set pages 999
col OWNER for a30
col object_type for a20
spool ${T_LOG_PATH}/check_invalid_objects.log
select owner,object_type, count(1) CNT from dba_objects do where do.owner in (select username from migups.s_user_list) and do.status!='VALID' group by owner,object_type order by 1,2,3;
spool off

EOF
ls -l ${T_LOG_PATH}/check_invalid_objects.log
}


compare_objects ()
{
sqlplus -S '/ as sysdba' << EOF
set echo off
set head on
set feed off
set wrap off
set lines 12000
set pages 999
col owner for a20
col OBJECT_NAME for a50
col object_type for a20
spool ${T_LOG_PATH}/compare_objects.log

SELECT 'OBJECTS NOT FOUND IN TARGET:' FROM DUAL;
SELECT DO.OWNER, DO.OBJECT_NAME, object_type
from MIGUPS.S_DBA_OBJECTS do
where owner in (select username from migups.s_user_list)
AND NOT EXISTS (SELECT 1
FROM DBA_OBJECTS O
WHERE O.OWNER = DO.OWNER
AND O.OBJECT_NAME = DO.OBJECT_NAME
AND O.OBJECT_TYPE = DO.OBJECT_TYPE )
AND OBJECT_NAME NOT LIKE '%/%'
AND OBJECT_NAME NOT LIKE 'SYS%' AND OWNER NOT IN ('PUBLIC') ORDER BY OWNER,OBJECT_TYPE,DO.OBJECT_NAME;

SELECT 'OBJECTS COMPARE OVER VIEW: ' FROM DUAL;
select a.* from (
with rs as (select owner,object_name,object_type,status from MIGUPS.S_dba_objects
where owner in (select username from migups.s_user_list)
and owner not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK') and object_name not like 'SYS%' /*and object_name not like 'DBMS_JOB%'*/ ),
tg as (select owner,object_name,object_type,status from dba_objects
where owner in (select username from migups.s_user_list)
and owner not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK') AND object_name not like 'SYS%' and object_name not like 'DBMS_JOB%' )
select 'source ' role,object_type,status,count(1) num from rs where not exists (select 1 from tg where rs.owner=tg.owner and rs.object_name=tg.object_name and rs.object_type=tg.object_type and rs.status=tg.status ) and rs.status='VALID' group by object_type,status
union all
select 'target' role,object_type,status,count(1) num from tg where not exists (select 1 from rs where rs.owner=tg.owner and rs.object_name=tg.object_name and rs.object_type=tg.object_type and rs.status=tg.status) and tg.status='INVALID' group by object_type,status
) a where object_type not in ('SYNONYM') order by object_type,status;
spool off

EOF
ls -l ${T_LOG_PATH}/compare_objects.log
}


complie_objects_utlprp ()
{
sqlplus -S '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
col value for 999
spool ${T_LOG_PATH}/parallel_max_servers.log
select value from v\$parameter where name='parallel_max_servers';
spool off;
alter system set parallel_max_servers=16 scope=memory ;
EOF
PARALLEL_MAX=`cat ${T_LOG_PATH}/parallel_max_servers.log |grep -v "no rows selected" |grep -v ">"|tr -s '\n'|sed 's/ //g'`
run_script_backgroup $ORACLE_HOME/rdbms/admin/utlrp.sql ${T_LOG_PATH}/complie_objects_utlprp.log complie_objects_utlprp
sqlplus -S '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
spool ${T_LOG_PATH}/parallel_max_servers_setback.log
alter system set parallel_max_servers=${PARALLEL_MAX} scope=memory;
select value from v\$parameter where name='parallel_max_servers';
EOF
}

check_complie_objects_utlprp ()
{
tail -30 ${T_LOG_PATH}/complie_objects_utlprp.log
sqlplus -S '/ as sysdba' << EOF
set echo off
set feed off
set wrap off
set lines 12000
set pages 999
spool ${T_LOG_PATH}/check_complie_objects_utlprp.log
SELECT COUNT(*) INVALID_OBJECTS_REMAINING FROM obj$ WHERE status IN (4, 5, 6);
SELECT COUNT(*) INVALID_OBJECTS_COMPLIED FROM UTL_RECOMP_COMPILED;
SELECT job_name JOB_CREATED FROM dba_scheduler_jobs where job_name like 'UTL_RECOMP_SLAVE_%';
SELECT job_name JOB_RUNNING FROM dba_scheduler_running_jobs WHERE job_name like 'UTL_RECOMP_SLAVE_%';
EOF
}


check_user_role_sys_privilege ()
{
sqlplus -S '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999

spool ${T_LOG_PATH}/check_role_sys_privilege.log

with tg as (select PRIVILEGE, GRANTEE,admin_option FROM DBA_SYS_PRIVS WHERE GRANTEE in (select username from migups.s_user_list@dblink_migup_gl) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK')),
rs as (select PRIVILEGE, GRANTEE,admin_option FROM DBA_SYS_PRIVS@dblink_migup_gl WHERE GRANTEE in (select username from migups.s_user_list@dblink_migup_gl) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK'))
select 'GRANT '||PRIVILEGE||' TO '||GRANTEE ||CASE admin_option WHEN 'YES' THEN ' with admin option;' ELSE ';' END from rs where not exists (select 1 from tg where rs.PRIVILEGE=tg.PRIVILEGE and rs.grantee=tg.grantee and rs.admin_option=rs.admin_option);



with tg as (select GRANTED_ROLE, GRANTEE,admin_option FROM DBA_ROLE_PRIVS WHERE GRANTEE in (select username from migups.s_user_list@dblink_migup_gl) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK')),
rs as (select GRANTED_ROLE, GRANTEE,admin_option FROM DBA_ROLE_PRIVS@dblink_migup_gl WHERE GRANTEE in (select username from migups.s_user_list@dblink_migup_gl) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK'))
select 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE||CASE admin_option WHEN 'YES' THEN ' with admin option;' ELSE ';' END from rs where not exists (select 1 from tg where rs.GRANTED_ROLE=tg.GRANTED_ROLE and rs.grantee=tg.grantee ) ;


with tg as (select PRIVILEGE, GRANTEE,admin_option FROM DBA_SYS_PRIVS WHERE GRANTEE in (select GRANTED_ROLE FROM USER_ROLE_PRIVS WHERE USERNAME in (select username from migups.s_user_list@dblink_migup_gl)) ),
rs as (select PRIVILEGE, GRANTEE,admin_option FROM DBA_SYS_PRIVS@dblink_migup_gl WHERE GRANTEE in (select GRANTED_ROLE FROM USER_ROLE_PRIVS WHERE USERNAME in (select username from migups.s_user_list@dblink_migup_gl)) )
select 'GRANT '||PRIVILEGE||' TO '||GRANTEE||CASE admin_option WHEN 'YES' THEN ' with admin option;' ELSE ';' END from rs where not exists (select 1 from tg where rs.PRIVILEGE=tg.PRIVILEGE and rs.grantee=tg.grantee) AND grantee not in ('DBA');

spool off
EOF

CHECK_USER_ROLE_SYS=`cat ${T_LOG_PATH}/check_role_sys_privilege.log|grep -v "no rows selected" |grep -v ">"|tr -s '\n'`

if [ "" != "$CHECK_USER_ROLE_SYS" ]; then
echo "["`wc -l ${T_LOG_PATH}/check_role_sys_privilege.log` "] PRIVILEGES IS NOT FULLY GRANTED "${WARNING}
else
echo "ALL SYS PRIVILEGES HAVE BEEN GRANT TO USERS AND ROLES "${INFOLINE}
fi
IFS=$SAVEIFS

}



grant_user_role_sys_privilege ()
{
sqlplus -S '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
grant unlimited tablespace to MIGUPS;
grant execute on dbms_aqadm to public;


spool ${T_SCRIPT_PATH}/user_role_sys_privilege.sql

select 'grant create job to '|| username|| ';' from migups.s_user_list@dblink_migup_gl;

--USER DBS SYS
with tg as (select PRIVILEGE, GRANTEE,admin_option FROM DBA_SYS_PRIVS WHERE GRANTEE in (select username from migups.s_user_list@dblink_migup_gl) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK')),
rs as (select PRIVILEGE, GRANTEE,admin_option FROM DBA_SYS_PRIVS@dblink_migup_gl WHERE GRANTEE in (select username from migups.s_user_list@dblink_migup_gl) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK'))
select 'GRANT '||PRIVILEGE||' TO '||GRANTEE ||CASE admin_option WHEN 'YES' THEN ' with admin option;' ELSE ';' END from rs where not exists (select 1 from tg where rs.PRIVILEGE=tg.PRIVILEGE and rs.grantee=tg.grantee and rs.admin_option=rs.admin_option);


--USER DBA ROLE
with tg as (select GRANTED_ROLE, GRANTEE,admin_option FROM DBA_ROLE_PRIVS WHERE GRANTEE in (select username from migups.s_user_list@dblink_migup_gl) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK')),
rs as (select GRANTED_ROLE, GRANTEE,admin_option FROM DBA_ROLE_PRIVS@dblink_migup_gl WHERE GRANTEE in (select username from migups.s_user_list@dblink_migup_gl) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK'))
select 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE||CASE admin_option WHEN 'YES' THEN ' with admin option;' ELSE ';' END from rs where not exists (select 1 from tg where rs.GRANTED_ROLE=tg.GRANTED_ROLE and rs.grantee=tg.grantee ) ;

--ROLE DBA SYS
with tg as (select PRIVILEGE, GRANTEE,admin_option FROM DBA_SYS_PRIVS WHERE GRANTEE in (select GRANTED_ROLE FROM USER_ROLE_PRIVS WHERE USERNAME in (select username from migups.s_user_list@dblink_migup_gl)) ),
rs as (select PRIVILEGE, GRANTEE,admin_option FROM DBA_SYS_PRIVS@dblink_migup_gl WHERE GRANTEE in (select GRANTED_ROLE FROM USER_ROLE_PRIVS WHERE USERNAME in (select username from migups.s_user_list@dblink_migup_gl)) )
select 'GRANT '||PRIVILEGE||' TO '||GRANTEE||CASE admin_option WHEN 'YES' THEN ' with admin option;' ELSE ';' END from rs where not exists (select 1 from tg where rs.PRIVILEGE=tg.PRIVILEGE and rs.grantee=tg.grantee) AND grantee not in ('DBA');



spool off
! ls -l ${T_SCRIPT_PATH}/user_role_sys_privilege.sql
@${T_SCRIPT_PATH}/user_role_sys_privilege.sql
EOF
ls -l ${T_SCRIPT_PATH}/user_role_sys_privilege.sql
#run_script_backgroup ${T_SCRIPT_PATH}/user_role_sys_privilege.sql ${T_LOG_PATH}/user_role_sys_privilege.log user_role_sys_privilege
}





grant_user_role_privilege ()
{
sqlplus -S '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
grant execute on LIFEMAN.LAS_REP_BANKVIREMENT_PKG to lifejob;
grant execute on DBMS_LOCK to LIFEMAN;
grant execute on DBMS_LOCK to REINSDEV;
grant execute on SYS.DBMS_SHARED_POOL to dbmgr;
grant all on SYS.DBMS_AUDIT_MGMT to dbmgr;
grant execute on SYS.DBMS_CRYPTO to dbmgr;
grant execute on DBMS_REDEFINITION to dbmgr;
grant execute on DBMS_SERVICE to dbmgr;

spool ${T_SCRIPT_PATH}/user_role_privilege.sql

--USER DBS SYS
with tg as (select PRIVILEGE, GRANTEE,admin_option FROM DBA_SYS_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK')),
rs as (select PRIVILEGE, GRANTEE,admin_option FROM MIGUPS.S_DBA_SYS_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK'))
select 'GRANT '||PRIVILEGE||' TO '||GRANTEE ||CASE admin_option WHEN 'YES' THEN ' with admin option;' ELSE ';' END from rs where not exists (select 1 from tg where rs.PRIVILEGE=tg.PRIVILEGE and rs.grantee=tg.grantee and rs.admin_option=rs.admin_option);

--USER TAB PRIV
with tg as (select PRIVILEGE,owner,table_name, GRANTEE FROM DBA_TAB_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK')),
rs as (select PRIVILEGE,owner,table_name, GRANTEE FROM MIGUPS.S_DBA_TAB_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK'))
select 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||';' from rs where not exists (select 1 from tg where rs.PRIVILEGE=tg.PRIVILEGE and rs.table_name=tg.table_name and rs.grantee=tg.grantee) and table_name not like '%/%' order by GRANTEE;


--USER DBA ROLE
with tg as (select GRANTED_ROLE, GRANTEE,admin_option FROM DBA_ROLE_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK')),
rs as (select GRANTED_ROLE, GRANTEE,admin_option FROM MIGUPS.S_DBA_ROLE_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK'))
select 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE||CASE admin_option WHEN 'YES' THEN ' with admin option;' ELSE ';' END from rs where not exists (select 1 from tg where rs.GRANTED_ROLE=tg.GRANTED_ROLE and rs.grantee=tg.grantee ) ;

--ROLE DBA SYS
with tg as (select PRIVILEGE, GRANTEE,admin_option FROM DBA_SYS_PRIVS WHERE GRANTEE in (select GRANTED_ROLE FROM USER_ROLE_PRIVS WHERE USERNAME in (select username from migups.s_user_list)) ),
rs as (select PRIVILEGE, GRANTEE,admin_option FROM MIGUPS.S_DBA_SYS_PRIVS WHERE GRANTEE in (select GRANTED_ROLE FROM USER_ROLE_PRIVS WHERE USERNAME in (select username from migups.s_user_list)) )
select 'GRANT '||PRIVILEGE||' TO '||GRANTEE||CASE admin_option WHEN 'YES' THEN ' with admin option;' ELSE ';' END from rs where not exists (select 1 from tg where rs.PRIVILEGE=tg.PRIVILEGE and rs.grantee=tg.grantee) AND grantee not in ('DBA');

--ROLE DBA TAB
with tg as (select PRIVILEGE,owner,table_name, GRANTEE FROM DBA_TAB_PRIVS WHERE GRANTEE in (select GRANTED_ROLE FROM USER_ROLE_PRIVS WHERE USERNAME in (select username from migups.s_user_list))),
rs as (select PRIVILEGE,owner,table_name, GRANTEE FROM MIGUPS.S_DBA_TAB_PRIVS WHERE GRANTEE in (select GRANTED_ROLE FROM USER_ROLE_PRIVS WHERE USERNAME in (select username from migups.s_user_list)))
select 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||';' from rs where not exists (select 1 from tg where rs.PRIVILEGE=tg.PRIVILEGE and rs.table_name=tg.table_name and rs.grantee=tg.grantee) AND grantee not in ('DBA');


--DEFAULT TABLESPACE

SELECT 'ALTER USER '||du.USERNAME||' DEFAULT TABLESPACE '||DU.default_tablespace||';' FROM MIGUPS.S_DBA_USERS DU,dba_users dut WHERE du.username=dut.username and du.default_tablespace!=dut.default_tablespace and DU.username IN (select username from migups.s_user_list) and DU.default_tablespace in (select name from v\$tablespace);
--TABLESPACE QUOTA

SELECT 'ALTER USER ' || USERNAME || ' QUOTA ' ||
DECODE(MAX_BYTES, -1, 'UNLIMITED', MAX_BYTES) || ' ON ' ||
TABLESPACE_NAME || ';'
FROM MIGUPS.S_DBA_TS_QUOTAS TS
WHERE USERNAME in
(select username from migups.s_user_list)
AND NOT EXISTS (SELECT 1
FROM DBA_TS_QUOTAS TT
WHERE TS.TABLESPACE_NAME = TT.TABLESPACE_NAME
AND TS.USERNAME = TT.USERNAME
AND TT.max_bytes = TS.MAX_BYTES) and TABLESPACE_NAME in (select name from v\$tablespace)
union
select 'ALTER USER ' || USERNAME || ' QUOTA UNLIMITED ON ' ||
DU.default_tablespace || ';'
from dba_users DU
where USERNAME in
(select username from migups.s_user_list)
AND NOT EXISTS (SELECT 1
FROM DBA_TS_QUOTAS TS
WHERE TS.TABLESPACE_NAME = DU.default_tablespace
AND TS.USERNAME = DU.USERNAME);
spool off
! ls -l ${T_SCRIPT_PATH}/user_role_privilege.sql
--@${T_SCRIPT_PATH}/user_role_privilege.sql
EOF
ls -l ${T_SCRIPT_PATH}/user_role_privilege.sql
run_script_backgroup ${T_SCRIPT_PATH}/user_role_privilege.sql ${T_LOG_PATH}/user_role_privilege.log grant_user_role_privilege
}


compare_user_role_privilege ()
{
sqlplus -S '/ as sysdba' << EOF
set wrap on
set lines 12000
set pages 999

spool ${T_LOG_PATH}/user_role_privilege.log

--USER DBA SYS
with tg as (select PRIVILEGE, GRANTEE,admin_option FROM DBA_SYS_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK')),
rs as (select PRIVILEGE, GRANTEE,admin_option FROM MIGUPS.S_DBA_SYS_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK'))
select COUNT(1) NUM_DBA_SYS_NG2_USER from rs where not exists (select 1 from tg where rs.PRIVILEGE=tg.PRIVILEGE and rs.grantee=tg.grantee and rs.admin_option=rs.admin_option);

--USER TAB PRIV
with tg as (select PRIVILEGE,owner,table_name, GRANTEE FROM DBA_TAB_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK')),
rs as (select PRIVILEGE,owner,table_name, GRANTEE FROM MIGUPS.S_DBA_TAB_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK'))
select COUNT(1) NUM_DBA_TAB_NG2_USER from rs where not exists (select 1 from tg where rs.PRIVILEGE=tg.PRIVILEGE and rs.table_name=tg.table_name and rs.grantee=tg.grantee) and table_name not like '%/%' order by GRANTEE;

--USER DBA ROLE
with tg as (select GRANTED_ROLE, GRANTEE,admin_option FROM DBA_ROLE_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK')),
rs as (select GRANTED_ROLE, GRANTEE,admin_option FROM MIGUPS.S_DBA_ROLE_PRIVS WHERE GRANTEE in (select username from migups.s_user_list) and GRANTEE not in ('AUDSYS','GSMADMIN_INTERNAL','OJVMSYS','NICK'))
select COUNT(1) NUM_DBA_ROLE_NG2_ROLE from rs where not exists (select 1 from tg where rs.GRANTED_ROLE=tg.GRANTED_ROLE and rs.grantee=tg.grantee ) ;

--ROLE DBA SYS
with tg as (select PRIVILEGE, GRANTEE,admin_option FROM DBA_SYS_PRIVS WHERE GRANTEE in (select GRANTED_ROLE FROM USER_ROLE_PRIVS WHERE USERNAME in (select username from migups.s_user_list)) ),
rs as (select PRIVILEGE, GRANTEE,admin_option FROM MIGUPS.S_DBA_SYS_PRIVS WHERE GRANTEE in (select GRANTED_ROLE FROM USER_ROLE_PRIVS WHERE USERNAME in (select username from migups.s_user_list)) )
select COUNT(1) NUM_DBA_SYS_NG2_ROLE from rs where not exists (select 1 from tg where rs.PRIVILEGE=tg.PRIVILEGE and rs.grantee=tg.grantee) AND grantee not in ('DBA');

--ROLE DBA TAB
with tg as (select PRIVILEGE,owner,table_name, GRANTEE FROM DBA_TAB_PRIVS WHERE GRANTEE in (select GRANTED_ROLE FROM USER_ROLE_PRIVS WHERE USERNAME in (select username from migups.s_user_list))),
rs as (select PRIVILEGE,owner,table_name, GRANTEE FROM MIGUPS.S_DBA_TAB_PRIVS WHERE GRANTEE in (select GRANTED_ROLE FROM USER_ROLE_PRIVS WHERE USERNAME in (select username from migups.s_user_list)))
select COUNT(1) NUM_DBA_TAB_NG2_ROLE from rs where not exists (select 1 from tg where rs.PRIVILEGE=tg.PRIVILEGE and rs.table_name=tg.table_name and rs.grantee=tg.grantee) AND grantee not in ('DBA');


--DEFAULT TABLESPACE

SELECT count(1) DEFAULT_TABLESPACE_DIFF FROM MIGUPS.S_DBA_USERS DU,dba_users dut WHERE du.username=dut.username and du.default_tablespace!=dut.default_tablespace and DU.username IN (select username from migups.s_user_list) and DU.default_tablespace in (select name from v\$tablespace);


--TABLESPACE QUOTA
SELECT COUNT(1) TABLESPACE_QUOTA_DIFF FROM (
SELECT 'ALTER USER ' || USERNAME || ' QUOTA ' ||
DECODE(MAX_BYTES, -1, 'UNLIMITED', MAX_BYTES) || ' ON ' ||
TABLESPACE_NAME || ';'
FROM MIGUPS.S_DBA_TS_QUOTAS TS
WHERE USERNAME in
(select username from migups.s_user_list)
AND NOT EXISTS (SELECT 1
FROM DBA_TS_QUOTAS TT
WHERE TS.TABLESPACE_NAME = TT.TABLESPACE_NAME
AND TS.USERNAME = TT.USERNAME
AND TT.max_bytes = TS.MAX_BYTES) and TABLESPACE_NAME in (select name from v\$tablespace)
union
select 'ALTER USER ' || USERNAME || ' QUOTA UNLIMITED ON ' ||
DU.default_tablespace || ';'
from dba_users DU
where USERNAME in
(select username from migups.s_user_list)
AND NOT EXISTS (SELECT 1
FROM DBA_TS_QUOTAS TS
WHERE TS.TABLESPACE_NAME = DU.default_tablespace
AND TS.USERNAME = DU.USERNAME));
spool off
EOF
ls -l ${T_SCRIPT_PATH}/user_role_privilege.log

}




chk_tbs_readonly ()
{
sqlplus -S '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
col "TABLESPACE_NAME" form a30
col "STATUS" form a30
spool ${T_LOG_PATH}/tbs_readonly.log
select TBS.TABLESPACE_NAME ,STATUS from dba_tablespaces tbs where tbs.tablespace_name in (select * from migups.s_tbs_list) /* OR tablespace_name in ('SYSTEM','SYSAUX') */ order by STATUS desc;
spool off
EOF

NOT_READONLY_TBS=`cat $T_LOG_PATH/tbs_readonly.log |sed 1d |grep -v "ONLINE" |awk '{print $1}'|grep -v "no rows selected" |grep -v ">"|tr -s '\n'`
echo $NOT_READONLY_TBS
if [ "" != "$NOT_READONLY_TBS" ]; then
echo "["$NOT_READONLY_TBS"] is not read write"${WARNING}
for i in $NOT_READONLY_TBS
do
echo "alter tablespace "$i" read write;"
done
fi
}

set_tbs_readwrite ()
{
echo "confirm set database read write? [Y|N]"
read FLAG
if [ "$FLAG" == 'y' ] || [ "$FLAG" == 'Y' ]; then
sqlplus -S '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set linesize 1000
set pages 999
col "TABLESPACE_NAME" form a30
spool ${T_SCRIPT_PATH}/set_tbs_readwrite.sql
select 'alter tablespace '||TBS.TABLESPACE_NAME || ' read write;'from dba_tablespaces tbs where tbs.tablespace_name in (select * from migups.s_tbs_list) and STATUS!='ONLINE';
spool off
spool ${T_LOG_PATH}/set_tbs_readwrite.log
@${T_SCRIPT_PATH}/set_tbs_readwrite.sql
spool off
EOF
fi
}


stop_jobs ()
{
sqlplus -S '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
spool ${T_LOG_PATH}/stop_jobs.log
@${S_SCRIPT_PATH}/set_job_3999.sql
spool off
spool ${T_LOG_PATH}/jobs_set_fail.log
select job from dba_jobs j where j.next_date < to_date('3999-09-09','yyyy-mm-dd') ;
spool off
EOF

JOBS_SET_FAIL=`cat $T_LOG_PATH/jobs_set_fail.log |grep -v "no rows selected" |grep -v ">"|tr -s '\n'`

if [ "" != "$JOBS_SET_FAIL" ]; then
echo "["$JOBS_SET_FAIL"] RUNNING DATE DATA SET FAIL"${WARNING}
IFS=' '
for i in $JOBS_SET_FAIL
do
echo " exec sys.dbms_ijob.next_date(job => "$1",next_date=>to_date('3999/9/9','yyyy/mm/dd')) ; "
done
fi
IFS=$SAVEIFS
}

create_users ()
{
sqlplus -S '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999

spool ${T_SCRIPT_PATH}/role.sql

select case PASSWORD_REQUIRED
when 'YES' then
'create role ' || role || ' identified by values '''||du.password||''';'
else
'create role ' || role || ';'
end
from dba_roles@dblink_migup_gl dr, sys.user\$@dblink_migup_gl du
where du.name= dr.role and dr.ROLE not in (select role from dba_roles);
spool off

spool ${T_SCRIPT_PATH}/profile.sql
select (case
when rn = 1 then
('create profile ' || profile ||' limit ' || resource_name ||' ' || limit || (case
when rn = max_rn then
';'
end)) else(resource_name || ' ' || limit || (case
when rn = max_rn then
';'
end)) end)
from (select profile,
resource_name,
limit,
row_number() over(partition by profile order by profile, resource_name) rn,
count(*) over(partition by profile) max_rn
from dba_profiles@dblink_migup_gl
where profile in
(select distinct profile
from dba_profiles@dblink_migup_gl
minus
select distinct profile from dba_profiles)
order by profile, resource_name)
order by profile, resource_name;
spool off

spool ${T_SCRIPT_PATH}/users.sql
select 'CREATE USER ' || u.username || ' IDENTIFIED BY VALUES ''' ||
s.password || ''' DEFAULT TABLESPACE ' || 'USERS_19C' ||
' temporary TABLESPACE temp ' || ' PROFILE ' ||
u.profile ||
decode(u.account_status, 'EXPIRED', ' PASSWORD EXPIRE', '') ||
' ACCOUNT ' || decode(u.account_status, 'LOCKED', 'LOCK', 'UNLOCK') || ';'
from dba_users@dblink_migup_gl u, sys.user\$@dblink_migup_gl s
where u.username = s.name and u.username not in (select username from dba_users);
spool off

spool ${T_SCRIPT_PATH}/dblink.sql
select distinct 'grant create database link to ' || owner || ';'
from dba_db_linkS@dblink_migup_gl
where owner not in ( select grantee from dba_sys_privs dp where dp.privilege like upper('create database link'));
spool off

spool ${T_LOG_PATH}/create_users.log
! ls -l ${T_SCRIPT_PATH}/profile.sql;
! ls -l ${T_SCRIPT_PATH}/role.sql;
! ls -l ${T_SCRIPT_PATH}/users.sql;
! ls -l ${T_SCRIPT_PATH}/dblink.sql;
! ls -l ${T_SCRIPT_PATH}/profile_pw_function.sql
@${T_SCRIPT_PATH}/profile_pw_function.sql;
@${T_SCRIPT_PATH}/profile.sql;
@${T_SCRIPT_PATH}/role.sql;
@${T_SCRIPT_PATH}/users.sql;
@${T_SCRIPT_PATH}/dblink.sql;

spool off


EOF


}




check_users ()
{
sqlplus -S '/ as sysdba' << EOF
set echo off
set head off
set feed off
set wrap off
set lines 12000
set pages 999
col username for a30
col ROLE for a30
col owner for a30
spool ${T_LOG_PATH}/user_check.log
select distinct username from dba_users@dblink_migup_gl du
where du.username not in (select username from dba_users) and username is not null;
spool off;

spool ${T_LOG_PATH}/role_check.log
select distinct role
from dba_roles@dblink_migup_gl dr
where dr.ROLE not in (select role from dba_roles);
spool off;

spool ${T_LOG_PATH}/dblink_grant_check.log
select distinct owner
from dba_db_linkS@dblink_migup_gl
where owner not in ( select grantee from dba_sys_privs dp where dp.privilege like upper('create database link'));
spool off
EOF

ROLE_NOT_FOUND=`cat $T_LOG_PATH/role_check.log |grep -v "no rows selected" |grep -v ">"|tr -s '\n'`

if [ "" != "$ROLE_NOT_FOUND" ]; then
echo "["$ROLE_NOT_FOUND"] ROLE NEED TO BE CREATED IN TARGET "${WARNING}
else
echo "ALL ROLE CREATED"${INFOLINE}
fi


USER_NOT_FOUND=`cat $T_LOG_PATH/user_check.log |grep -v "no rows selected" |grep -v ">"|tr -s '\n'`

if [ "" != "$USER_NOT_FOUND" ]; then
echo "["$USER_NOT_FOUND"] USER NEED TO BE CREATED IN TARGET "${WARNING} |sed 's/ //g'

else
echo "ALL USER CREATED"${INFOLINE}
fi

DB_LINK_GRANT_NOT_FOUND=`cat $T_LOG_PATH/dblink_grant_check.log |grep -v "no rows selected" |grep -v ">"|tr -s '\n'`
if [ "" != "$DB_LINK_GRANT_NOT_FOUND" ]; then
echo "["$DB_LINK_GRANT_NOT_FOUND"] NEED TO BE GRANT CREATE DB_LINK IN TARGET "${WARNING}
else
echo "ALL DB LINK OWNER WAS GRANT WITH CREATE DB LINK PRIVILIGE"${INFOLINE}
fi


}



#
# MAIN
#
SAVEIFS=$IFS
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
echo "hostname is:"
hostname
echo $0 |grep source/xtts
echo $0 |grep target/xtts
echo "CONFIRM THIS IS <<"LINUX X86 GGGGGGLLLLLL ENV ">> ? [Y|N]"
read FLAG

if [ "$FLAG" == 'y' ] || [ "$FLAG" == 'Y' ]; then
main
fi



IFS=$SAVEIFS