DM7&DM8数据对象校验shell脚本

1.ORA-DM数据迁移后，数据对象校验shell脚本。

  1 #!/bin/bash
  2 ### --note1: select OBJECT_TYPE,COUNT(*) from all_objects where OWNER='SYSDBA' GROUP BY OBJECT_TYPE;
  3 ### --note2： detail_pk,detail_fk,detail_ck,detail_unique,detail_idx
  4 
  5 ##源库信息设置：
  6 #~ vs_tab_num=4
  7 #~ vs_cons_num=4
  8 #~ vs_view_num=4
  9 #~ vs_proc_num=4
 10 #~ vs_fun_num=4
 11 #~ vs_seq_num=4
 12 #~ vs_tri_num=4
 13 #~ vs_pkg_num=4
 14 #~ vs_syn_num=4
 15 #~ vs_idx_num=4
 16 #~ vd_tab_num=4
 17 
 18 v_user="SYSDBA"
 19 v_dm_conn="SYSDBA/SYSDBA@192.168.204.129:5236"
 20 v_ora_conn="SYSDBA/SYSDBA@192.168.204.129:5236"
 21 #~ v_db_link="@LINK01"
 22 v_db_link=""
 23 
 24 detail_dir="./detail/"
 25 temp_dir="./temp/"
 26 log="dbcheck.log"
 27 
 28 
 29 ########################################################################
 30 ########################---1.before online---###########################
 31 #1.1 table num
 32 echo "select count(*) from DBA_TABLES$v_db_link where OWNER like '$v_user' AND TABLE_NAME NOT LIKE '##%' 
 33     order by TABLE_NAME;" |disql $v_ora_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/tabnum_ora.txt
 34     
 35 echo "select count(*) from DBA_TABLES where OWNER like '$v_user' AND TABLE_NAME NOT LIKE '##%' 
 36     order by TABLE_NAME;" |disql $v_dm_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/tabnum.txt
 37 
 38 while  read  i
 39 do
 40 vs_tab_num=$( echo  "$i" | awk  -F  ' '  '{print $1}' )
 41 done <$temp_dir/tabnum_ora.txt
 42 
 43 while  read  i
 44 do
 45 vd_tab_num=$( echo  "$i" | awk  -F  ' '  '{print $1}' )
 46 done <$temp_dir/tabnum.txt
 47 
 48 if [[ $vs_tab_num = $vd_tab_num ]];then
 49     echo "same tab_num:"${vs_tab_num}
 50     echo "same tab_num:"${vs_tab_num} >$log
 51 else
 52     echo 'Not same tab_num:'${vs_tab_num}'!='${vd_tab_num}
 53     echo 'Not same tab_num:'${vs_tab_num}'!='${vd_tab_num} >$log
 54 fi
 55 
 56 
 57 #1.2 constraint (unique, check, fk, pk)
 58 echo "select COUNT(*) from dba_objects$v_db_link where OWNER='$v_user' and OBJECT_TYPE='CONSTRAINT';" \
 59     |disql $v_ora_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/consnum_ora.txt
 60 
 61 echo "select COUNT(*) from dba_objects where OWNER='$v_user' and OBJECT_TYPE='CONSTRAINT';" \
 62     |disql $v_dm_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/consnum.txt
 63 
 64 while  read  i
 65 do
 66 vs_cons_num=$( echo  "$i" | awk  -F  ' '  '{print $1}' )
 67 done <$temp_dir/consnum_ora.txt
 68          
 69 while  read  i
 70 do
 71 vd_cons_num=$( echo  "$i" | awk  -F  ' '  '{print $1}' )
 72 done <$temp_dir/consnum.txt
 73 
 74 if [[ $vs_cons_num = $vd_cons_num ]];then
 75     echo "same cons_num:"${vs_cons_num}
 76     echo "same cons_num:"${vs_cons_num} >>$log
 77 else
 78     echo 'Not same cons_num:'${vs_cons_num}'!='${vd_cons_num}
 79     echo 'Not same cons_num:'${vs_cons_num}'!='${vd_cons_num}  >>$log
 80 fi
 81 
 82 #1.2.1 constraint (pk)
 83 echo "SELECT * FROM USER_CONSTRAINTS$v_db_link WHERE CONSTRAINT_NAME IN(
 84     SELECT DISTINCT A.CONSTRAINT_NAME FROM DBA_CONSTRAINTS$v_db_link A WHERE A.OWNER ='$v_user'
 85     AND A.CONSTRAINT_TYPE='P' AND A.CONSTRAINT_NAME NOT IN(
 86     SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE OWNER ='$v_user' AND CONSTRAINT_TYPE='P'));" \
 87     |disql $v_dm_conn  > $detail_dir/pk
 88 
 89 
 90 #1.2.2 constraint (fk)
 91 echo "SELECT * FROM USER_CONSTRAINTS$v_db_link WHERE CONSTRAINT_NAME IN(
 92     SELECT DISTINCT A.CONSTRAINT_NAME FROM DBA_CONSTRAINTS$v_db_link A WHERE A.OWNER ='$v_user'
 93     AND A.CONSTRAINT_TYPE='R' AND A.CONSTRAINT_NAME NOT IN(
 94     SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE OWNER ='$v_user' AND CONSTRAINT_TYPE='R'));" \
 95     |disql $v_dm_conn  > $detail_dir/fk
 96 
 97 #1.2.3 constraint (check)
 98 echo "SELECT * FROM USER_CONSTRAINTS$v_db_link WHERE CONSTRAINT_NAME IN(
 99     SELECT DISTINCT A.CONSTRAINT_NAME FROM DBA_CONSTRAINTS$v_db_link A WHERE A.OWNER ='$v_user'
