Bash-Script/DBRollback.sh at main · vikassharmapunj/Bash-Script · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
#!/bin/bash -x
#################################
# This Script is to Rollback 	#
# Database to previous TAG		#
# in case of any error			#
# Date 27th February 2018		#
# Version 1.0					#
#################################

export ORACLE_HOME=/home/u01/app/oracle/product/12.1.0/dbhome_1;
export PATH=$ORACLE_HOME/bin:/usr/bin:/usr/local/bin:/bin;

### Drop object of Destination Schema ###
User_Destination=$1
DestinationIP=$2
DestinationSID=$3
TAG=$4
Destination_Password='spice#123'

/usr/local/bin/sqlplus -s $User_Destination/$Destination_Password@$DestinationIP/$DestinationSID <<EOF
BEGIN
   FOR cur_rec IN (SELECT object_name, object_type
                     FROM user_objects
                    WHERE object_type IN
                             ('TABLE',
                              'TYPE',
                              'VIEW',
                              'MATERIALIZED VIEW',
                              'PACKAGE',
                              'PROCEDURE',
                              'FUNCTION',
                              'SEQUENCE'
                             ))
   LOOP
      BEGIN
         IF cur_rec.object_type = 'TABLE'
         THEN
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '" CASCADE CONSTRAINTS';
         ELSE
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '"';
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   'FAILED: DROP '
                                  || cur_rec.object_type
                                  || ' "'
                                  || cur_rec.object_name
                                  || '"'
                                 );
      END;
   END LOOP;
END;
/
exit;
EOF

impdp system/Spice_123@$DestinationIP/$DestinationSID schemas=$User_Destination directory=DATA_PUMP_DIR dumpfile=DataBaseDump_using_build_$TAG.dmp logfile=ROLLBACK_DataBaseDump_using_build_$TAG.log