oracle高级复制文库.doc

oracle高级复制文库.txt35温馨是大自然的一抹色彩，独具慧眼的匠师才能把它表现得尽善尽美；温馨是乐谱上的一个跳动音符，感情细腻的歌唱者才能把它表达得至真至纯首先配置需要复制的两台数据库tns保证能相互访问用system身份登录主数据库（注意：你创建的dblinkName必须和Connection_Server(想要连接的数据库)的 global_name相同）sqlplus system/sysdbalter database rename global_name to ;用system身份登录备库sqlplus system/systestalter database rename global_name to ;用system身份登录主库sqlplus system/sysdb create public database link using test;用system身份登录备库sqlplus system/systestcreate public database link using db;用system身份分别登录主备数据库，测试数据库全局名称和公共的数据库链接select * from global_namedb;select * from global_;select * from global_nametest;select * from global_;在主备机上分别建好需要传输表，并赋予相应权限conn sys/sysdb as sysdbadrop user ismc cascade;create user ismc identified by ismc default tablespace users temporary tablespace temp;grant connect,resource to ismc;grant execute on sys.dbms_defer to ismc;SQL conn ismc/ismcdb(备机使用test)SQL create table orig_rept (report_id number(5) primary key,title varchar2(16),info varchar2(16) );SQL create sequence seq_rpt_id increment by 1 start with 1 maxvalue 100 cycle nocache; （备机不用创建）建立管理数据库复制的用户repadmin，并赋予权限用sys身份登录db数据库SQL conn sys/sysdb as sysdbadrop user repadmin cascade;create user repadmin identified by repadmin default tablespace users temporary tablespace temp;execute dbms_defer_sys.register_propagator(repadmin);grant execute any procedure to repadmin;execute dbms_repcat_admin.grant_admin_any_repgroup(repadmin);grant comment any table to repadmin;grant lock any table to repadmin;execute dbms_repcat_admin.grant_admin_any_schema(username=repadmin);在主备机上分别创建私有数据库链接用repadmin身份登录主机数据库conn repadmin/repadmindbcreate database link connect to repadmin identified by repadmin;select * from global_nametest;select * from global_;用repadmin身份登录备机数据库conn repadmin/repadmintestcreate database link connect to repadmin identified by repadmin;SQL select * from global_namedb;GLOBAL_NAME-DB.ZZ.COMSQL select * from global_;GLOBAL_NAME-DB.ZZ.COM在主机上创建复制组zz_rept_group用repadmin身份登录db（test）conn repadmin/repadmindbexecute dbms_repcat.create_master_repgroup(zz_rept_group);在复制组zz_rept_group里加入复制对象conn repadmin/repadmindb;execute dbms_repcat.create_master_repobject(sname=ismc,oname=orig_rept,type=table,use_existing_object = true,gname=zz_rept_group);-sname :用户名称-oname ：对象名称-type ：复制对象类别：表，索引，同义词，触发器，视图，过程，函数，程序包，程序包体-use_existing_object true 使用在主复制节点已经存在的数据库对象-gname 主复制组名生成复制对象支持，即通知给相关服务器conn repadmin/repadmindbexecut dbms_repcat.generate_replication_support(ismc,orig_rept,table);创建主复制节点，此步会将复制信息写入到备机数据库中conn repadmin/repadmindbexecute dbms_repcat.add_master_database(gname = zz_rept_group,master = ,use_existing_objects = true,copy_rows = false,propagation_mode = asynchronous);- gname 主复制组名- master 加入主复制节点的另一个数据库-use_existing_object true 表示用主复制节点已经存在的数据库对象-copy_rows false 表示第一次开始复制时不用和主复制节点保持一致-propagation_mode 异步地执行如果此步报：ora-23375数据库版本不兼容问题分别在主备机上执行execute dbms_repcat_admin.grant_admin_any_schema(username = repadmin);然后再执行上面那步激活复制组，状态由quiesced改为normal检查之前状态conn repadmin/repadmindbSQL select gname,master,status from dba_repgroup;SQL conn repadmin/repadmintestSQL select gname,master,status from dba_repgroup;激活SQL conn repadmin/repadmindbConnected to Oracle Database 10g Enterprise Edition Release .0 Connected as repadminSQL execute dbms_repcat.resume_master_activity(zz_rept_group,true);再次检查状态,确保两边激活成功。conn repadmin/repadmindbSQL select gname,master,status from dba_repgroup;SQL conn repadmin/repadmintestSQL select gname,master,status from dba_repgroup;创建复制数据库的时间表-设置为1分钟一次，及1/1440天SQL conn repadmin/repadmindbSQL begin 2 dbms_defer_sys.schedule_push ( 3 destination=, 4 interval=sysdate+1/1440, 5 next_date=sysdate); 6 end;SQL begin 2 dbms_defer_sys.schedule_purge ( 3 next_date=sysdate, 4 interval=sysdate+1/1440, 5 delay_seconds=0, 6 rollback_segment=); 7 end; 8 /commit；以repadmin身份登录test数据库；SQL begin 2 dbms_defer_sys.schedule_push ( 3 destination=, 4 interval=sysdate+1/1440, 5 next_date=sysdate); 6 end; 7 /SQL begin 2 dbms_defer_sys.schedule_purge ( 3 next_date=sysdate, 4 interval=sysdate+1/1440, 5 delay_seconds=0, 6 rollbac