100     AND A.CONSTRAINT_TYPE='C' AND A.CONSTRAINT_NAME NOT IN(
101     SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE OWNER ='$v_user' AND CONSTRAINT_TYPE='C'));" \
102     |disql $v_dm_conn  > $detail_dir/ck
103     
104 #1.2.4 constraint (unique)
105 echo "SELECT * FROM USER_CONSTRAINTS$v_db_link WHERE CONSTRAINT_NAME IN(
106     SELECT DISTINCT A.CONSTRAINT_NAME FROM DBA_CONSTRAINTS$v_db_link A WHERE A.OWNER ='$v_user'
107     AND A.CONSTRAINT_TYPE='U' AND A.CONSTRAINT_NAME NOT IN(
108     SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE OWNER ='$v_user' AND CONSTRAINT_TYPE='U'));" \
109     |disql $v_dm_conn  > $detail_dir/unique
110 
111 
112 
113 #1.3 VIEW   (MATERIALIZED VIEW --no)
114 echo "select COUNT(*) from dba_objects$v_db_link where OWNER='$v_user' and OBJECT_TYPE='VIEW';" \
115     |disql $v_ora_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/viewnum_ora.txt
116 
117 echo "select COUNT(*) from dba_objects where OWNER='$v_user' and OBJECT_TYPE='VIEW';" \
118     |disql $v_dm_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/viewnum.txt
119    
120 while  read  i
121 do
122 vs_view_num=$( echo  "$i" | awk  -F  ' '  '{print $1}' )
123 done <$temp_dir/viewnum_ora.txt
124        
125 while  read  i
126 do
127 vd_view_num=$( echo  "$i" | awk  -F  ' '  '{print $1}' )
128 done <$temp_dir/viewnum.txt
129 
130 if [[ $vs_view_num = $vd_view_num ]];then
131     echo "same view_num:"${vs_view_num}
132     echo "same view_num:"${vs_view_num}  >>$log
133 else
134     echo 'Not same view_num:'${vs_view_num}'!='${vd_view_num}
135     echo 'Not same view_num:'${vs_view_num}'!='${vd_view_num}   >>$log
136 fi
137 
138 
139 #1.4 PROCEDURE
140 echo "select COUNT(*) from dba_objects$v_db_link where OWNER='$v_user' and OBJECT_TYPE='PROCEDURE';" \
141     |disql $v_ora_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/procnum_ora.txt
142 
143 echo "select COUNT(*) from dba_objects where OWNER='$v_user' and OBJECT_TYPE='PROCEDURE';" \
144     |disql $v_dm_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/procnum.txt
145 
146 
147 while  read  i
148 do
149 vs_proc_num=$( echo  "$i" | awk  -F  ' '  '{print $1}' )
150 done <$temp_dir/procnum_ora.txt
151     
152 while  read  i
153 do
154 vd_proc_num=$( echo  "$i" | awk  -F  ' '  '{print $1}' )
155 done <$temp_dir/procnum.txt
156 
157 if [[ $vs_proc_num = $vd_proc_num ]];then
158     echo "same proc_num:"${vs_proc_num}
159     echo "same proc_num:"${vs_proc_num}  >>$log
160 else
161     echo 'Not same proc_num:'${vs_proc_num}'!='${vd_proc_num}
162     echo 'Not same proc_num:'${vs_proc_num}'!='${vd_proc_num}   >>$log
163 fi
164 
165 
166 #1.5 函数FUNCTION，(外部函数--no)
167 echo "select COUNT(*) from dba_objects$v_db_link where OWNER='$v_user' and OBJECT_TYPE='FUNCTION';" \
168     |disql $v_ora_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/funnum_ora.txt
169     
170 echo "select COUNT(*) from dba_objects where OWNER='$v_user' and OBJECT_TYPE='FUNCTION';" \
171     |disql $v_dm_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/funnum.txt
172 
173 while  read  i
174 do
175 vs_fun_num=$( echo  "$i" | awk  -F  ' '  '{print $1}' )
176 done <$temp_dir/funnum_ora.txt
177 
178 while  read  i
179 do
180 vd_fun_num=$( echo  "$i" | awk  -F  ' '  '{print $1}' )
181 done <$temp_dir/funnum.txt
182 
183 if [[ $vs_fun_num = $vd_fun_num ]];then
184     echo "same fun_num:"${vs_fun_num}
185     echo "same fun_num:"${vs_fun_num}  >>$log
186 else
187     echo 'Not same fun_num:'${vs_fun_num}'!='${vd_fun_num}
188     echo 'Not same fun_num:'${vs_fun_num}'!='${vd_fun_num}  >>$log
189 fi
190 
191 
192 #1.6 序列,SEQUENCE
193 echo "select COUNT(*) from dba_objects$v_db_link where OWNER='$v_user' and OBJECT_TYPE='SEQUENCE';" \
194     |disql $v_ora_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/seqnum_ora.txt
195 
196 echo "select COUNT(*) from dba_objects where OWNER='$v_user' and OBJECT_TYPE='SEQUENCE';" \
197     |disql $v_dm_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/seqnum.txt
198 
199 while  read  i
200 do
201 vs_seq_num=$( echo  "$i" | awk  -F  ' '  '{print $1}' )
202 done <$temp_dir/seqnum_ora.txt
203 
204 while  read  i
205 do
206 vd_seq_num=$( echo  "$i" | awk  -F  ' '  '{print $1}' )
207 done <$temp_dir/seqnum.txt
208 
209 if [[ $vs_seq_num = $vd_seq_num ]];then
210     echo "same seq_num:"${vs_seq_num}
211     echo "same seq_num:"${vs_seq_num}  >>$log
212 else
213     echo 'Not same seq_num:'${vs_seq_num}'!='${vd_seq_num}
214     echo 'Not same seq_num:'${vs_seq_num}'!='${vd_seq_num}   >>$log
215 fi
216 
217 
218 #1.7 TRIGGER + trigger off
219 echo "select COUNT(*) from dba_objects$v_db_link where OWNER='$v_user' and OBJECT_TYPE='TRIGGER';" \
220     |disql $v_ora_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/trinum_ora.txt
221 
222 echo "select COUNT(*) from dba_objects where OWNER='$v_user' and OBJECT_TYPE='TRIGGER';" \
223     |disql $v_dm_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/trinum.txt
224 
225 while  read  i
226 do
227 vs_tri_num=$( echo  "$i" | awk  -F  ' '  '{print $1}' )
228 done <$temp_dir/trinum_ora.txt
229 
230 while  read  i
231 do
232 vd_tri_num=$( echo  "$i" | awk  -F  ' '  '{print $1}' )
233 done <$temp_dir/trinum.txt
234 
235 if [[ $vs_tri_num = $vd_tri_num ]];then
236     echo "same tri_num:"${vs_tri_num}
237     echo "same tri_num:"${vs_tri_num}   >>$log
238 else
239     echo 'Not same tri_num:'${vs_tri_num}'!='${vd_tri_num}
240     echo 'Not same tri_num:'${vs_tri_num}'!='${vd_tri_num}   >>$log
241 fi
242 
243 
244 #1.8 包,PACKAGE
245 echo "select COUNT(*) from dba_objects$v_db_link where OWNER='$v_user' and OBJECT_TYPE='PACKAGE';" \
246     |disql $v_ora_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/pkgnum_ora.txt
247     
248 echo "select COUNT(*) from dba_objects where OWNER='$v_user' and OBJECT_TYPE='PACKAGE';" \
249     |disql $v_dm_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/pkgnum.txt
250 
251 while  read  i
252 do
253 vs_pkg_num=$( echo  "$i" | awk  -F  ' '  '{print $1}' )
254 done <$temp_dir/pkgnum_ora.txt
255 
256 while  read  i
257 do
258 vd_pkg_num=$( echo  "$i" | awk  -F  ' '  '{print $1}' )
259 done <$temp_dir/pkgnum.txt
260 
261 if [[ $vs_tpkg_num = $vd_pkg_num ]];then
262     echo "same pkg_num:"${vs_pkg_num}
263     echo "same pkg_num:"${vs_pkg_num}   >>$log
264 else
265     echo 'Not same pkg_num:'${vs_pkg_num}'!='${vd_pkg_num}
266     echo 'Not same pkg_num:'${vs_pkg_num}'!='${vd_pkg_num}   >>$log
267 fi
268 
269 
270 #1.9 同义词
271 echo "select COUNT(*) from dba_objects$v_db_link where OWNER='$v_user' and OBJECT_TYPE='SYNONYM';" \
272     |disql $v_ora_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/synnum_ora.txt
273 
274 echo "select COUNT(*) from dba_objects where OWNER='$v_user' and OBJECT_TYPE='SYNONYM';" \
275     |disql $v_dm_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/synnum.txt
276 
277 while  read  i
278 do
279 vs_syn_num=$( echo  "$i" | awk  -F  ' '  '{print $1}' )
280 done <$temp_dir/synnum_ora.txt
281 
282 while  read  i
283 do
284 vd_syn_num=$( echo  "$i" | awk  -F  ' '  '{print $1}' )
285 done <$temp_dir/synnum.txt
286 
287 if [[ $vs_syn_num = $vd_syn_num ]];then
288     echo "same syn_num:"${vs_syn_num}
289     echo "same syn_num:"${vs_syn_num}   >>$log
290 else
291     echo 'Not same syn_num:'${vs_syn_num}'!='${vd_syn_num}
292     echo 'Not same syn_num:'${vs_syn_num}'!='${vd_syn_num}   >>$log
293 fi
294 
295 
296 #1.3.10 类 --no
297 #1.3.12 域 --no
298 #1.3.12 自定义类型TYPE --no
299 
300 
301 ########################################################################
302 ########################----2.online------##############################
303 #2.1 index num
304 echo "select COUNT(*) from dba_objects$v_db_link where OWNER='$v_user' and OBJECT_TYPE='INDEX';" \
305     |disql $v_ora_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/idxnum_ora.txt
306     
307 echo "select COUNT(*) from dba_objects where OWNER='$v_user' and OBJECT_TYPE='INDEX';" \
308     |disql $v_dm_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/idxnum.txt
309 
310 while  read  i
311 do
312 vs_idx_num=$( echo  "$i" | awk  -F  ' '  '{print $1}' )
313 done <$temp_dir/idxnum_ora.txt
314 
315 while  read  i
316 do
317 vd_idx_num=$( echo  "$i" | awk  -F  ' '  '{print $1}' )
318 done <$temp_dir/idxnum.txt
319 
320 if [[ $vs_idx_num = $vd_idx_num ]];then
321     echo "same idx_num:"${vs_idx_num}
322     echo "same idx_num:"${vs_idx_num}   >>$log
323 else
324     echo 'Not same idx_num:'${vs_idx_num}'!='${vd_idx_num}
325     echo 'Not same idx_num:'${vs_idx_num}'!='${vd_idx_num}   >>$log
326 fi
327 
328 #2.1.1 idx detail
329 echo "SELECT * FROM USER_INDEXES$v_db_link WHERE INDEX_NAME IN(
330     SELECT DISTINCT A.INDEX_NAME FROM DBA_INDEXES$v_db_link A WHERE A.OWNER ='$v_user'
331     AND A.INDEX_TYPE='NORMAL' AND A.INDEX_NAME NOT LIKE 'PK_%' AND A.INDEX_NAME NOT IN(
332     SELECT INDEX_NAME FROM DBA_INDEXES WHERE OWNER ='$v_user' AND INDEX_TYPE='NORMAL'));" \
333     |disql $v_dm_conn  > $detail_dir/idx
334 
335 
336 
337 
338 #select TABLE_NAME from DBA_TABLES where OWNER like '$v_user' AND TABLE_NAME NOT LIKE '##%' order by TABLE_NAME;
339 #2.2 table rows
340 echo "select TABLE_NAME from DBA_TABLES where OWNER like '$v_user' AND TABLE_NAME NOT LIKE '##%' order by TABLE_NAME;" \
341     |disql $v_dm_conn | tail -n +8 | head -n -3 |awk '{print $2}' > $temp_dir/all_tablename
342 
343 while  read  i
344 do
345     vd_table_name=$( echo  "$i" | awk  -F  ' '  '{print $1}' )
346     #~ echo "$vd_table_name"
347 
348 #---dm
349 echo "select COUNT(*) from '$v_user'.$vd_table_name;" \
350     |disql $v_dm_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/dm_tab.txt
351     
352     while  read  m
353     do
354     vd_dm_num=$( echo  "$m" | awk  -F  ' '  '{print $1}' )
355     done <$temp_dir/dm_tab.txt
356 
357 #--oracle
358 echo "select COUNT(*) from '$v_user'.$vd_table_name$v_db_link;" \
359     |disql $v_ora_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/ora_tab.txt
360 
361     while  read  n
362     do
363     vd_ora_num=$( echo  "$n" | awk  -F  ' '  '{print $1}' )
364     done <$temp_dir/ora_tab.txt
365 
366 #-- veri dm->roacle
367     if [[ $vd_dm_num = $vd_ora_num ]];then
368         echo "same $v_user.$vd_table_name tab_count:"${vd_dm_num}
369         echo "same $v_user.$vd_table_name tab_count:"${vd_dm_num}   >>$log
370     else
371         echo 'Not same $v_user.$vd_table_name tab_count:'${vd_dm_num}'!='${vd_ora_num}
372         echo 'Not same $v_user.$vd_table_name tab_count:'${vd_dm_num}'!='${vd_ora_num}   >>$log
373     fi
374     
375 done <$temp_dir/all_tablename
376 
377 
378 
379 #2.3 data value
380 #2.4 check trigger on
381 
382 
383 
384

