#删除用户然后重新建用户再导入dump数据
importDB()
{
arr_sys_user=(${sys_user_list//,/ })
sys_schemalist="";
for sys_user_name in ${arr_sys_user[@]}
do
if [ "x${sys_user_name}" != "x" ] ; then
sys_schemalist=\\\'${sys_user_name}\\\'\\\,${sys_schemalist}
fi
done
sys_schemalist=`echo ${sys_schemalist%,*}`
if [ "X$except_flag" != "X" ]
then
tablelist=""
while read TableName ; do
if [ "x${TableName}" != "x" ] ; then
#tablelist=\\\'INOC_SDM_SERVICEDESK_${sdm_number}\\\.${TableName}\\\'\\\,${tablelist}
tablelist=\\\'${TableName}\\\'\\\,${tablelist}
fi
done < table_list
echo "table_list: ${table_list}"
table_list=`echo ${tablelist%,*}`
if [ "$except_flag" == "switch" ]; then
if [ ! -f ${dump_directory}/insert_to_backup_table.log ]
then
> ${dump_directory}/insert_to_backup_table.log
fi
if [ ! -f ${dump_directory}/drop_table.log ]
then
> ${dump_directory}/drop_table.log
fi
if [ ! -f ${dump_directory}/alter_table_name.log ]
then
> ${dump_directory}/alter_table_name.log
fi
chmod 777 ${dump_directory}/insert_to_backup_table.log
chmod 777 ${dump_directory}/drop_table.log
chmod 777 ${dump_directory}/alter_table_name.log
#get fk and set fk disable >>
sed -i "s#@cur_path#$INSTALL_HOME/dbscript/impdp#g" $INSTALL_HOME/dbscript/impdp/get_fk_disable.sql
su - oracle -c "sqlplus INOC_SDM_SERVICEDESK_${sdm_number}/${DB_APP_password}@//${DB_server_ip}:${DB_Port}/${DB_instance} @$INSTALL_HOME/dbscript/impdp/get_fk_disable.sql;"
while read fk_low_line ; do
if [ "x${fk_low_line}" != "x" ] ; then
fk_low=`echo $fk_low_line | grep [a-z] | awk '{print $NF }' `
if [ "x${fk_low}" != "x" ] ; then
fk_low=`echo ${fk_low%;*}`
fk_low_replace=\"$fk_low\"
sed -i "s#${fk_low}#${fk_low_replace}#g" $INSTALL_HOME/dbscript/impdp/set_fk_disable.sql
fi
fi
done < $INSTALL_HOME/dbscript/impdp/set_fk_disable.sql
su - oracle -c "sqlplus INOC_SDM_SERVICEDESK_${sdm_number}/${DB_APP_password}@//${DB_server_ip}:${DB_Port}/${DB_instance} <<EOF
@$INSTALL_HOME/dbscript/impdp/set_fk_disable.sql;
quit;
EOF";
#get fk and set fk disable <<
su - oracle -c "sqlplus INOC_SDM_SERVICEDESK_${sdm_number}/${DB_APP_password}@//${DB_server_ip}:${DB_Port}/${DB_instance} >${dump_directory}/insert_to_backup_table.log<<EOF
@$INSTALL_HOME/dbscript/impdp/insert_to_backup_table.sql;
quit;
EOF";
if [ $? -ne 0 ]; then
writeLog $log_file_name "Can not insert data to backup_table" $log_path
exit 1;
fi
RESULT=`cat ${dump_directory}/insert_to_backup_table.log |grep -v -E 'ORA-02449|ORA-28011|ORA-00001' | grep ORA`
if [ "$RESULT" ]
then
writeLog $log_file_name "Can not insert data to backup_table, please check ${dump_directory}/insert_to_backup_table.log" $log_path
exit 1;
fi
writeLog $log_file_name "Insert data to backup table successfully" $log_path
su - oracle -c "sqlplus INOC_SDM_SERVICEDESK_${sdm_number}/${DB_APP_password}@//${DB_server_ip}:${DB_Port}/${DB_instance} >${dump_directory}/drop_table.log<<EOF
@$INSTALL_HOME/dbscript/impdp/drop_table.sql;
quit;
EOF";
if [ $? -ne 0 ]; then
writeLog $log_file_name "Can not drop main_table" $log_path
exit 1;
fi
RESULT=`cat ${dump_directory}/drop_table.log |grep -v -E 'ORA-02449|ORA-28011|ORA-00001' | grep ORA`
if [ "$RESULT" ]
then
writeLog $log_file_name "Can not drop main_table, please check ${dump_directory}/drop_table.log" $log_path
exit 1;
fi
writeLog $log_file_name "Drop main_table successfully" $log_path
su - oracle -c "sqlplus INOC_SDM_SERVICEDESK_${sdm_number}/${DB_APP_password}@//${DB_server_ip}:${DB_Port}/${DB_instance} >${dump_directory}/alter_table_name.log<<EOF
@$INSTALL_HOME/dbscript/impdp/alter_table_name.sql;
quit;
EOF";
if [ $? -ne 0 ]; then
writeLog $log_file_name "Can not alter table name" $log_path
exit 1;
fi
RESULT=`cat ${dump_directory}/alter_table_name.log |grep -v -E 'ORA-02449|ORA-28011|ORA-00001' | grep ORA`
if [ "$RESULT" ]
then
writeLog $log_file_name "Can not alter table name, please check ${dump_directory}/alter_table_name.log" $log_path
exit 1;
fi
writeLog $log_file_name "Alter table name successfully" $log_path
su - oracle -c "impdp ${DB_System_username}/${DB_System_password}@//${DB_server_ip}:${DB_Port}/${DB_instance} DIRECTORY=dumpdir parallel=4 ENCRYPTION_PASSWORD=${DB_wallet_password} logfile=inoc_sdm_servicedesk_${sdm_number}_switch_index.log DUMPFILE=inoc_sdm_servicedesk_${sdm_number}_%u.dump SCHEMAS=inoc_sdm_servicedesk_${sdm_number} include=index,constraint,comment;"
RESULT=`cat ${dump_directory}/inoc_sdm_servicedesk_${sdm_number}_switch_index.log |grep -v -E 'ORA-31684|ORA-02270|ORA-39083' | grep ORA`
if [ "$RESULT" ]
then
writeLog $log_file_name "Can not rebuild index, please check ${dump_directory}/inoc_sdm_servicedesk_${sdm_number}_switch_index.log" $log_path
exit 1;
fi
writeLog $log_file_name "Rebuild index successfully" $log_path
#get fk and set fk disable >>
sed -i "s#@cur_path#$INSTALL_HOME/dbscript/impdp#g" $INSTALL_HOME/dbscript/impdp/get_fk_enable.sql
su - oracle -c "sqlplus INOC_SDM_SERVICEDESK_${sdm_number}/${DB_APP_password}@//${DB_server_ip}:${DB_Port}/${DB_instance} @$INSTALL_HOME/dbscript/impdp/get_fk_enable.sql;"
while read fk_low_line ; do
if [ "x${fk_low_line}" != "x" ] ; then
fk_low=`echo $fk_low_line | grep [a-z] | awk '{print $NF }' `
if [ "x${fk_low}" != "x" ] ; then
fk_low=`echo ${fk_low%;*}`
fk_low_replace=\"$fk_low\"
sed -i "s#${fk_low}#${fk_low_replace}#g" $INSTALL_HOME/dbscript/impdp/set_fk_enable.sql
fi
fi
done < $INSTALL_HOME/dbscript/impdp/set_fk_enable.sql
su - oracle -c "sqlplus INOC_SDM_SERVICEDESK_${sdm_number}/${DB_APP_password}@//${DB_server_ip}:${DB_Port}/${DB_instance} <<EOF
@$INSTALL_HOME/dbscript/impdp/set_fk_enable.sql;
quit;
EOF";
#get fk and set fk disable <<
if [ ! -d ${dump_directory}/log ];then
mkdir ${dump_directory}/log
chmod -R 777 ${dump_directory}/log
fi
mv ${dump_directory}/*.log -t ${dump_directory}/log
exit;
fi
if [ "$except_flag" == "more" ] ; then
sed -i "s#@cur_path#$INSTALL_HOME/dbscript/impdp#g" $INSTALL_HOME/dbscript/impdp/get_fk_disable.sql
su - oracle -c "sqlplus INOC_SDM_SERVICEDESK_${sdm_number}/${DB_APP_password}@//${DB_server_ip}:${DB_Port}/${DB_instance} @$INSTALL_HOME/dbscript/impdp/get_fk_disable.sql;"
while read fk_low_line ; do
if [ "x${fk_low_line}" != "x" ] ; then
fk_low=`echo $fk_low_line | grep [a-z] | awk '{print $NF }' `
if [ "x${fk_low}" != "x" ] ; then
fk_low=`echo ${fk_low%;*}`
fk_low_replace=\"$fk_low\"
sed -i "s#${fk_low}#${fk_low_replace}#g" $INSTALL_HOME/dbscript/impdp/set_fk_disable.sql
fi
fi
done < $INSTALL_HOME/dbscript/impdp/set_fk_disable.sql
su - oracle -c "sqlplus INOC_SDM_SERVICEDESK_${sdm_number}/${DB_APP_password}@//${DB_server_ip}:${DB_Port}/${DB_instance} <<EOF
@$INSTALL_HOME/dbscript/impdp/set_fk_disable.sql;
quit;
EOF";
echo "impdp ${DB_System_username}/${DB_System_password}@//${DB_server_ip}:${DB_Port}/${DB_instance} DIRECTORY=dumpdir parallel=4 ENCRYPTION_PASSWORD=${DB_wallet_password} logfile=inoc_sdm_servicedesk_${sdm_number}_more.log DUMPFILE=inoc_sdm_servicedesk_${sdm_number}_%u.dump SCHEMAS=inoc_sdm_servicedesk_${sdm_number} content=metadata_only TABLE_EXISTS_ACTION=REPLACE INCLUDE=TABLE:\"IN\($table_list\)\" ;"
base_tablelist=""
remap_tablelist=""
echo "" > $INSTALL_HOME/dbscript/impdp/insert_to_backup_table.sql
echo "" > $INSTALL_HOME/dbscript/impdp/drop_table.sql
echo "" > $INSTALL_HOME/dbscript/impdp/alter_table_name.sql
while read TableName ; do
if [ "x${TableName}" != "x" ] ; then
echo "insert into ${TableName}_B select * from ${TableName};" >> $INSTALL_HOME/dbscript/impdp/insert_to_backup_table.sql
echo "commit;" >> $INSTALL_HOME/dbscript/impdp/insert_to_backup_table.sql
echo "drop table ${TableName} cascade constraints;" >> $INSTALL_HOME/dbscript/impdp/drop_table.sql
echo "ALTER TABLE ${TableName}_B RENAME TO ${TableName};" >> $INSTALL_HOME/dbscript/impdp/alter_table_name.sql
su - oracle -c "impdp INOC_SDM_SERVICEDESK_${sdm_number}/${DB_APP_password}@//${DB_server_ip}:${DB_Port}/${DB_instance} DIRECTORY=dumpdir parallel=4 ENCRYPTION_PASSWORD=${DB_wallet_password} logfile=more_${TableName}.log DUMPFILE=inoc_sdm_servicedesk_${sdm_number}_%u.dump TABLE_EXISTS_ACTION=REPLACE TABLES=${TableName} remap_table=${TableName}:${TableName}_B ;"
#transform=segment_attributes:n
fi
done < table_list
sed -i "s#@cur_path#$INSTALL_HOME/dbscript/impdp#g" $INSTALL_HOME/dbscript/impdp/get_fk_enable.sql
su - oracle -c "sqlplus INOC_SDM_SERVICEDESK_${sdm_number}/${DB_APP_password}@//${DB_server_ip}:${DB_Port}/${DB_instance} @$INSTALL_HOME/dbscript/impdp/get_fk_enable.sql;"
while read fk_low_line ; do
if [ "x${fk_low_line}" != "x" ] ; then
fk_low=`echo $fk_low_line | grep [a-z] | awk '{print $NF }' `
if [ "x${fk_low}" != "x" ] ; then
fk_low=`echo ${fk_low%;*}`
fk_low_replace=\"$fk_low\"
sed -i "s#${fk_low}#${fk_low_replace}#g" $INSTALL_HOME/dbscript/impdp/set_fk_enable.sql
fi
fi
done < $INSTALL_HOME/dbscript/impdp/set_fk_enable.sql
su - oracle -c "sqlplus INOC_SDM_SERVICEDESK_${sdm_number}/${DB_APP_password}@//${DB_server_ip}:${DB_Port}/${DB_instance} <<EOF
@$INSTALL_HOME/dbscript/impdp/set_fk_enable.sql;
quit;
EOF";
if [ ! -d ${dump_directory}/log ];then
mkdir ${dump_directory}/log
chmod -R 777 ${dump_directory}/log
fi
mv ${dump_directory}/*.log -t ${dump_directory}/log
exit;
fi
fi
#先删除用户
arr_user=(${user_list//,/ })
schemalist="";
for user_name in ${arr_user[@]}
do
cp -avx ./template/impdp/drop_user.sql ./dbscript/impdp
sed -i "s/@username/${user_name}/g" ./dbscript/impdp/drop_user.sql
su - oracle -c "sqlplus ${DB_System_username}/${DB_System_password}@//${DB_server_ip}:${DB_Port}/${DB_instance} @$INSTALL_HOME/dbscript/impdp/drop_user.sql >${dump_directory}/drop_${user_name}.log"
if [ $? -ne 0 ]; then
writeLog $log_file_name "Can not drop user ${user_name}" $log_path
exit 1;
fi
RESULT=`cat ${dump_directory}/drop_${user_name}.log |grep ORA-01940`
if [ "$RESULT" ]
then
writeLog $log_file_name "Can not drop user ${user_name} that is currently connected, please check !" $log_path
exit 1;
fi
if [ "x${user_name}" != "x" ] ; then
schema_name=`echo ${user_name} | tr 'a-z' 'A-Z'`
schemalist=\\\'${schema_name}\\\'\\\,${schemalist}
fi
done
#存在full.dump
if [ -f ${dump_directory}/full.dump ]
then
#直接用全库meta恢复表空间和用户
su - oracle -c "impdp ${DB_System_username}/${DB_System_password}@//${DB_server_ip}:${DB_Port}/${DB_instance} DIRECTORY=dumpdir parallel=4 ENCRYPTION_PASSWORD=${DB_wallet_password} logfile=impdp_full_metadata.log DUMPFILE=full.dump full=y content=metadata_only TABLE_EXISTS_ACTION=REPLACE EXCLUDE=SCHEMA:\"IN\($sys_schemalist\)\" ; ";
#INCLUDE=SCHEMA:\"IN\($schemalist\)\"
RESULT=`cat ${dump_directory}/impdp_full_metadata.log | grep ERROR | grep -i ORA- | grep -v -E ${except_ora_err}`
if [ "$RESULT" ];
then
writeLog $log_file_name "import impdp_full_metadata dump Faild, please check ${dump_directory}/impdp_full_metadata.log" $log_path
exit 1;
fi
if [ -f ${dump_directory}/full_most.dump ]
then
#先删除MOSTCMDB用户
cp -avx ./template/impdp/drop_user.sql ./dbscript/impdp
sed -i "s/@username/MOSTCMDB_${sdm_number}/g" ./dbscript/impdp/drop_user.sql
su - oracle -c "sqlplus ${DB_System_username}/${DB_System_password}@//${DB_server_ip}:${MOST_DB_listen_port}/${MOST_instance_name} @$INSTALL_HOME/dbscript/impdp/drop_user.sql >${dump_directory}/drop_MOSTCMDB_${sdm_number}.log"
if [ $? -ne 0 ]; then
writeLog $log_file_name "Can not drop user MOSTCMDB_${sdm_number}" $log_path
exit 1;
fi
RESULT=`cat ${dump_directory}/drop_MOSTCMDB_${sdm_number}.log |grep ORA-01940`
if [ "$RESULT" ]
then
writeLog $log_file_name "Can not drop user MOSTCMDB_${sdm_number} that is currently connected, please check !" $log_path
exit 1;
fi
schemalist=\\\'MOSTCMDB_${sdm_number}\\\'\\\,${schemalist}
schemalist=`echo ${schemalist%,*}`
#直接用全库meta恢复most表空间和用户
su - oracle -c "impdp ${DB_System_username}/${DB_System_password}@//${DB_server_ip}:${MOST_DB_listen_port}/${MOST_instance_name} DIRECTORY=dumpdir parallel=4 ENCRYPTION_PASSWORD=${DB_wallet_password} logfile=impdp_full_most_metadata.log DUMPFILE=full_most.dump full=y content=metadata_only TABLE_EXISTS_ACTION=REPLACE EXCLUDE=SCHEMA:\"IN\($sys_schemalist\)\"; ";
#INCLUDE=SCHEMA:\"IN\($schemalist\)\"
RESULT=`cat ${dump_directory}/impdp_full_most_metadata.log | grep ERROR | grep -i ORA- | grep -v -E ${except_ora_err}`
if [ "$RESULT" ];
then
writeLog $log_file_name "import impdp_full_most_metadata dump Faild, please check ${dump_directory}/impdp_full_most_metadata.log" $log_path
exit 1;
fi
fi