#!/bin/bash -x
. ~/.bash_profile

export DATE=$(date +%d-%m-%Y)

echo `date`
echo "Script de carga de base Teste"
echo
echo "Parando listener e recriando usuarios"

sudo -u oracle

lsnrctl stop

sqlplus / as sysdba <<EOF
alter user lidera account lock;
alter user sapiens account lock;
alter user vetorh account lock;
alter user vetbos account lock;
alter user jira8 account lock;
alter user secullum account lock;
alter user secullumlince account lock;
alter user secullumweb account lock;
alter user secullumlimger account lock;

shutdown immediate
startup

drop user LIDERA cascade;
drop user SAPIENS cascade;
drop user VETORH cascade;
drop user VETBOS cascade;
drop user JIRA8 cascade;
drop user SECULLUM cascade;
drop user SECULLUMLINCE cascade;
drop user SECULLUMWEB cascade;
drop user SECULLUMLIMGER cascade;
-- drop user JIRA cascade;
-- drop user BIGBET cascade;
-- drop user SUSTENTA cascade;
drop user SDE cascade;
-- drop user VETEBTEL cascade;

---- Create the user
create user LIDERA
  default tablespace LIDERA
  temporary tablespace TEMP
  profile DEFAULT
  IDENTIFIED BY "lidera2017";
---- Grant/Revoke role privileges
grant connect to LIDERA;
grant dba to LIDERA;
grant exp_full_database to LIDERA;
grant imp_full_database to LIDERA;
grant resource to LIDERA;
---- Grant/Revoke system privileges 
grant alter any sequence to LIDERA;
grant analyze any to LIDERA;
grant create any sequence to LIDERA;
grant create any view to LIDERA;
grant create database link to LIDERA;
grant create public database link to LIDERA;
grant create session to LIDERA;
grant delete any table to LIDERA;
grant execute any procedure to LIDERA;
grant grant any privilege to LIDERA;
grant insert any table to LIDERA;
grant select any dictionary to LIDERA;
grant select any table to LIDERA;
grant unlimited tablespace to LIDERA;
grant update any table to LIDERA;

---- Create the user 
create user SAPIENS
  default tablespace SAPIENS
  temporary tablespace TEMP
  profile DEFAULT
  IDENTIFIED BY "lidera2017";
---- Grant/Revoke role privileges 
grant connect to SAPIENS;
grant dba to SAPIENS;
grant exp_full_database to SAPIENS;
grant imp_full_database to SAPIENS;
grant resource to SAPIENS;
---- Grant/Revoke system privileges 
grant alter any sequence to SAPIENS;
grant analyze any to SAPIENS;
grant create any sequence to SAPIENS;
grant create any view to SAPIENS;
grant create database link to SAPIENS;
grant create public database link to SAPIENS;
grant create session to SAPIENS;
grant delete any table to SAPIENS;
grant execute any procedure to SAPIENS;
grant grant any privilege to SAPIENS;
grant insert any table to SAPIENS;
grant select any dictionary to SAPIENS;
grant select any table to SAPIENS;
grant unlimited tablespace to SAPIENS;
grant update any table to SAPIENS;

---- Create the user 
create user VETORH
  default tablespace VETORH
  temporary tablespace TEMP
  profile DEFAULT
  IDENTIFIED BY "lidera2017";
---- Grant/Revoke role privileges 
grant connect to VETORH;
grant dba to VETORH;
grant exp_full_database to VETORH;
grant imp_full_database to VETORH;
grant resource to VETORH;
---- Grant/Revoke system privileges 
grant alter any sequence to VETORH;
grant analyze any to VETORH;
grant create any sequence to VETORH;
grant create any view to VETORH;
grant delete any table to VETORH;
grant drop any table to VETORH;
grant execute any procedure to VETORH;
grant grant any privilege to VETORH;
grant insert any table to VETORH;
grant select any dictionary to VETORH;
grant select any table to VETORH;
grant unlimited tablespace to VETORH;
grant update any table to VETORH;

CREATE OR REPLACE DIRECTORY dir_expdp AS '/home/backup/script';
EOF
echo `date`
echo "Iniciando listener"
lsnrctl start
echo `date`
echo "Importando dados"
# Modificado por Ivan - Carga realizada diretamente da producao utilizando NETWORK_LINK
impdp "'"SYS/lidera as sysdba"'" NETWORK_LINK=PROD_DATA_PUMP PARALLEL=4  SCHEMAS=LIDERA,SAPIENS,VETORH DIRECTORY=dir_expdp LOGTIME=all METRICS=y LOGFILE=log_orateste-$DATE.log EXCLUDE=STATISTICS

#impdp "'"SYS/lidera as sysdba"'" SCHEMAS=LIDERA,SAPIENS,VETORH,VETBOS,JIRA,SECULLUM DIRECTORY=dir_expdp DUMPFILE=expdp_full-$DATE.dmp LOGTIME=all METRICS=y LOGFILE=log_orarel-$DATE.log TRANSFORM=disable_archive_logging:y
#impdp "'"SYS/lidera as sysdba"'" schemas=SAPIENS,VETORH,LIDERA,SDE,JIRA,VETBOS,VETEBTEL,LIFERAY,BIGBET DIRECTORY=dir_expdp PARALLEL=8 DUMPFILE=expdp_full-$DATE.dmp LOGFILE=log_orarel-$DATE.log
#impdp "'"SYS/lidera as sysdba"'" DIRECTORY=dir_expdp DUMPFILE=expdp_full-$DATE.dmp FULL=y

echo `date`
echo "Truncando tabelas e atualizando dados"
sqlplus -s / as sysdba <<EOF
truncate table vetorh.r900log;
update vetorh.r030emp set cfgsap = ' ', intsap = 2, arqins = ' ',  cfgint = ' ';
commit;
update vetorh.r900gpr set prmval = null where prmid = 33;
commit;
update vetorh.r999age set enable = 'F' where 1 = 1;
commit;
update sapiens.r999age set enable = 'F' where 1 = 1;
commit;
EOF
echo `date`
echo "Coletando estatisticas"
sqlplus -s / as sysdba <<EOF
exec dbms_stats.create_stat_table@PROD_DATA_PUMP( ownname => 'SYSTEM', stattab => 'DB_STATS');
exec dbms_stats.export_database_stats@PROD_DATA_PUMP(statown => 'SYSTEM', stattab => 'DB_STATS', stat_category => 'OBJECT_STATS');
create table system.db_stats as select * from system.db_stats@PROD_DATA_PUMP;
exec dbms_stats.drop_stat_table@PROD_DATA_PUMP( ownname => 'SYSTEM', stattab => 'DB_STATS');
exec dbms_stats.import_database_stats(statown => 'SYSTEM', stattab => 'DB_STATS');
drop table system.db_stats;
EOF
echo `date`
echo "Reiniciando listener"
lsnrctl stop
lsnrctl start
lsnrctl stop
lsnrctl start
echo `date`
echo "Script finalidado"