#!/bin/bash### --note1: select OBJECT_TYPE,COUNT(*) from all_objects where OWNER='SYSDBA' GROUP BY OBJECT_TYPE;### --note2： detail_pk,detail_fk,detail_ck,detail_unique,detail_idx
##源库信息设置：#~ vs_tab_num=4#~ vs_cons_num=4#~ vs_view_num=4#~ vs_proc_num=4#~ vs_fun_num=4#~ vs_seq_num=4#~ vs_tri_num=4#~ vs_pkg_num=4#~ vs_syn_num=4#~ vs_idx_num=4#~ vd_tab_num=4
v_user="SYSDBA"v_dm_conn="SYSDBA/SYSDBA@192.168.204.129:5236"v_ora_conn="SYSDBA/SYSDBA@192.168.204.129:5236"#~ v_db_link="@LINK01"v_db_link=""
detail_dir="./detail/"temp_dir="./temp/"log="dbcheck.log"

################################################################################################---1.before online---############################1.1 table numecho "select count(*) from DBA_TABLES$v_db_link where OWNER like '$v_user' AND TABLE_NAME NOT LIKE '##%' order by TABLE_NAME;" |disql $v_ora_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/tabnum_ora.txtecho "select count(*) from DBA_TABLES where OWNER like '$v_user' AND TABLE_NAME NOT LIKE '##%' order by TABLE_NAME;" |disql $v_dm_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/tabnum.txt
while read idovs_tab_num=$( echo "$i" | awk -F ' ' '{print $1}' )done <$temp_dir/tabnum_ora.txt
while read idovd_tab_num=$( echo "$i" | awk -F ' ' '{print $1}' )done <$temp_dir/tabnum.txt
if [[ $vs_tab_num = $vd_tab_num ]];thenecho "same tab_num:"${vs_tab_num}echo "same tab_num:"${vs_tab_num} >$logelseecho 'Not same tab_num:'${vs_tab_num}'!='${vd_tab_num}echo 'Not same tab_num:'${vs_tab_num}'!='${vd_tab_num} >$logfi

