oracle数据库快照

(172)上配置用于源的UDB_V2和UDB的tnsname.ora,以便直接在172上分别操控源和目的:
UDB_V2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.91)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = smsc)
)
)
UDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = udb)
)
)
1,在源数据库上登录,查询
sqlplus udb_v2/udb@udb
执行:
select * from global_name;
得到
SMSC.REGRESS.RDBMS.DEV.US.ORACLE.COM , 记住数据库名:smsc
2,在目的数据库上登录:
sqlplus 'udb_v2/udb123#@UDB'
执行:
create database link smsc connect to udb_v2 identified by udb using 'test';
建立dblink
其中smsc为源数据库的数据库名,test为服务器端的tnsname!
test为tnsname.ora中连接源的服务名
检查下dblink是否可用:
select count(*) from device@smsc;
看是否能查到结果
3,再源数据库上登录
建立快照日志:
create snapshot log on userid;
create snapshot log on service;
4,在目的数据库上登录
建立快照,会很长时间,这时数据会导入
create snapshot sn_userid as select * from userid@smsc;
create snapshot sn_service as select * from service@smsc;
将快照插入userid和service表中
insert into userid select * from sn_userid;
insert into service select * from sn_service;

建立快照的触发器
sn_userid:
CREATE OR REPLACE TRIGGER UDB_V2.TRI_SN_USERID_AFR
AFTER DELETE OR INSERT OR UPDATE
ON UDB_V2.SN_USERID REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
tmp_id varchar2(40);
tmp_ss integer;
begin
dbms_output.put_line('begin triger on sn_userid');
if inserting then

