Auto refresh for ERP

#!/bin/sh
#####################################################
# script to take a rman full restore
# this script must be run
# as the trusted user 'oracle10g' 'oracle9i' etc
#####################################################
# create output logs with that number AND THE CURRENT DATE in the ./rman/logs directory
# age out in 14-30 days
# get env IMPORTANT, if multiple Oracle SID's the env var ORACLE_SID must be set
. /u01/oramis/db/tech_st/11.2.0/ERPMIS_server528.env
#ORACLE_BASE=/oracle/; export ORACLE_BASE
#ORACLE_HOME=$ORACLE_BASE/10.2.0/products/db; export ORACLE_HOME
#ORACLE_SID=ERPMIS; export ORACLE_SID
#LD_LIBRARY_PATH=/oracle/10.2.0/products/crs/lib:/oracle/10.2.0/products/crs/lib32; export LD_LIBRARY_PATH
#HEAD=/u01/oramis/
#WORK=$HEAD/rman/scripts
#cd $WORK

var11=`sqlplus -s / as sysdba<shutdown abort;

startup nomount;
EOF`

cd /u01/oramis/apps_st/data

rm -rf /u01/oramis/apps_st/data/*
cd /u01/oramis/db/tech_st/11.2.0/dbs

rm -rf arch1_*.dbf

var1=`sqlplus -s rman/rman@rmancat<select MIN(HANDLE) from bp where db_key=145140 and completion_time>=sysdate-1 and handle like 'c-%';
EOF`

var1=$(echo $var1|awk '{print $3}')
echo $var1

var2=`sqlplus -s rman/rman@rmancat<select TO_CHAR(completion_time,'DD/MON/YYYY HH24:MI:SS') DATE_TIME from bp where db_key=145140 and completion_time>=sysdate-1 and handle='$var1' ;
EOF`

var2=$(echo $var2|awk '{print $3 " " $4}')
echo $var2

var3=`rman target / catalog rman/rman@rmancat<run
{
allocate channel t1 type 'sbt_tape'
parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
restore controlfile from '$var1';
alter database mount;
}


run {
set until time "TO_DATE('$var2','DD/MON/YYYY HH24:MI:SS')";
allocate channel t1 type 'sbt_tape'
parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel t2 type 'sbt_tape'
parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel t3 type 'sbt_tape'
parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel t4 type 'sbt_tape'
parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
set newname for  datafile 1 to '/u01/oramis/apps_st/data/system01.dbf';
set newname for  datafile 2 to '/u01/oramis/apps_st/data/system02.dbf';
set newname for  datafile 3 to '/u01/oramis/apps_st/data/system03.dbf';
set newname for  datafile 4 to '/u01/oramis/apps_st/data/system04.dbf';
set newname for  datafile 5 to '/u01/oramis/apps_st/data/system05.dbf';
set newname for  datafile 6 to '/u01/oramis/apps_st/data/ctxd01.dbf';
set newname for  datafile 7 to '/u01/oramis/apps_st/data/owad01.dbf';
set newname for  datafile 8 to '/u01/oramis/apps_st/data/a_queue02.dbf';
set newname for  datafile 9 to '/u01/oramis/apps_st/data/odm.dbf';
set newname for  datafile 10 to '/u01/oramis/apps_st/data/olap.dbf';
set newname for  datafile 11 to '/u01/oramis/apps_st/data/sysaux01.dbf';
set newname for  datafile 12 to '/u01/oramis/apps_st/data/apps_ts_tools01.dbf';
set newname for  datafile 13 to '/u01/oramis/apps_st/data/system12.dbf';
set newname for  datafile 14 to '/u01/oramis/apps_st/data/a_txn_data04.dbf';
set newname for  datafile 15 to '/u01/oramis/apps_st/data/a_txn_ind06.dbf';
set newname for  datafile 16 to '/u01/oramis/apps_st/data/a_ref03.dbf';
set newname for  datafile 17 to '/u01/oramis/apps_st/data/a_int02.dbf';
set newname for  datafile 18 to '/u01/oramis/apps_st/data/sysaux02.dbf';
set newname for  datafile 19 to '/u01/oramis/apps_st/data/users01.dbf';
set newname for  datafile 20 to '/u01/oramis/apps_st/data/a_txn_data05.dbf';
set newname for  datafile 21 to '/u01/oramis/apps_st/data/a_media02.dbf';
set newname for  datafile 22 to '/u01/oramis/apps_st/data/a_txn_data06.dbf';
set newname for  datafile 23 to '/u01/oramis/apps_st/data/a_txn_data07.dbf';
set newname for  datafile 24 to '/u01/oramis/apps_st/data/a_txn_data08.dbf';
set newname for  datafile 25 to '/u01/oramis/apps_st/data/a_txn_data09.dbf';
set newname for  datafile 26 to '/u01/oramis/apps_st/data/a_txn_data10.dbf';
set newname for  datafile 27 to '/u01/oramis/apps_st/data/a_txn_data11.dbf';
set newname for  datafile 28 to '/u01/oramis/apps_st/data/a_txn_data12.dbf';
set newname for  datafile 29 to '/u01/oramis/apps_st/data/a_txn_data13.dbf';
set newname for  datafile 30 to '/u01/oramis/apps_st/data/a_media03.dbf';
set newname for  datafile 288 to '/u01/oramis/apps_st/data/system10.dbf';
set newname for  datafile 295 to '/u01/oramis/apps_st/data/system06.dbf';
set newname for  datafile 314 to '/u01/oramis/apps_st/data/portal01.dbf';
set newname for  datafile 351 to '/u01/oramis/apps_st/data/system07.dbf';
set newname for  datafile 352 to '/u01/oramis/apps_st/data/system09.dbf';
set newname for  datafile 353 to '/u01/oramis/apps_st/data/system08.dbf';
set newname for  datafile 354 to '/u01/oramis/apps_st/data/system11.dbf';
set newname for  datafile 379 to '/u01/oramis/apps_st/data/undo01.dbf';
set newname for  datafile 392 to '/u01/oramis/apps_st/data/a_txn_data01.dbf';
set newname for  datafile 393 to '/u01/oramis/apps_st/data/a_txn_ind01.dbf';
set newname for  datafile 394 to '/u01/oramis/apps_st/data/a_ref01.dbf';
set newname for  datafile 395 to '/u01/oramis/apps_st/data/a_int01.dbf';
set newname for  datafile 396 to '/u01/oramis/apps_st/data/a_summ01.dbf';
set newname for  datafile 397 to '/u01/oramis/apps_st/data/a_nolog01.dbf';
set newname for  datafile 398 to '/u01/oramis/apps_st/data/a_archive01.dbf';
set newname for  datafile 399 to '/u01/oramis/apps_st/data/a_queue01.dbf';
set newname for  datafile 400 to '/u01/oramis/apps_st/data/a_media01.dbf';
set newname for  datafile 401 to '/u01/oramis/apps_st/data/a_txn_data02.dbf';
set newname for  datafile 402 to '/u01/oramis/apps_st/data/a_txn_data03.dbf';
set newname for  datafile 403 to '/u01/oramis/apps_st/data/a_txn_ind02.dbf';
set newname for  datafile 404 to '/u01/oramis/apps_st/data/a_txn_ind03.dbf';
set newname for  datafile 405 to '/u01/oramis/apps_st/data/a_txn_ind04.dbf';
set newname for  datafile 406 to '/u01/oramis/apps_st/data/a_txn_ind05.dbf';
set newname for  datafile 407 to '/u01/oramis/apps_st/data/a_ref02.dbf';
restore database;
switch datafile all;
recover database;
}

EOF`

var4=`sqlplus -s / as sysdba<alter database rename file '/d04/PROD/data/log02a.dbf' to '/u01/oramis/apps_st/data/log02a.dbf';
alter database rename file '/d04/PROD/data/log02b.dbf' to '/u01/oramis/apps_st/data/log02b.dbf';
alter database rename file '/d04/PROD/data/log01a.dbf' to '/u01/oramis/apps_st/data/log01a.dbf';
alter database rename file '/d04/PROD/data/log01b.dbf' to '/u01/oramis/apps_st/data/log01b.dbf';
alter database open resetlogs;

alter tablespace TEMP1 add tempfile '/u01/oramis/apps_st/data/temp1.dbf' SIZE  2G REUSE AUTOEXTEND OFF;

alter tablespace TEMP2 add tempfile '/u01/oramis/apps_st/data/temp2.dbf' SIZE  2G REUSE AUTOEXTEND OFF;


alter tablespace TEMP1 drop tempfile '/d04/PROD/data/temp01.dbf';

alter tablespace TEMP2 drop tempfile '/d04/PROD/data/temp02.dbf';

shutdown immediate;
startup mount;
alter database noarchivelog;
alter database open;

alter user apps identified by apps;

conn apps/apps;

Update applsys.fnd_concurrent_requests set PHASE_CODE='C', STATUS_CODE='D' where phase_code in ('P','R','I');

update fnd_profile_option_values set profile_option_value ='ERPMIS Clone of PROD' where PROFILE_OPTION_ID=125;

EXEC FND_CONC_CLONE.SETUP_CLEAN;

conn / as sysdba

alter user sys identified by manager;
alter user system identified by manager;

DROP PUBLIC DATABASE LINK "ERP_JBL_DBLINK.ALHILALBANK.AE";

commit;

EOF`

echo $var11;

echo $var1;

echo $var2

echo $var3

echo $var4

exit