#1.2 constraint (unique, check, fk, pk)echo "select COUNT(*) from dba_objects$v_db_link where OWNER='$v_user' and OBJECT_TYPE='CONSTRAINT';" \|disql $v_ora_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/consnum_ora.txt
echo "select COUNT(*) from dba_objects where OWNER='$v_user' and OBJECT_TYPE='CONSTRAINT';" \|disql $v_dm_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/consnum.txt
while read idovs_cons_num=$( echo "$i" | awk -F ' ' '{print $1}' )done <$temp_dir/consnum_ora.txt while read idovd_cons_num=$( echo "$i" | awk -F ' ' '{print $1}' )done <$temp_dir/consnum.txt
if [[ $vs_cons_num = $vd_cons_num ]];thenecho "same cons_num:"${vs_cons_num}echo "same cons_num:"${vs_cons_num} >>$logelseecho 'Not same cons_num:'${vs_cons_num}'!='${vd_cons_num}echo 'Not same cons_num:'${vs_cons_num}'!='${vd_cons_num} >>$logfi
#1.2.1 constraint (pk)echo "SELECT * FROM USER_CONSTRAINTS$v_db_link WHERE CONSTRAINT_NAME IN(SELECT DISTINCT A.CONSTRAINT_NAME FROM DBA_CONSTRAINTS$v_db_link A WHERE A.OWNER ='$v_user'AND A.CONSTRAINT_TYPE='P' AND A.CONSTRAINT_NAME NOT IN(SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE OWNER ='$v_user' AND CONSTRAINT_TYPE='P'));" \|disql $v_dm_conn > $detail_dir/pk