dbms_output.put_line('insert');
for p in(
select user_id into tmp_id from userid where user_id=:new.user_id
)
loop
tmp_id:=p.user_id;
end loop;
if (tmp_id is null) then
INSERT INTO USERID
(
USER_ID, PASSWD, USERID_STATUS,
CREATE_TIME, EXT_PASSWD, TMP_PASSWD,
TMP_CREATE_TIME, TMP_EXIPRATION_TIME, OTP_TOKENID,
SEX, EMAIL, LAST_MODIFY_TIME,
PROVINCE_NO, CITY_NO, CERTIFICATE_TYPE,
CERTIFICATE_NO, PAY_TYPE, PRE_PAY_SYSTEM_NO,
PAY_EFFECT_MODE, PAY_EFFECT_TIME, SRC_DEVICE_NO,
ACTIVE_STATUS, UPDATE_FLAG, USERID_TYPE,
AREACODE, CUSTOMERID, USERNAME,
SERSET_TYPE, BINDING_TELE_NO, P_USERID,
ALIAS, ACCOUNT_ACT, ACCOUNT_TYPE,
ACCOUNT_STATE, ACCOUNT_RETRY, ACCOUNT_RETRYTIME,
PASSWD_ACT, USERID_LOGIN_STATUS, USERIDSTATUS_REFTIME,
MOBILE_PHONE, SSO_FLAG, UIM,
CUSTOMERFLAG, PASSWD2, P_USERID_PASSPORT,
USERID_STATUS_EXT
)
VALUES
(
:NEW.USER_ID, :NEW.PASSWD, :NEW.USERID_STATUS,
:NEW.CREATE_TIME, :NEW.EXT_PASSWD, :NEW.TMP_PASSWD,
:NEW.TMP_CREATE_TIME, :NEW.TMP_EXIPRATION_TIME, :NEW.OTP_TOKENID,
:NEW.SEX, :NEW.EMAIL, :NEW.LAST_MODIFY_TIME,
:NEW.PROVINCE_NO, :NEW.CITY_NO, :NEW.CERTIFICATE_TYPE,
:NEW.CERTIFICATE_NO, :NEW.PAY_TYPE, :NEW.PRE_PAY_SYSTEM_NO,
:NEW.PAY_EFFECT_MODE, :NEW.PAY_EFFECT_TIME, :NEW.SRC_DEVICE_NO,
:NEW.ACTIVE_STATUS, :NEW.UPDATE_FLAG, :NEW.USERID_TYPE,
:NEW.AREACODE, :NEW.CUSTOMERID, :NEW.USERNAME,
:NEW.SERSET_TYPE, :NEW.BINDING_TELE_NO, :NEW.P_USERID,
:NEW.ALIAS, :NEW.ACCOUNT_ACT, :NEW.ACCOUNT_TYPE,
:NEW.ACCOUNT_STATE, :NEW.ACCOUNT_RETRY, :NEW.ACCOUNT_RETRYTIME,
:NEW.PASSWD_ACT, :NEW.USERID_LOGIN_STATUS, :NEW.USERIDSTATUS_REFTIME,
:NEW.MOBILE_PHONE, :NEW.SSO_FLAG, :NEW.UIM,
:NEW.CUSTOMERFLAG, :NEW.PASSWD2,:NEW.P_USERID_PASSPORT,
:NEW.USERID_STATUS_EXT
);
end if;
end if;
if updating then
dbms_output.put_line('updated');
for p in(select * from userid where user_id=:OLD.user_id)
loop
if (
(p.PASSWD != :new.PASSWD)
OR (p.USERID_STATUS != :new.USERID_STATUS)
OR (p.CREATE_TIME != :new.CREATE_TIME)
OR (p.EXT_PASSWD != :new.EXT_PASSWD)
OR (p.TMP_PASSWD != :new.TMP_PASSWD)
OR (p.TMP_EXIPRATION_TIME != :new.TMP_EXIPRATION_TIME)
OR (p.OTP_TOKENID != :new.OTP_TOKENID)
OR (p.SEX != :new.SEX)
OR (p.EMAIL != :new.EMAIL)
OR (p.LAST_MODIFY_TIME != :new.LAST_MODIFY_TIME)
OR (p.PROVINCE_NO != :new.PROVINCE_NO)
OR (p.CITY_NO != :new.CITY_NO)
OR (p.CERTIFICATE_TYPE != :new.CERTIFICATE_TYPE)
OR (p.CERTIFICATE_NO != :new.CERTIFICATE_NO)
OR (p.PAY_TYPE != :new.PAY_TYPE)
OR (p.PRE_PAY_SYSTEM_NO != :new.PRE_PAY_SYSTEM_NO)
OR (p.PAY_EFFECT_MODE != :new.PAY_EFFECT_MODE)
OR (p.PAY_EFFECT_TIME != :new.PAY_EFFECT_TIME)
OR (p.SRC_DEVICE_NO != :new.SRC_DEVICE_NO)
OR (p.ACTIVE_STATUS != :new.ACTIVE_STATUS)
OR (p.UPDATE_FLAG != :new.UPDATE_FLAG)
OR (p.USERID_TYPE != :new.USERID_TYPE)
OR (p.AREACODE != :new.AREACODE)
OR (p.CUSTOMERID != :new.CUSTOMERID)
OR (p.USERNAME != :new.USERNAME)
OR (p.SERSET_TYPE != :new.SERSET_TYPE)
OR (p.BINDING_TELE_NO != :new.BINDING_TELE_NO)
OR (p.P_USERID != :new.P_USERID)
OR (p.ALIAS != :new.ALIAS)
OR (p.ACCOUNT_ACT != :new.ACCOUNT_ACT)
OR (p.ACCOUNT_TYPE != :new.ACCOUNT_TYPE)
OR (p.ACCOUNT_STATE != :new.ACCOUNT_STATE)
OR (p.ACCOUNT_RETRY != :new.ACCOUNT_RETRY)
OR (p.ACCOUNT_RETRYTIME != :new.ACCOUNT_RETRYTIME)
OR (p.PASSWD_ACT != :new.PASSWD_ACT)
OR (p.USERID_LOGIN_STATUS != :new.USERID_LOGIN_STATUS)
OR (p.USERIDSTATUS_REFTIME != :new.USERIDSTATUS_REFTIME)
OR (p.MOBILE_PHONE != :new.MOBILE_PHONE)
OR (p.SSO_FLAG != :new.SSO_FLAG)
OR (p.UIM != :new.UIM)
OR (p.CUSTOMERFLAG != :new.CUSTOMERFLAG)
OR (p.PASSWD2 != :new.PASSWD2)
OR (p.P_USERID_PASSPORT != :new.P_USERID_PASSPORT)
OR (p.USERID_STATUS_EXT != :new.USERID_STATUS_EXT)
) then
UPDATE USERID
SET USER_ID = :new.USER_ID,
PASSWD = :new.PASSWD,
USERID_STATUS = :new.USERID_STATUS,
CREATE_TIME = :new.CREATE_TIME,
EXT_PASSWD = :new.EXT_PASSWD,
TMP_PASSWD = :new.TMP_PASSWD,
TMP_CREATE_TIME = :new.TMP_CREATE_TIME,
TMP_EXIPRATION_TIME = :new.TMP_EXIPRATION_TIME,
OTP_TOKENID = :new.OTP_TOKENID,
SEX = :new.SEX,
EMAIL = :new.EMAIL,
LAST_MODIFY_TIME = :new.LAST_MODIFY_TIME,
PROVINCE_NO = :new.PROVINCE_NO,
CITY_NO = :new.CITY_NO,
CERTIFICATE_TYPE = :new.CERTIFICATE_TYPE,
CERTIFICATE_NO = :new.CERTIFICATE_NO,
PAY_TYPE = :new.PAY_TYPE,
PRE_PAY_SYSTEM_NO = :new.PRE_PAY_SYSTEM_NO,
PAY_EFFECT_MODE = :new.PAY_EFFECT_MODE,
PAY_EFFECT_TIME = :new.PAY_EFFECT_TIME,
SRC_DEVICE_NO = :new.SRC_DEVICE_NO,
ACTIVE_STATUS = :new.ACTIVE_STATUS,
UPDATE_FLAG = :new.UPDATE_FLAG,
USERID_TYPE = :new.USERID_TYPE,
AREACODE = :new.AREACODE,
CUSTOMERID = :new.CUSTOMERID,
USERNAME = :new.USERNAME,
SERSET_TYPE = :new.SERSET_TYPE,
BINDING_TELE_NO = :new.BINDING_TELE_NO,
P_USERID = :new.P_USERID,
ALIAS = :new.ALIAS,
ACCOUNT_ACT = :new.ACCOUNT_ACT,
ACCOUNT_TYPE = :new.ACCOUNT_TYPE,
ACCOUNT_STATE = :new.ACCOUNT_STATE,
ACCOUNT_RETRY = :new.ACCOUNT_RETRY,
ACCOUNT_RETRYTIME = :new.ACCOUNT_RETRYTIME,
PASSWD_ACT = :new.PASSWD_ACT,
USERID_LOGIN_STATUS = :new.USERID_LOGIN_STATUS,
USERIDSTATUS_REFTIME = :new.USERIDSTATUS_REFTIME,
MOBILE_PHONE = :new.MOBILE_PHONE,
SSO_FLAG = :new.SSO_FLAG,
UIM = :new.UIM,
CUSTOMERFLAG = :new.CUSTOMERFLAG,
PASSWD2 = :new.PASSWD2,
P_USERID_PASSPORT = :new.P_USERID_PASSPORT,
USERID_STATUS_EXT = :new.USERID_STATUS_EXT
WHERE USER_ID = :new.USER_ID
;
end if;
end loop;
end if;
if deleting then

