forked from dwheltzel/Shell-Scripts-for-Oracle-DBAs
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathflashback_db.sh
More file actions
317 lines (298 loc) · 8.98 KB
/
flashback_db.sh
File metadata and controls
317 lines (298 loc) · 8.98 KB
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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
#!/bin/bash
#Description: This script is a wrapper that utilizes Oracle Database Flashback Log features for portal releases
#Requirements: The script should be run as software owner on the oracle db server. RAC is assumed.
#Input: Update the dblist parameter below to specify list of databases for flashback ops.
# Value is single space separated list of db names(not instance name).
#
# Modified $Author: dheltzel $
# Date $Date: 2014-02-04 20:33:07 +0000 (Tue, 04 Feb 2014) $
#Estimate fra storage room required to retain flashback and archivelogs for $numdays days
numdays=8
#Check the awr for $trenddays no. of days to figure out approx transaction activity per day
trenddays=7
#Databases for flashback operations
dblist="stvpn2 stedw stp03 staglk"
#sqlplus commands for each database
sqlpluscmd="sqlplus -SL / as sysdba"
usage() {
echo "Usage: $0 [size|on|rollback|off|savestate|reset|check]"
echo " size - get size estimate for FRA"
echo " on - sets a guaranteed restore point"
echo " rollback - rollback to restore point"
echo " off - remotes the guaranteed restore point"
echo " savestate - capture db logical state"
echo " reset - drop any existing restore point and set a new one"
echo " check - report on the restore point status"
exit 1
}
checkflashbackenabled(){
#Not implemented
q="select flashback_on from v\$database";
}
checkflashbacksizing(){
echo "1. Estimated Flashback logs size for $numdays days"
numdays=$1
trenddays=$2
$3 $4 $5 $6 $7 <<EOF
set heading off feedback off pages 0
select round(avg(v1.csredo-v2.csredo))*$numdays||' MB' avgredosizemb
from
(
select btime,round(sum(credo)/1024/1024) csredo
from(
select trunc(a.begin_interval_time) btime,max(b.value) credo
from dba_hist_sysstat b, dba_hist_snapshot a
where a.snap_id=b.snap_id and a.instance_number=b.instance_number
and a.begin_interval_time>sysdate-$trenddays and b.stat_name='redo size'
group by trunc(a.begin_interval_time), a.instance_number)
group by btime ) v1,
(
select btime,round(sum(credo)/1024/1024) csredo
from(
select trunc(a.begin_interval_time) btime,max(b.value) credo
from dba_hist_sysstat b, dba_hist_snapshot a
where a.snap_id=b.snap_id and a.instance_number=b.instance_number
and a.begin_interval_time>sysdate-$trenddays and b.stat_name='redo size'
group by trunc(a.begin_interval_time), a.instance_number)
group by btime ) v2
where v1.btime=v2.btime+1;
exit
EOF
}
checkflashbackfreespacerequirement(){
echo "2. Estimated amount of free space required in FRA for $numdays days"
numdays=$1
trenddays=$2
$3 $4 $5 $6 $7 <<EOF
set heading off feedback off pages 0
select round(avg(v1.csredo-v2.csredo))*$numdays*2||' MB' avgredosizemb
from
(
select btime,round(sum(credo)/1024/1024) csredo
from(
select trunc(a.begin_interval_time) btime,max(b.value) credo
from dba_hist_sysstat b, dba_hist_snapshot a
where a.snap_id=b.snap_id and a.instance_number=b.instance_number
and a.begin_interval_time>sysdate-$trenddays and b.stat_name='redo size'
group by trunc(a.begin_interval_time), a.instance_number)
group by btime ) v1,
(
select btime,round(sum(credo)/1024/1024) csredo
from(
select trunc(a.begin_interval_time) btime,max(b.value) credo
from dba_hist_sysstat b, dba_hist_snapshot a
where a.snap_id=b.snap_id and a.instance_number=b.instance_number
and a.begin_interval_time>sysdate-$trenddays and b.stat_name='redo size'
group by trunc(a.begin_interval_time), a.instance_number)
group by btime ) v2
where v1.btime=v2.btime+1;
exit
EOF
}
checkfrafreespace(){
echo "3. Check current free space in FRA"
$1 $2 $3 $4 $5 <<EOF
set heading off feedback off pagesize 0
select round((100-usedspace)*frasize/1024/1024/100)||' MB' flashbackfreespaceMB
from
(select round(sum(PERCENT_SPACE_USED)-sum(percent_space_reclaimable)) usedspace from v\$recovery_area_usage),
(select value frasize from v\$parameter where name='db_recovery_file_dest_size');
exit
EOF
}
enableflashback(){
$1 $2 $3 $4 $5 <<EOF
create restore point before_changes guarantee flashback database;
exit
EOF
}
stopflashback(){
$1 $2 $3 $4 $5 <<EOF
drop restore point before_changes;
exit
EOF
}
reset_restorepoint(){
$1 $2 $3 $4 $5 <<EOF
drop restore point before_changes;
create restore point before_changes guarantee flashback database;
exit
EOF
}
checkrestorepoint(){
$1 $2 $3 $4 $5 <<EOF
SET PAGES 0
SELECT sys_context('USERENV', 'DB_NAME')||': Restore point '||name||' set at '||time FROM v\$restore_point;
exit
EOF
}
spoollogicalstate(){
echo "Saving report.."
$1 $2 $3 $4 $5 <<EOF
set termout on heading off feedback off
select 'dbflashback_'||name||'_'||to_char(sysdate,'yyyymmddhh24miss')||'.txt' reportname1
from v\$database;
exit
EOF
$1 $2 $3 $4 $5 <<EOF 1> /dev/null
set termout off
col reportname1 new_value reportname
select 'dbflashback_'||name||'_'||to_char(sysdate,'yyyymmddhh24miss')||'.txt' reportname1
from v\$database;
spool &reportname
set lines 200 pages 0 trimspool on
prompt Object counts
select count(1) from dba_objects;
prompt Invalid objects(object_name,owner,object_type)
col object_name for a30
select object_name,owner,object_type from dba_objects where status!='VALID' order by owner,object_name,object_type;
prompt Errors(owner,name,text)
col text for a100
select owner,name,text from dba_errors order by owner,name;
prompt Sequences(owner,name,lastnumber)
select sequence_owner,sequence_name,last_number from dba_sequences where sequence_owner not in ('SYS','SYSTEM','DBSNMP') order by 1,2;
col username for a15
col module for a30
col machine for a30
prompt Connections(user,module,machine)
select username,substr(module||':'||program,1,30) module,machine from gv\$session where type='USER' order by username,module;
spool off
exit
EOF
}
flashbackdb(){
db=$(dbname $1 $2 $3 $4 $5)
$1 $2 $3 $4 $5 <<EOF
prompt Disable job queue..
alter system set job_queue_processes=0 scope=both sid='*';
prompt Stop shared servers..
alter system set shared_servers=0 scope=memory sid='*';
EOF
echo "Stopping db.. " $db
srvctl stop database -d $db -o immediate
$1 $2 $3 $4 $5 <<EOF
startup mount
flashback database to restore point before_changes;
alter database open resetlogs;
shutdown immediate
EOF
echo "Starting db.. " $db
srvctl start database -d $db
$1 $2 $3 $4 $5 <<EOF
prompt Enable job queue..
alter system set job_queue_processes=10 scope=both sid='*';
EOF
}
# Get sizing requirements for FRA
getsizing(){
checkflashbacksizing $1 $2 $3 $4 $5 $6 $7
checkflashbackfreespacerequirement $1 $2 $3 $4 $5 $6 $7
checkfrafreespace $3 $4 $5 $6 $7
}
dbname(){
$1 $2 $3 $4 $5 <<EOF
set heading off feedback off pagesize 0
select name from v\$database;
exit
EOF
}
case $1 in
size)
for i in $dblist; do
oid=$(ps -ef|grep pmon|grep $i|grep -v grep|cut -d'_' -f3)
export ORACLE_SID=$oid
if [ -n "$ORACLE_SID" ]; then
echo "---------------------------------------------"
dbname $sqlpluscmd
getsizing $numdays $trenddays $sqlpluscmd
else
echo "DB $i not running"
fi
done
;;
on)
echo "Turn on flashback"
for i in $dblist; do
oid=$(ps -ef|grep pmon|grep $i|grep -v grep|cut -d'_' -f3)
export ORACLE_SID=$oid
if [ -n "$ORACLE_SID" ]; then
echo "---------------------------------------------"
dbname $sqlpluscmd
enableflashback $sqlpluscmd
else
echo "DB $i not running"
fi
done
;;
off)
echo "Turn off flashback"
for i in $dblist; do
oid=$(ps -ef|grep pmon|grep $i|grep -v grep|cut -d'_' -f3)
export ORACLE_SID=$oid
if [ -n "$ORACLE_SID" ]; then
echo "---------------------------------------------"
dbname $sqlpluscmd
stopflashback $sqlpluscmd
else
echo "DB $i not running"
fi
done
;;
savestate)
echo "Spooling out db state"
for i in $dblist; do
oid=$(ps -ef|grep pmon|grep $i|grep -v grep|cut -d'_' -f3)
export ORACLE_SID=$oid
if [ -n "$ORACLE_SID" ]; then
echo "---------------------------------------------"
dbname $sqlpluscmd
spoollogicalstate $sqlpluscmd
else
echo "DB $i is not running"
fi
done
;;
rollback)
echo "Do rollback to restore point"
for i in $dblist; do
oid=$(ps -ef|grep pmon|grep $i|grep -v grep|cut -d'_' -f3)
export ORACLE_SID=$oid
if [ -n "$ORACLE_SID" ]; then
echo "---------------------------------------------"
dbname $sqlpluscmd
flashbackdb $sqlpluscmd
else
echo "DB $i not running"
fi
done
;;
reset)
echo "Reset the restore point"
for i in $dblist; do
oid=$(ps -ef|grep pmon|grep $i|grep -v grep|cut -d'_' -f3)
export ORACLE_SID=$oid
if [ -n "$ORACLE_SID" ]; then
echo "---------------------------------------------"
dbname $sqlpluscmd
reset_restorepoint $sqlpluscmd
else
echo "DB $i not running"
fi
done
;;
check)
echo "Report on the restore points"
for i in $dblist; do
oid=$(ps -ef|grep pmon|grep $i|grep -v grep|cut -d'_' -f3)
export ORACLE_SID=$oid
if [ -n "$ORACLE_SID" ]; then
checkrestorepoint $sqlpluscmd
else
echo "DB $i not running"
fi
done
;;
*)
usage
;;
esac