#1.2.2 constraint (fk)echo "SELECT * FROM USER_CONSTRAINTS$v_db_link WHERE CONSTRAINT_NAME IN(SELECT DISTINCT A.CONSTRAINT_NAME FROM DBA_CONSTRAINTS$v_db_link A WHERE A.OWNER ='$v_user'AND A.CONSTRAINT_TYPE='R' AND A.CONSTRAINT_NAME NOT IN(SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE OWNER ='$v_user' AND CONSTRAINT_TYPE='R'));" \|disql $v_dm_conn > $detail_dir/fk
#1.2.3 constraint (check)echo "SELECT * FROM USER_CONSTRAINTS$v_db_link WHERE CONSTRAINT_NAME IN(SELECT DISTINCT A.CONSTRAINT_NAME FROM DBA_CONSTRAINTS$v_db_link A WHERE A.OWNER ='$v_user'AND A.CONSTRAINT_TYPE='C' AND A.CONSTRAINT_NAME NOT IN(SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE OWNER ='$v_user' AND CONSTRAINT_TYPE='C'));" \|disql $v_dm_conn > $detail_dir/ck#1.2.4 constraint (unique)echo "SELECT * FROM USER_CONSTRAINTS$v_db_link WHERE CONSTRAINT_NAME IN(SELECT DISTINCT A.CONSTRAINT_NAME FROM DBA_CONSTRAINTS$v_db_link A WHERE A.OWNER ='$v_user'AND A.CONSTRAINT_TYPE='U' AND A.CONSTRAINT_NAME NOT IN(SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE OWNER ='$v_user' AND CONSTRAINT_TYPE='U'));" \|disql $v_dm_conn > $detail_dir/unique