dbms_output.put_line('deleted');
delete from service where user_id=:old.user_id;
delete from userid where user_id=:old.user_id;
end if;
dbms_output.put_line('end triger on sn_userid');
end TRI_SN_USERID_AFR;
/

/
service:
CREATE OR REPLACE TRIGGER UDB_V2.TRI_SN_SERVICE_AFR
AFTER DELETE OR INSERT OR UPDATE
ON UDB_V2.SN_SERVICE REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
tmp_ss INTEGER;
begin
dbms_output.put_line('begin triger on sn_service');
if inserting then
dbms_output.put_line('insert');
for p in(
select SS_INDEX into tmp_ss from SERVICE where SS_INDEX=:new.SS_INDEX
)
loop
tmp_ss:=p.SS_INDEX;
end loop;

if (tmp_ss is null) then
dbms_output.put_line('in insert service');
INSERT INTO SERVICE (
SS_INDEX, USER_ID, SS_PASSWORD,
SS_STATUS, SRC_DEVICE_NO, CREATE_TIME,
LAST_MODIFY_TIME, AUTH_SRC, REG_EFFECT_MODE,
REG_EFFECT_TIME, BIND_TYPE, BIND_EFFECT_MODE,
BIND_EFFECT_TIME, SS_TYPE, USERID_SS_LOGIN_STATUS,
SS_ID, SS_PASSWD_ACT, SS_ACTIVE_FLAG
)
VALUES (
:NEW.SS_INDEX, :NEW.USER_ID, :NEW.SS_PASSWORD,
:NEW.SS_STATUS, :NEW.SRC_DEVICE_NO, :NEW.CREATE_TIME,
:NEW.LAST_MODIFY_TIME, :NEW.AUTH_SRC, :NEW.REG_EFFECT_MODE,
:NEW.REG_EFFECT_TIME, :NEW.BIND_TYPE, :NEW.BIND_EFFECT_MODE,
:NEW.BIND_EFFECT_TIME, :NEW.SS_TYPE, :NEW.USERID_SS_LOGIN_STATUS ,
:NEW.SS_ID, :NEW.SS_PASSWD_ACT, :NEW.SS_ACTIVE_FLAG
);
end if;

