oracle数据库快照初学者学习与实践

oracle数据库快照初学者学习与实践

定义
oracle 数据库的快照是一个表，它包含有对一个本地或远程数据库上一个或多个表或视图的查询的结果。正因为快照是一个主表的查询子集，使用快照可以加快数据的查询速度；在保持不同数据库中的两个表的同步中，利用快照刷新，数据的更新性能也会有很大的改善。
操作
1.首先准备两个库 一个是源数据库 （本地数据库）、一个是备份数据库（远程数据库） 。（注意：两个库的名称和登录名最好是一样 这样方便识别）

2.创建两个库的数据连接。（注意：如果只做本地数据库数据快照到备份数据的单向同步的话，我们只需要在备份数据库创建改数据库连接。如果是要
做源数据库和备份双向同步到话,需要在源数据库和备份库同时创建数据库连接）

方案一。单向同步数据库快照，也就是源数据库数据改变时，对应备份数据库数据跟着改变，备份数据库数据改变不影响源数据库数据。
（1）.备份机配置连接到主数据库( 192.168.2.61)的 oracle\product\10.2.0\db_1\NETWORK\ADMIN 找到tnsname.ora这个文件 添加一下备份
MASTER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.61)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
（2）.在备份数据库创建私有的数据库连接 database link
在这边要阐述一下，分别登录主备机sys数据库分别创建ismc 数据库用户 并给他们赋予对应的权限 grant connect to ismc， grant resource to ismc两个基本权限
在给备份数据库imsc 赋予grant create database link to ismc,grant create any snapshot to ismc 数据库连接和数据库快照的权限

–conn ismc/ismc@backup 创建私有连接 grant create database link to ismc 登录备份库 以下操作都在改用户下操作
create database link ismcMaster.zz.com connect to ismc identified by ismc using ‘master’; （master 其实就是上面配置的那个MASTER ）

（3）.在源数据库和备份数据库 创建表

create table test_user
(id number(10) primary key,
name varchar(2),
age number(3)
);

 --测试数据库连接是否成功
select * from test_user@ismcMaster.zz.com;

（4）.创建数据库快照 目的数据库
create snapshot sn_test_user as select * from test_user@ismcMaster.zz.com;

–在源数据库创建快照日志 看快照是否执行成功
create snapshot log on test_user;

（5）.创建单向同步刷新 源数据改变目标数据库改变 目标数据库改变原数据库不改变 的触发器
create or replace trigger ismc.tri_test_user_afr
after insert or update or delete on ismc.sn_test_user
for each row
begin
if deleting then
delete from test_user where id=:old.id;
end if;
if inserting then
insert into test_user(id,name,age)
values(:new.id,:new.name,:new.age);
end if;
if updating then
update test_user set name=:new.name where id=:old.id;
end if;
end tri_test_user_afr;

（6）. 设置快照的刷新时间（只能选择一种刷新方式，推荐使用快速刷新，这样才可以用触发器双向同步）快速刷新 这个只适合做测试时候使用
真正项目中我们应该考虑正确权衡时间来做数据库快照是数据库的性能达到最大化
alter snapshot sn_test_user refresh fast start with sysdate next sysdate with primary key;

完全刷新 oracle 自动在30秒进行第一次完成刷新，以后每个30秒完全刷新一次
alter snapshot sn_test_user refresh complete start with sysdate+1/86400 next sysdate+1/86400;

手动刷新快照 字啊没有自动刷新的情况下，可以手动刷新快照
–手动刷新方式1
begin
dbms_refresh.refresh(‘sn_test_user’);
end;
–手动刷新方式2//第一个参数是快照，第二个参数F是快照刷新 C 是完全刷新
exec dbms_snapshot.refresh(‘sn_test_user’,‘f’);

（7）.查看快照最后一次刷新时间
select name,last_refresh from all_snapshot_refresh_times;

（8）.查看快照下次执行时间
select last_date,next_date, what from user_jobs order by next_date;

测试：在源数据库插入一条数据或删除一条数据库 查看备份数据库是否有数据改变
insert into test_user(id,name,age)
values(1,‘12’,2);

在备份数据库查看以下信息
select * from test_user;
select * from test_user@ismcmaster.zz.com;

–查看快照日志
select * from RUPD${}_{T}es{t}_{u}ser;select\ast fromMlog$_test_user;

如果Mlog$_test_user;如果在规定时间也就是创建快照刷新时间超过还出现以上日志说明快照没有成功
原因有很多种情况。可能是数据库快照没有创建好造成的。

方案二。双向同步数据库快照，也就是源数据库数据改变时，对应备份数据库数据跟着改变，备份数据库数据改变源数据库数据也跟着改变。

（1）.备份机配置连接到主数据库( 192.168.2.61)的 oracle\product\10.2.0\db_1\NETWORK\ADMIN 找到tnsname.ora这个文件 添加一下备份
MASTER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.61)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

