配置Advanced Replication
星期四, 三月 19th, 2009源端8.1.17.3 for hp-ux 目的端10.2.0.4 for linux
1导出表
exp system/oracle file=repexp.dmp TABLES=(DEF\$\_ERROR,REPCAT\$\_REPSCHEMA,DEF\$\_DESTINATION,DEF\$\_CALLDEST,DEF\$\_DEFAULTDEST,
DEF\$\_LOB,DEF\$\_TEMP\$LOB,DEF\$\_ORIGIN,DEF\$\_PUSHED\_TRANSACTIONS,DEF\$\_PROPAGATOR)
2 drop表
drop table system.DEF$_CALLDEST;
drop table system.DEF$_DEFAULTDEST;
drop table system.DEF$_ERROR;
drop table system.DEF$_ORIGIN;
drop table system.REPCAT$_REPSCHEMA;
drop table system.DEF$_DESTINATION;
drop table system.DEF$_LOB;
drop table system.DEF$_TEMP$LOB;
drop table system.DEF$_PUSHED_TRANSACTIONS;
drop table system.DEF$_PROPAGATOR;
3 system
execute dbms_aqadm.drop_queue_table('SYSTEM.DEF$_AQCALL',TRUE);
Execute dbms_aqadm.drop_queue_table('SYSTEM.DEF$_AQERROR',TRUE);
4 修改SYSTEM表空间属性
alter user system default tablespace ibmsf_rep quota unlimited on ibmsf_rep;
alter user system quota 0 on system;
revoke unlimited tablespace from system;
5 执行高级复制脚本
@?/rdbms/admin/catdefrt.sql;
6 导入备份的表
imp system/oracle file=repexp.dmp full=y ignore=y
7 修改回SYSTEM默认的表空间属性
alter user system default tablespace system quota unlimited on system;
grant unlimited tablespace to system;
8 运行编译过程
@?/rdbms/admin/utlrp.sql;
9 确认高级复制表已经转移
select * from dba_tables where tablespace_name='IBMSF_REP’
--------------------------------------------------以上操作在源端操作---------------------------------------------------
10 修改源端以下三个参数：
open_links=8
JOB_QUEUE_PROCESSES=10
PARALLEL_MAX_SERVERS=20
11 源端上创建REPADM用户
sqlplus / as sysdba
create user repadm identified by repadm;
exec DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA(USERNAME => 'repadm');
GRANT COMMENT ANY TABLE TO repadm;
GRANT LOCK ANY TABLE TO repadm;
exec DBMS_DEFER_SYS.register_propagator(username => 'repadm');
12 ）目的端创建高级复制用户
sqlplus / as sysdba
create user repadm identified by repadm;
exec DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA(USERNAME => 'repadm');
GRANT COMMENT ANY TABLE TO repadm;
GRANT LOCK ANY TABLE TO repadm;
grant select any dictionary to repadm;
exec DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP(USERNAME => 'repadm',PRIVILEGE_TYPE => 'receiver',LIST_OF_GNAMES => NULL);
13 在源端TNSNAMES添加以下服务
TAIPCC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = *)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = taipcc)
(INSTANCE_NAME = taipcc1)
(FAILOVER_MODE =
(BACKUP = taipcc2)
(TYPE = select)
(METHOD = preconnect)
)
)
)
TAIPCC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = *)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = taipcc)
(INSTANCE_NAME = taipcc2)
)
)
14 源端创建以下DB LINK
sqlplus / as sysdba
CREATE PUBLIC DATABASE LINK TAIPCC USING 'TAIPCC';
sqlplus repadm/repadm
CREATE DATABASE LINK TAIPCC CONNECT TO repadm IDENTIFIED BY repadm;
15 目的端创建以下DB LINK
sqlplus / as sysdba
CREATE PUBLIC DATABASE LINK IBMSF USING 'IBMSF';
sqlplus repadm/repadm
CREATE DATABASE LINK IBMSF CONNECT TO repadm IDENTIFIED BY repadm;
16 在源端添加复制组和复制站点
sqlplus repadm/repadm
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP (
gname => 'REP1');
END;
/
BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => 'REP1',
master => 'TAIPCC',
use_existing_objects => TRUE,
copy_rows => FALSE,
propagation_mode => 'ASYNCHRONOUS');
END;
/
17 在源端添加复制对象以及对象支持 参加脚本
select 'exec DBMS_REPCAT.CREATE_MASTER_REPOBJECT (gname => ' || '''REP1''' || ',' ||
'type => ' || '''TABLE''' || ',' || 'oname =>' || '''' || '' ||
table_name || '''' || ',' || 'sname => ' || '''IBMSF''' || ',' ||
'use_existing_object => TRUE' || ',' || 'copy_rows => FALSE);'
from user_tables
select 'exec DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(sname => ' || '''IBMSF''' ||
',' || 'oname =>' || '''' || '' ||
table_name || '''' || ','||'type => '||'''TABLE''' ||',' ||
'min_communication => TRUE);'
from user_tables
18 添加复制JOB（测试用5分钟一次，测试完毕后修改为每天晚上1点执行）
exec DBMS_DEFER_SYS.SCHEDULE_PUSH (destination => 'TAIPCC',interval => 'SYSDATE+1/288',next_date => SYSDATE,parallelism => 2);
19 在源端启用高级复制
execute dbms_repcat.resume_master_activity('rep1',true);
20 手动执行高级复制的命令
Set serverout on
DECLARE
temp INTEGER;
BEGIN
temp := DBMS_DEFER_SYS.PUSH (
destination => 'TAIPCC',
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 1);
dbms_output.put_line(temp);
END;
1导出表
exp system/oracle file=repexp.dmp TABLES=(DEF\$\_ERROR,REPCAT\$\_REPSCHEMA,DEF\$\_DESTINATION,DEF\$\_CALLDEST,DEF\$\_DEFAULTDEST,
DEF\$\_LOB,DEF\$\_TEMP\$LOB,DEF\$\_ORIGIN,DEF\$\_PUSHED\_TRANSACTIONS,DEF\$\_PROPAGATOR)
2 drop表
drop table system.DEF$_CALLDEST;
drop table system.DEF$_DEFAULTDEST;
drop table system.DEF$_ERROR;
drop table system.DEF$_ORIGIN;
drop table system.REPCAT$_REPSCHEMA;
drop table system.DEF$_DESTINATION;
drop table system.DEF$_LOB;
drop table system.DEF$_TEMP$LOB;
drop table system.DEF$_PUSHED_TRANSACTIONS;
drop table system.DEF$_PROPAGATOR;
3 system
execute dbms_aqadm.drop_queue_table('SYSTEM.DEF$_AQCALL',TRUE);
Execute dbms_aqadm.drop_queue_table('SYSTEM.DEF$_AQERROR',TRUE);
4 修改SYSTEM表空间属性
alter user system default tablespace ibmsf_rep quota unlimited on ibmsf_rep;
alter user system quota 0 on system;
revoke unlimited tablespace from system;
5 执行高级复制脚本
@?/rdbms/admin/catdefrt.sql;
6 导入备份的表
imp system/oracle file=repexp.dmp full=y ignore=y
7 修改回SYSTEM默认的表空间属性
alter user system default tablespace system quota unlimited on system;
grant unlimited tablespace to system;
8 运行编译过程
@?/rdbms/admin/utlrp.sql;
9 确认高级复制表已经转移
select * from dba_tables where tablespace_name='IBMSF_REP’
--------------------------------------------------以上操作在源端操作---------------------------------------------------
10 修改源端以下三个参数：
open_links=8
JOB_QUEUE_PROCESSES=10
PARALLEL_MAX_SERVERS=20
11 源端上创建REPADM用户
sqlplus / as sysdba
create user repadm identified by repadm;
exec DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA(USERNAME => 'repadm');
GRANT COMMENT ANY TABLE TO repadm;
GRANT LOCK ANY TABLE TO repadm;
exec DBMS_DEFER_SYS.register_propagator(username => 'repadm');
12 ）目的端创建高级复制用户
sqlplus / as sysdba
create user repadm identified by repadm;
exec DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA(USERNAME => 'repadm');
GRANT COMMENT ANY TABLE TO repadm;
GRANT LOCK ANY TABLE TO repadm;
grant select any dictionary to repadm;
exec DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP(USERNAME => 'repadm',PRIVILEGE_TYPE => 'receiver',LIST_OF_GNAMES => NULL);
13 在源端TNSNAMES添加以下服务
TAIPCC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = *)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = taipcc)
(INSTANCE_NAME = taipcc1)
(FAILOVER_MODE =
(BACKUP = taipcc2)
(TYPE = select)
(METHOD = preconnect)
)
)
)
TAIPCC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = *)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = taipcc)
(INSTANCE_NAME = taipcc2)
)
)
14 源端创建以下DB LINK
sqlplus / as sysdba
CREATE PUBLIC DATABASE LINK TAIPCC USING 'TAIPCC';
sqlplus repadm/repadm
CREATE DATABASE LINK TAIPCC CONNECT TO repadm IDENTIFIED BY repadm;
15 目的端创建以下DB LINK
sqlplus / as sysdba
CREATE PUBLIC DATABASE LINK IBMSF USING 'IBMSF';
sqlplus repadm/repadm
CREATE DATABASE LINK IBMSF CONNECT TO repadm IDENTIFIED BY repadm;
16 在源端添加复制组和复制站点
sqlplus repadm/repadm
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP (
gname => 'REP1');
END;
/
BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => 'REP1',
master => 'TAIPCC',
use_existing_objects => TRUE,
copy_rows => FALSE,
propagation_mode => 'ASYNCHRONOUS');
END;
/
17 在源端添加复制对象以及对象支持 参加脚本
select 'exec DBMS_REPCAT.CREATE_MASTER_REPOBJECT (gname => ' || '''REP1''' || ',' ||
'type => ' || '''TABLE''' || ',' || 'oname =>' || '''' || '' ||
table_name || '''' || ',' || 'sname => ' || '''IBMSF''' || ',' ||
'use_existing_object => TRUE' || ',' || 'copy_rows => FALSE);'
from user_tables
select 'exec DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(sname => ' || '''IBMSF''' ||
',' || 'oname =>' || '''' || '' ||
table_name || '''' || ','||'type => '||'''TABLE''' ||',' ||
'min_communication => TRUE);'
from user_tables
18 添加复制JOB（测试用5分钟一次，测试完毕后修改为每天晚上1点执行）
exec DBMS_DEFER_SYS.SCHEDULE_PUSH (destination => 'TAIPCC',interval => 'SYSDATE+1/288',next_date => SYSDATE,parallelism => 2);
19 在源端启用高级复制
execute dbms_repcat.resume_master_activity('rep1',true);
20 手动执行高级复制的命令
Set serverout on
DECLARE
temp INTEGER;
BEGIN
temp := DBMS_DEFER_SYS.PUSH (
destination => 'TAIPCC',
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 1);
dbms_output.put_line(temp);
END;
