ORACLE10g Stream表级复制配置
本次实验在vmware9.0环境下进行，安装的操作系统CentOS-6.4-x86_64,数据库版本是ORACLE10g10.2.0.1，源库和目标库都安装了数据库，源库ORACLE_SID=myorcl，目标库ORACLE_SID=orcl
2.在源库与目标库初始化参数设置
在源库操作如下：
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
myorcl OPEN
SQL> alter system set aq_tm_processes=1 scope=spfile; --队列等待时间
SQL> alter system set job_queue_processes=2 scope=spfile; --至少为2
SQL> alter system set global_names=true scope=spfile; --用来控制database link同名
SQL> alter database rename global_name to myorcl.net;
SQL> alter system set streams_pool_size=50m scope=spfile;
重启数据库并查看设置的global_name，如下：
SQL> shutdown immediate
SQL> startup
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
MYORCL.NET
在目标库操作，如下：
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
orcl OPEN
SQL> alter system set aq_tm_processes=1 scope=spfile;
SQL> alter system set job_queue_processes=2 scope=spfile;
SQL> alter system set global_names=true scope=spfile;
SQL> alter database rename global_name to orcl.net;
SQL> alter system set streams_pool_size=50m scope=spfile;
SQL> shutdown immediate
重启数据库并查看设置的global_name，如下：
SQL> shutdown immediate
SQL> startup
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL.NET
3.在源库和目标库配置tnsnames.ora，如下：
primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.6.118)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = myorcl)
)
)
standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.6.119)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
测试能否连通，如下：
首先关闭防火墙，service iptables stop
在源库测试，如下：
[oracle@dayong ~]$ tnsping standby
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 03-APR-2014 11:47:41
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.6.119)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (10 msec)
在目标库测试，如下：
[oracle@xiaoru ~]$ tnsping primary
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 03-APR-2014 11:53:24
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.6.118)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = myorcl)))
OK (0 msec)
4.源库和目标库复制管理员的创建
不能使用sys和system作为流管理员，流管理员不能使用system表空间作为默认表空间；
源库操作如下：
SQL> create tablespace streamtbs datafile '/u01/app/oradata/myorcl/streamtbs.dbf' size 100m;
SQL> create user streamadmin identified by oracle default tablespace streamtbs quota unlimited on streamtbs;
SQL> grant connect,resource,dba to streamadmin;
目标库操作如下：
SQL> create tablespace streamtbs datafile '/u01/app/oradata/orcl/streamtbs.dbf' size 100m;
SQL> create user streamadmin identified by oracle default tablespace streamtbs quota unlimited on streamtbs;
SQL> grant connect,resource,dba to streamadmin;
5.源库和目标库创建互连的database link
源库操作如下：
SQL> conn streamadmin/oracle@primary
SQL> create database link orcl.net connect to streamadmin identified by oracle using 'standby';
SQL> select * from dual@orcl.net; --测试
D
-
X
查看创建的DB_LINK，如下：
SQL> conn / as sysdba
SQL> desc dba_db_links;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
DB_LINK NOT NULL VARCHAR2(128)
USERNAME VARCHAR2(30)
HOST VARCHAR2(2000)
CREATED NOT NULL DATE
SQL> col owner for a15;
SQL> col db_link for a15;
SQL> col username for a15;
SQL> col host for a15;
SQL> select owner,db_link,username,host from dba_db_links;
OWNER DB_LINK USERNAME HOST
--------------- --------------- --------------- ---------------
STREAMADMIN ORCL.NET STREAMADMIN standby
目标库操作如下：
SQL> connect streamadmin/oracle@standby
SQL> create database link myorcl.net connect to streamadmin identified by oracle using 'primary';
Database link created.
SQL> select * from dual@myorcl.net;
D
-
X
6.查看源库和目标数据库是否处于归档模式，如果不是归档模式，必须设置处于归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/my_arch
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
7.创建测试用的表空间和表
在源库上操作如下：
SQL> create user huyong identified by huyong default tablespace streamtbs quota unlimited on streamtbs;
SQL> grant connect,resource to huyong;
SQL>CONN HUYONG/HUYONG;
SQL>create table test01(id number,name varchar2(20));
SQL>create table test02(id number,name varchar2(20));
SQL>create table test03(id number,name varchar2(20));
在目标数据库操作如下：
SQL>create user huyong identified by huyong default tablespace streamtbs quota unlimited on streamtbs;
SQL>grant connect,resource to huyong;
8.在源库上执行maintain_tables过程
SQL> connect streamadmin/oracle@primary
Connected.
SQL> declare
2 v_tables DBMS_UTILITY.UNCL_ARRAY;
3 begin
4 v_tables(1) := 'huyong.test01';
5 v_tables(2) := 'huyong.test02';
6 v_tables(3) := 'huyong.test03';
7 dbms_streams_adm.maintain_tables(
8 table_names => v_tables,
9 source_directory_object => null,
10 destination_directory_object => null,
11 source_database => 'myorcl.net',
12 destination_database => 'orcl.net',
13 perform_actions => true,
14 bi_directional => true,
15 include_ddl => true,
16 instantiation => dbms_streams_adm.instantiation_table_network);
17 end;
18 /
PL/SQL procedure successfully completed.
批注：此操作过程中打开另一个终端查看tail -f /u01/app/admin/myorcl/bdump/alert_myorcl.log 日志，观察是否有错误信息
9.验证
查看目标端数据库的huyong用户下是否实现表的同步
SQL> conn huyong/huyong
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST01 TABLE
TEST02 TABLE
TEST03 TABLE
在源端数据库的huyong用户下插入数据，如下：
SQL> conn huyong/huyong;
Connected.
SQL> insert into test01 values (1,'huyong');
SQL> insert into test02 values (1,'huyong');
SQL> insert into test03 values (1,'huyong');
SQL> commit;
在目标端数据库验证，如下：
SQL> conn huyong/huyong;
Connected.
SQL> select * from test01;
ID NAME
---------- --------------------
1 huyong
SQL> select * from test02;
ID NAME
---------- --------------------
1 huyong
SQL> select * from test03;
ID NAME
---------- --------------------
1 huyong
继续验证，在目标端插入数据，如下：
SQL> insert into test01 values (2,'xiaoru');
SQL> commit;
在源端进行验证，如下：
SQL> select * from test01;
ID NAME
---------- --------------------
1 huyong
2 xiaoru
可知已经完成了数据的同步。
补充：
现由于业务需要，增加新的表并实现数据库之间表级同步，操作如下：
在源库操作如下：
SQL> conn huyong/huyong;
SQL> create table test04 as select * from test01;
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST02 TABLE
TEST01 TABLE
TEST03 TABLE
TEST04 TABLE
SQL> conn streamadmin@primary
Enter password:
Connected.
SQL> declare
2 v_tables DBMS_UTILITY.UNCL_ARRAY;
3 begin
4 v_tables(1) := 'huyong.test04';
5 dbms_streams_adm.maintain_tables(
6 table_names => v_tables,
7 source_directory_object => null,
8 destination_directory_object => null,
9 source_database => 'myorcl.net',
10 destination_database => 'orcl.net',
11 perform_actions => true,
12 bi_directional => true,
13 include_ddl => true,
14 instantiation => dbms_streams_adm.instantiation_table_network);
15 end;
16 /
PL/SQL procedure successfully completed.
在目标库查看是否完成表的同步：
SQL> conn huyong/huyong;
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST04 TABLE
TEST01 TABLE
TEST02 TABLE
TEST03 TABLE
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/14288937/viewspace-1264058/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- 缺少log_archive_config导致归档路径被禁用
- [WK-T]ORACLE RAC +ASM Backup and Recovery(四)
- [WK-T]ORACLE RAC +ASM Backup and Recovery(三)
- [WK-T]ORACLE RAC +ASM Backup and Recovery(二)
- [WK-T]ORACLE RAC +ASM Backup and Recovery(一)
- [WK-T]ORACLE 10G RAC+ASM增加controlfile
- How to copy a datafile from ASM to a file system not using RMAN
- Oracle Flashback(二)
- Oracle Flashback(一)
- ORACLE10g Stream用户级复制配置