end if;

if updating then
dbms_output.put_line('updated');
for p in(select * from SERVICE where SS_INDEX=:OLD.SS_INDEX)
loop
if (
(P.USER_ID != :NEW.USER_ID)
OR (P.SS_PASSWORD != :NEW.SS_PASSWORD)
OR (P.SS_STATUS != :NEW.SS_STATUS)
OR (P.SRC_DEVICE_NO != :NEW.SRC_DEVICE_NO)
OR (P.CREATE_TIME != :NEW.CREATE_TIME)
OR (P.LAST_MODIFY_TIME != :NEW.LAST_MODIFY_TIME)
OR (P.AUTH_SRC != :NEW.AUTH_SRC)
OR (P.REG_EFFECT_MODE != :NEW.REG_EFFECT_MODE)
OR (P.REG_EFFECT_TIME != :NEW.REG_EFFECT_TIME)
OR (P.BIND_TYPE != :NEW.BIND_TYPE)
OR (P.BIND_EFFECT_MODE != :NEW.BIND_EFFECT_MODE)
OR (P.BIND_EFFECT_TIME != :NEW.BIND_EFFECT_TIME)
OR (P.SS_TYPE != :NEW.SS_TYPE)
OR (P.USERID_SS_LOGIN_STATUS != :NEW.USERID_SS_LOGIN_STATUS)
OR (P.SS_ID != :NEW.SS_ID)
OR (P.SS_PASSWD_ACT != :NEW.SS_PASSWD_ACT)
OR (P.SS_ACTIVE_FLAG != :NEW.SS_ACTIVE_FLAG)
)
then
UPDATE SERVICE
SET
SS_INDEX = :NEW.SS_INDEX,
USER_ID = :NEW.USER_ID,
SS_PASSWORD = :NEW.SS_PASSWORD,
SS_STATUS = :NEW.SS_STATUS,
SRC_DEVICE_NO = :NEW.SRC_DEVICE_NO,
CREATE_TIME = :NEW.CREATE_TIME,
LAST_MODIFY_TIME = :NEW.LAST_MODIFY_TIME,
AUTH_SRC = :NEW.AUTH_SRC,
REG_EFFECT_MODE = :NEW.REG_EFFECT_MODE,
REG_EFFECT_TIME = :NEW.REG_EFFECT_TIME,
BIND_TYPE = :NEW.BIND_TYPE,
BIND_EFFECT_MODE = :NEW.BIND_EFFECT_MODE,
BIND_EFFECT_TIME = :NEW.BIND_EFFECT_TIME,
SS_TYPE = :NEW.SS_TYPE,
USERID_SS_LOGIN_STATUS = :NEW.USERID_SS_LOGIN_STATUS,
SS_ID = :NEW.SS_ID,
SS_PASSWD_ACT = :NEW.SS_PASSWD_ACT,
SS_ACTIVE_FLAG = :NEW.SS_ACTIVE_FLAG
WHERE SS_INDEX = :new.SS_INDEX
;
end if;
end loop;
end if;

if deleting then

dbms_output.put_line('deleted');
delete from service where SS_INDEX=:old.SS_INDEX;
end if;
dbms_output.put_line('end triger on sn_service');
end TRI_SN_SERVICE_AFR;
/

修改快照刷新时间
Alter snapshot sn_userid refresh fast Start with sysdate+2/(24) next sysdate+1/(24);
Alter snapshot sn_service refresh fast Start with sysdate+2/(24) next sysdate+1/(24);
首次刷新在30秒之后,以后每分钟刷新一次


/
附件:
1.查看快照最后一次刷新时间
SELECT NAME,LAST_REFRESH FROM ALL_SNAPSHOT_REFRESH_TIMES;
2.查看快照下次执行时间
select last_date,next_date,what from user_jobs order by next_date;
3.手动执行更新
EXEC DBMS_SNAPSHOT.REFRESH ('sn_service','F');
手动刷新方式1
begin
dbms_refresh.refresh('sn_service');
end;
手动刷新方式2
EXEC DBMS_SNAPSHOT.REFRESH('sn_service','F'); //第一个参数是快照名,第二个参数 F 是快速刷新 C 是完全刷新.
4.删除快照日志(在源上)
drop snapshot log on userid;
drop snapshot log on service;
5删除掉快照(在目的上)
drop snapshot sn_userid;
drop snapshot sn_service;