#1.3 VIEW (MATERIALIZED VIEW --no)echo "select COUNT(*) from dba_objects$v_db_link where OWNER='$v_user' and OBJECT_TYPE='VIEW';" \|disql $v_ora_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/viewnum_ora.txt
echo "select COUNT(*) from dba_objects where OWNER='$v_user' and OBJECT_TYPE='VIEW';" \|disql $v_dm_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/viewnum.txt while read idovs_view_num=$( echo "$i" | awk -F ' ' '{print $1}' )done <$temp_dir/viewnum_ora.txt while read idovd_view_num=$( echo "$i" | awk -F ' ' '{print $1}' )done <$temp_dir/viewnum.txt
if [[ $vs_view_num = $vd_view_num ]];thenecho "same view_num:"${vs_view_num}echo "same view_num:"${vs_view_num} >>$logelseecho 'Not same view_num:'${vs_view_num}'!='${vd_view_num}echo 'Not same view_num:'${vs_view_num}'!='${vd_view_num} >>$logfi

#1.4 PROCEDUREecho "select COUNT(*) from dba_objects$v_db_link where OWNER='$v_user' and OBJECT_TYPE='PROCEDURE';" \|disql $v_ora_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/procnum_ora.txt
echo "select COUNT(*) from dba_objects where OWNER='$v_user' and OBJECT_TYPE='PROCEDURE';" \|disql $v_dm_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/procnum.txt

while read idovs_proc_num=$( echo "$i" | awk -F ' ' '{print $1}' )done <$temp_dir/procnum_ora.txtwhile read idovd_proc_num=$( echo "$i" | awk -F ' ' '{print $1}' )done <$temp_dir/procnum.txt
if [[ $vs_proc_num = $vd_proc_num ]];thenecho "same proc_num:"${vs_proc_num}echo "same proc_num:"${vs_proc_num} >>$logelseecho 'Not same proc_num:'${vs_proc_num}'!='${vd_proc_num}echo 'Not same proc_num:'${vs_proc_num}'!='${vd_proc_num} >>$logfi

