[20170410]快速找回触发器内容.txt
--上午登录发现一个是触发器被人为删除了,需要恢复.链接http://www.itpub.net/thread-2084789-1-1.html.
--我提供几个方法:1.取出备份的system数据文件,通过bbed之类的工具定位.2使用logminer 应该也可以定位,表sys.source$.
--实际上还有1个简单粗暴的方法,直接通过strings查询归档日志.例子如下:
1.环境:
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//我的测试环境很早建立的一个函数.
CREATE OR REPLACE function SCOTT.p2p_distance(
p_latitude1 number,
p_longitude1 number,
p_latitude2 number,
p_longitude2 number) return number deterministic is
earth_radius number := 6371;
pi_approx number := 3.1415197/180;
lat_delta number := (p_latitude2-p_latitude1)*pi_approx;
lon_delta number := (p_longitude2-p_longitude1)*pi_approx;
arc number := sin(lat_delta/2) * sin(lat_delta/2) +
sin(lon_delta/2) * sin(lon_delta/2) * cos(p_latitude1*pi_approx) * cos(p_latitude2*pi_approx);
begin
return earth_radius * 2 * atan2(sqrt(arc), sqrt(1-arc));
end;
/
2.删除它,恢复看看.
SYS@book> @ &r/logfile
GROUP# STATUS TYPE MEMBER IS_ GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------ ------ ---------- ------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
1 ONLINE /mnt/ramdisk/book/redo01.log NO 1 1 815 52428800 512 1 YES INACTIVE 13277272313 2017-04-08 16:00:41 13277278586 2017-04-10 08:43:12
2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 816 52428800 512 1 YES INACTIVE 13277278586 2017-04-10 08:43:12 13277278900 2017-04-10 08:43:16
3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 817 52428800 512 1 NO CURRENT 13277278900 2017-04-10 08:43:16 2.814750E+14
4 STANDBY /mnt/ramdisk/book/redostb01.log NO
5 STANDBY /mnt/ramdisk/book/redostb02.log NO
6 STANDBY /mnt/ramdisk/book/redostb03.log NO
7 STANDBY /mnt/ramdisk/book/redostb04.log NO
7 rows selected.
--//当前在线日志/mnt/ramdisk/book/redo03.log .
SYS@book> drop function SCOTT.p2p_distance;
Function dropped.
SYS@book> alter system checkpoint ;
System altered.
SYS@book> alter system checkpoint ;
System altered.
SYS@book> alter system checkpoint ;
System altered.
--//保证脏块写盘.
$ strings -3 /mnt/ramdisk/book/redo03.log > /tmp/aaa.txt
drop function SCOTT.p2p_distance
SCOTT
P2P_DISTANCE
ORA$BASE.,
YYYY-MM-DD HH24:MI:SS
YYYY-MM-DD HH24:MI:SS.FFHH.MI.SSXFF AM
HH.MI.SSXFF AM TZR
YYYY-MM-DD HH24:MI:SS.FF TZH:TZMAMERICANAMERICANGREGORIAN
BINARY
BINARY
AMERICA
AMERICA
BYTEFALSE
_H--------------------------------------
function p2p_distance(
p_latitude1 number,
p_longitude1 number,
p_latitude2 number,
p_longitude2 number) return number deterministic is
earth_radius number := 6371;
pi_approx number := 3.1415197/180;
la
t_delta number := (p_latitude2-p_latitude1)*pi_approx;
lon_delta number := (p_longitude2-p_longitude1)*pi_approx;
arc number := sin(lat_delta/2) * sin(lat_delta/2) +
sin(lon_delta
6/2) * sin(lon_delta/2) * cos(p_latitude1*pi_approx) * cos(p_latitude2*pi_approx);
~~
begin
return earth_radius * 2 * atan2(sqrt(arc), sqrt(1-arc));
end;
U7^M
--//ok,里面就有内容.改写一下就ok了:(注有一点点小错误,里面多了1个6,注意看下划线)
CREATE OR REPLACE function SCOTT.p2p_distance(
p_latitude1 number,
p_longitude1 number,
p_latitude2 number,
p_longitude2 number) return number deterministic is
earth_radius number := 6371;
pi_approx number := 3.1415197/180;
lat_delta number := (p_latitude2-p_latitude1)*pi_approx;
lon_delta number := (p_longitude2-p_longitude1)*pi_approx;
arc number := sin(lat_delta/2) * sin(lat_delta/2) +
sin(lon_delta/2) * sin(lon_delta/2) * cos(p_latitude1*pi_approx) * cos(p_latitude2*pi_approx);
begin
return earth_radius * 2 * atan2(sqrt(arc), sqrt(1-arc));
end;
/
--//在引申一点,实际上你可以直接扫描system01.dbf文件找到对应的内容.正常业务数据库一般不会有大量的dml对于sys.source$表.
SYS@book> column PARTITION_NAME noprint
SYS@book> select * from (select * from dba_extents where segment_name='SOURCE$' order by block_id desc) where rownum=1;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SYS SOURCE$ TABLE SYSTEM 79 1 91520 8388608 1024 1
--//最后1块在91520.占用1024块.
SYS@book> select (91520+1024-1)*8192 from dual ;
(91520+1024-1)*8192
-------------------
758112256
--//91520+1024=92544
$ dd if=/mnt/ramdisk/book/system01.dbf bs=8192 count=92544 | strings -3 > /tmp/bbb.txt
92544+0 records in
92544+0 records out
758120448 bytes (758 MB) copied, 11.8657 seconds, 63.9 MB/s
--//查询可以找到如下:
function p2p_distance(
_H
p_latitude1 number,
_H
! p_longitude1 number,
_H
p_latitude2 number,
_H
@ p_longitude2 number) return number deterministic is
_H
earth_radius number := 6371;
_H
) pi_approx number := 3.1415197/180;
_H
? lat_delta number := (p_latitude2-p_latitude1)*pi_approx;
_H
A lon_delta number := (p_longitude2-p_longitude1)*pi_approx;
_H
@ arc number := sin(lat_delta/2) * sin(lat_delta/2) +
_H
~ sin(lon_delta/2) * sin(lon_delta/2) * cos(p_latitude1*pi_approx) * cos(p_latitude2*pi_approx);
_H
begin
_H
; return earth_radius * 2 * atan2(sqrt(arc), sqrt(1-arc));
_H
end;,
--//编辑一下就ok了.感觉看归档日志更好一些.重新建立后
SCOTT@book> select p2p_distance(36.12, -86.67, 33.94, -118.4) from dual;
P2P_DISTANCE(36.12,-86.67,33.94,-118.4)
---------------------------------------
2886.40705
--//与原来计算结果一样.