源数据库上添加 oracle\product\10.2.0\db_1\NETWORK\ADMIN 找到tnsname.ora这个文件 添加一下备份
BACKUP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.98)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = djqz)
)
)
（2）.在备份数据库创建私有的数据库连接 database link
在这边要阐述一下，分别登录主备机sys数据库分别创建ismc 数据库用户 并给他们赋予对应的权限 grant connect to ismc， grant resource to ismc两个基本权限
在给备份数据库imsc 赋予grant create database link to ismc,grant create any snapshot to ismc 数据库连接和数据库快照的权限

–conn ismc/ismc@backup 创建私有连接 grant create database link to ismc
create database link ismcMaster.zz.com connect to ismc identified by ismc using ‘master’; （master 其实就是上面配置的那个MASTER ）

 --conn ismc/ismc@master 创建私有连接 grant create  database link to ismc  
create database link ismcbackup.zz.com connect to ismc identified by ismc using 'backup';

（3）.在源数据库和备份数据库 创建表

create table test_user
(id number(10) primary key,
name varchar(2),
age number(3)
);

 --测试数据库连接是否成功
select * from test_user@ismcMaster.zz.com;
select * from test_user@ismcbackup.zz.com ;

（4）. 源和备份数据库 创建数据库快照
create snapshot sn_test_user as select * from test_user@ismcMaster.zz.com;

–在源备份数据库创建快照日志 看快照是否执行成功
create snapshot log on test_user;

（5）.主备份 执行双向同步，请在源数据库中执行前6不，并在双方都创建以下触发器（当源数据库表改变时，目的数据库表跟着改变，目的数据库表改变时源数控表跟着改变）
create or replace trigger ismc.tri_test_user_afr
after delete or insert or update
on ismc.SN_TEST_User
referencing new as new old as old
for each row
declare
tmp_id number(10):=-1;

begin
dbms_output.put_line(‘begin’);

–insert data
if inserting then
–select id into temp_id form test_user where id=:new.id;
for p in(select id from test_user where id=:new.id)
loop
tmp_id:=p.id;，
end loop;

 dbms_output.put_line(tmp_id||'======---------------');
 if(tmp_id=-1) then
 insert into test_user(id,name,age)
 values(:new.id,:new.name,:new.age);
 end if;

end if;

–update data
if updating then
dbms_output.put_line(‘update’);
for p in(select name,age from test_user where id=:old.id)
loop
if(p.name!=:new.name) or (p.age!=:new.age) then
update test_user set name=:new.name,age=:new.age,state=1 where id=:old.id;
end if;
end loop;
end if;

—deleting data
if deleting then
dbms_output.put_line(‘deleted’);
delete from test_user where id=:old.id;
end if;
dbms_output.put_line(‘end’);

end tri_test_user_afr;

（6）. 在主备份数据库 设置快照的刷新时间（只能选择一种刷新方式，推荐使用快速刷新，这样才可以用触发器双向同步）快速刷新 这个只适合做测试时候使用
真正项目中我们应该考虑正确权衡时间来做数据库快照是数据库的性能达到最大化
alter snapshot sn_test_user refresh fast start with sysdate next sysdate with primary key;

完全刷新 oracle 自动在30秒进行第一次完成刷新，以后每个30秒完全刷新一次
alter snapshot sn_test_user refresh complete start with sysdate+(30/246060) next sysdate+(30/246060);

手动刷新快照 字啊没有自动刷新的情况下，可以手动刷新快照
–手动刷新方式1
begin
dbms_refresh.refresh(‘sn_test_user’);(全表刷新数据 把全表数据全部更新过来 如果要在快照表添加个标示字段来标示刚更新过来的数据话,采用这种手动刷新方式是不合适,可用第二种刷新方式的快速刷新 完全刷新也是全表刷新)
end;
–手动刷新方式2//第一个参数是快照，第二个参数F是快照刷新 C 是完全刷新
exec dbms_snapshot.refresh(‘sn_test_user’,‘f’);

（7）.查看快照最后一次刷新时间
select name,last_refresh from all_snapshot_refresh_times;

（8）.查看快照下次执行时间
select last_date,next_date, what from user_jobs order by next_date;

select last_refresh_type,last_refresh_date from user_mviews;

测试：在源备份是数据库分别插入一条数据或删除一条数据库 查看备份数据库是否有数据改变
insert into test_user(id,name,age)
values(1,‘12’,2);

在源备份数据库查看以下信息
select * from test_user;
select * from test_user@ismcmaster.zz.com;
select * from test_user@ismcbackup.zz.com;
–查看快照日志
select * from RUPD${}_{T}es{t}_{u}ser;select\ast fromMlog$_test_user;

如果Mlog$_test_user;如果在规定时间也就是创建快照刷新时间超过还出现以上日志说明快照没有成功
原因有很多种情况。可能是数据库快照没有创建好造成的。

总结
数据库快照只能针对库里面表以表的快照。主备表之间的表结构要一致，
但可以根据需求在备份表加数据库字段 作为标记也是可以，但具体实现
标记操作的值可以根据触发器更新插入。
数据库快照日志：是查看是否数据库快照成功的依据，如果该数据库快照日志表有数据存在则说明，有数据快照失败需要进行处理。