#1.5 函数FUNCTION，(外部函数--no)echo "select COUNT(*) from dba_objects$v_db_link where OWNER='$v_user' and OBJECT_TYPE='FUNCTION';" \|disql $v_ora_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/funnum_ora.txtecho "select COUNT(*) from dba_objects where OWNER='$v_user' and OBJECT_TYPE='FUNCTION';" \|disql $v_dm_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/funnum.txt
while read idovs_fun_num=$( echo "$i" | awk -F ' ' '{print $1}' )done <$temp_dir/funnum_ora.txt
while read idovd_fun_num=$( echo "$i" | awk -F ' ' '{print $1}' )done <$temp_dir/funnum.txt
if [[ $vs_fun_num = $vd_fun_num ]];thenecho "same fun_num:"${vs_fun_num}echo "same fun_num:"${vs_fun_num} >>$logelseecho 'Not same fun_num:'${vs_fun_num}'!='${vd_fun_num}echo 'Not same fun_num:'${vs_fun_num}'!='${vd_fun_num} >>$logfi

#1.6 序列,SEQUENCEecho "select COUNT(*) from dba_objects$v_db_link where OWNER='$v_user' and OBJECT_TYPE='SEQUENCE';" \|disql $v_ora_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/seqnum_ora.txt
echo "select COUNT(*) from dba_objects where OWNER='$v_user' and OBJECT_TYPE='SEQUENCE';" \|disql $v_dm_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/seqnum.txt
while read idovs_seq_num=$( echo "$i" | awk -F ' ' '{print $1}' )done <$temp_dir/seqnum_ora.txt
while read idovd_seq_num=$( echo "$i" | awk -F ' ' '{print $1}' )done <$temp_dir/seqnum.txt
if [[ $vs_seq_num = $vd_seq_num ]];thenecho "same seq_num:"${vs_seq_num}echo "same seq_num:"${vs_seq_num} >>$logelseecho 'Not same seq_num:'${vs_seq_num}'!='${vd_seq_num}echo 'Not same seq_num:'${vs_seq_num}'!='${vd_seq_num} >>$logfi

#1.7 TRIGGER + trigger offecho "select COUNT(*) from dba_objects$v_db_link where OWNER='$v_user' and OBJECT_TYPE='TRIGGER';" \|disql $v_ora_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/trinum_ora.txt
echo "select COUNT(*) from dba_objects where OWNER='$v_user' and OBJECT_TYPE='TRIGGER';" \|disql $v_dm_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/trinum.txt
while read idovs_tri_num=$( echo "$i" | awk -F ' ' '{print $1}' )done <$temp_dir/trinum_ora.txt
while read idovd_tri_num=$( echo "$i" | awk -F ' ' '{print $1}' )done <$temp_dir/trinum.txt
if [[ $vs_tri_num = $vd_tri_num ]];thenecho "same tri_num:"${vs_tri_num}echo "same tri_num:"${vs_tri_num} >>$logelseecho 'Not same tri_num:'${vs_tri_num}'!='${vd_tri_num}echo 'Not same tri_num:'${vs_tri_num}'!='${vd_tri_num} >>$logfi

#1.8 包,PACKAGEecho "select COUNT(*) from dba_objects$v_db_link where OWNER='$v_user' and OBJECT_TYPE='PACKAGE';" \|disql $v_ora_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/pkgnum_ora.txtecho "select COUNT(*) from dba_objects where OWNER='$v_user' and OBJECT_TYPE='PACKAGE';" \|disql $v_dm_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/pkgnum.txt
while read idovs_pkg_num=$( echo "$i" | awk -F ' ' '{print $1}' )done <$temp_dir/pkgnum_ora.txt
while read idovd_pkg_num=$( echo "$i" | awk -F ' ' '{print $1}' )done <$temp_dir/pkgnum.txt
if [[ $vs_tpkg_num = $vd_pkg_num ]];thenecho "same pkg_num:"${vs_pkg_num}echo "same pkg_num:"${vs_pkg_num} >>$logelseecho 'Not same pkg_num:'${vs_pkg_num}'!='${vd_pkg_num}echo 'Not same pkg_num:'${vs_pkg_num}'!='${vd_pkg_num} >>$logfi

#1.9 同义词echo "select COUNT(*) from dba_objects$v_db_link where OWNER='$v_user' and OBJECT_TYPE='SYNONYM';" \|disql $v_ora_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/synnum_ora.txt
echo "select COUNT(*) from dba_objects where OWNER='$v_user' and OBJECT_TYPE='SYNONYM';" \|disql $v_dm_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/synnum.txt
while read idovs_syn_num=$( echo "$i" | awk -F ' ' '{print $1}' )done <$temp_dir/synnum_ora.txt
while read idovd_syn_num=$( echo "$i" | awk -F ' ' '{print $1}' )done <$temp_dir/synnum.txt
if [[ $vs_syn_num = $vd_syn_num ]];thenecho "same syn_num:"${vs_syn_num}echo "same syn_num:"${vs_syn_num} >>$logelseecho 'Not same syn_num:'${vs_syn_num}'!='${vd_syn_num}echo 'Not same syn_num:'${vs_syn_num}'!='${vd_syn_num} >>$logfi

#1.3.10 类 --no#1.3.12 域 --no#1.3.12 自定义类型TYPE --no

################################################################################################----2.online------###############################2.1 index numecho "select COUNT(*) from dba_objects$v_db_link where OWNER='$v_user' and OBJECT_TYPE='INDEX';" \|disql $v_ora_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/idxnum_ora.txtecho "select COUNT(*) from dba_objects where OWNER='$v_user' and OBJECT_TYPE='INDEX';" \|disql $v_dm_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/idxnum.txt
while read idovs_idx_num=$( echo "$i" | awk -F ' ' '{print $1}' )done <$temp_dir/idxnum_ora.txt
while read idovd_idx_num=$( echo "$i" | awk -F ' ' '{print $1}' )done <$temp_dir/idxnum.txt
if [[ $vs_idx_num = $vd_idx_num ]];thenecho "same idx_num:"${vs_idx_num}echo "same idx_num:"${vs_idx_num} >>$logelseecho 'Not same idx_num:'${vs_idx_num}'!='${vd_idx_num}echo 'Not same idx_num:'${vs_idx_num}'!='${vd_idx_num} >>$logfi
#2.1.1 idx detailecho "SELECT * FROM USER_INDEXES$v_db_link WHERE INDEX_NAME IN(SELECT DISTINCT A.INDEX_NAME FROM DBA_INDEXES$v_db_link A WHERE A.OWNER ='$v_user'AND A.INDEX_TYPE='NORMAL' AND A.INDEX_NAME NOT LIKE 'PK_%' AND A.INDEX_NAME NOT IN(SELECT INDEX_NAME FROM DBA_INDEXES WHERE OWNER ='$v_user' AND INDEX_TYPE='NORMAL'));" \|disql $v_dm_conn > $detail_dir/idx

#select TABLE_NAME from DBA_TABLES where OWNER like '$v_user' AND TABLE_NAME NOT LIKE '##%' order by TABLE_NAME;#2.2 table rowsecho "select TABLE_NAME from DBA_TABLES where OWNER like '$v_user' AND TABLE_NAME NOT LIKE '##%' order by TABLE_NAME;" \|disql $v_dm_conn | tail -n +8 | head -n -3 |awk '{print $2}' > $temp_dir/all_tablename
while read idovd_table_name=$( echo "$i" | awk -F ' ' '{print $1}' )#~ echo "$vd_table_name"
#---dmecho "select COUNT(*) from '$v_user'.$vd_table_name;" \|disql $v_dm_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/dm_tab.txtwhile read mdovd_dm_num=$( echo "$m" | awk -F ' ' '{print $1}' )done <$temp_dir/dm_tab.txt
#--oracleecho "select COUNT(*) from '$v_user'.$vd_table_name$v_db_link;" \|disql $v_ora_conn | tail -n 4 | head -n 1 |awk '{print $2}' > $temp_dir/ora_tab.txt
while read ndovd_ora_num=$( echo "$n" | awk -F ' ' '{print $1}' )done <$temp_dir/ora_tab.txt
#-- veri dm->roacleif [[ $vd_dm_num = $vd_ora_num ]];thenecho "same $v_user.$vd_table_name tab_count:"${vd_dm_num}echo "same $v_user.$vd_table_name tab_count:"${vd_dm_num} >>$logelseecho 'Not same $v_user.$vd_table_name tab_count:'${vd_dm_num}'!='${vd_ora_num}echo 'Not same $v_user.$vd_table_name tab_count:'${vd_dm_num}'!='${vd_ora_num} >>$logfidone <$temp_dir/all_tablename

#2.3 data value#2.4 check trigger on

posted @ 2020-11-03 20:30 疾风泣影阅读(143) 评论(0) 编辑收藏举报

刷新页面返回顶部

疾风泣影

DM7&DM8数据对象校验shell脚本

公告