VMCD.ORG » Down-Stream 配置一例

测试环境：

[oracle@liu ~]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Dec 26 21:53:23 2011

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

在source,destination 分别设置：

创建stream用户的表空间：
create tablespace tbs_stream datafile ‘/oracle/app/oracle/oradata/*xx/tbs_stream02.dbf’ size 200M;

将logmnr的默认表空间转到刚刚建立的表空间：
SQL> execute dbms_logmnr_d.set_tablespace(‘tbs_stream’);

PL/SQL procedure successfully completed.

创建stream user和授权：
SQL> CREATE USER strmadmin IDENTIFIED BY strmadmin
2 DEFAULT TABLESPACE tbs_stream
3 QUOTA UNLIMITED ON tbs_stream;

SQL> grant dba to strmadmin;

Grant succeeded.

SQL>
SQL>
SQL> BEGIN
2 DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
3 grantee => ‘strmadmin’,
4 grant_privileges => true);
END;
/ 5 6

设置tnsname

[oracle@yang admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LIU =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = liu)
)
)

STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = standby)
)
)

CXX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = yang)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cxx)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

AXX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = liu)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = axx)
)
)

设置global_names
SQL> Alter system set global_names=TRUE scope=BOTH;

设置stream需要的初始化参数：

alter system set aq_tm_processes=2 scope=both;
alter system set global_names=true scope=both;
alter system set job_queue_processes=10 scope=both;
alter system set parallel_max_servers=20 scope=both;
alter system set undo_retention=3600 scope=both;
alter system set streams_pool_size=25M scope=spfile;
alter system set utl_file_dir=’*’ scope=spfile;
alter system set open_links=4 scope=spfile;

建立dblink。
在source机：
create database link cxx connect to strmadmin identified by strmadmin using ‘cxx’;
在destination机：
create database link axx connect to strmadmin identified by strmadmin using ‘axx’;

在source数据库启用Database 追加日志
alter database add supplemental log data;

在downstream site设置：

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=SPFILE;

alter system set log_archive_dest_1 = ‘LOCATION=/u01/arch’ scope=both;

注意创建standby redo时，大小要和source site的redo大小一致，数量上要比source site的redo多一个。

在downstream site：

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
(‘/u01/app/oracle/oradata/cxx/stdy_redo04.log’) SIZE 150M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
(‘/u01/app/oracle/oradata/cxx/stdy_redo05.log’) SIZE 150M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
(‘/u01/app/oracle/oradata/cxx/stdy_redo06.log’) SIZE 150M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7
(‘/u01/app/oracle/oradata/cxx/stdy_redo07.log’) SIZE 150M;

准备source site的参数：

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=SPFILE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=cxx LGWR SYNC NOREGISTER
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=cxx’
SCOPE=SPFILE;

在source site创建需要复制的schema：

SQL> create user liu identified by liu default tablespace users;

User created.

SQL> grant connect, resource, create table to liu;

Grant succeeded.

SQL>create table liu(id int);

SQL>begin

for i in 1..20001 loop
insert into liu.liu values (i);
commit;
end loop;
end;
/

PL/SQL procedure successfully completed.

在downstream site设置stream queue：

conn strmadmin/strmadmin

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => ‘strmadmin.DOWNSTREAM_Q_TABLE’,
queue_name => ‘strmadmin.DOWNSTREAM_Q’,
queue_user => ‘STRMADMIN’);
END;
/

在downstream site创建apply进程：

conn strmadmin/strmadmin

BEGIN
DBMS_APPLY_ADM.CREATE_APPLY(
queue_name => ‘strmadmin.DOWNSTREAM_Q’,
apply_name => ‘DOWNSTREAM_APPLY’,
apply_captured => TRUE
);
END;
/

在downstream site创建capture进程：

conn strmadmin/strmadmin

BEGIN
DBMS_CAPTURE_ADM.CREATE_CAPTURE(
queue_name => ‘strmadmin.DOWNSTREAM_Q’,
capture_name => ‘DOWNSTREAM_CAPTURE’,
rule_set_name => NULL,
start_scn => NULL,
source_database => ‘axx’,
use_database_link => true,
first_scn => NULL,
logfile_assignment => ‘implicit’);
END;
/

在downstream site：

conn strmadmin/strmadmin

SQL> BEGIN
2 DBMS_CAPTURE_ADM.SET_PARAMETER(
3 capture_name => ‘DOWNSTREAM_CAPTURE’,
4 parameter => ‘downstream_real_time_mine’,
5 value => ‘y’);
6 END;
7 /

PL/SQL procedure successfully completed.

在downstream site设置rule规则：

BEGIN
DBMS_STREAMS_ADM.ADD_table_RULES(
table_name => ‘LIU.LIU’,
streams_type => ‘capture’,
streams_name => ‘downstream_capture’,
queue_name => ‘strmadmin.downstream_q’,
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => ‘axx’,
inclusion_rule => TRUE);
END;
/

导出测试表：

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
—————————————–
618212

[oracle@liu ~]$ exp system/oracle tables=liu.liu file=liu.dump log=liu.log object_consistent=Y

Export: Release 10.2.0.1.0 – Production on Mon Dec 26 21:47:31 2011

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path …
Current user changed to LIU
. . exporting table LIU 20001 rows exported
Export terminated successfully without warnings.
[oracle@liu ~]$ imp system/oracle@cxx file=liu.dump full=y ignore=y STREAMS_INSTANTIATION=Y

Import: Release 10.2.0.1.0 – Production on Mon Dec 26 21:50:36 2011

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYSTEM’s objects into SYSTEM
. importing LIU’s objects into LIU
. . importing table “LIU” 20001 rows imported
Import terminated successfully without warnings.

destination:设置apply scn

conn strmadmin/strmadmin

begin
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name =>’liu.liu’,
source_database_name =>’axx’,
instantiation_scn =>618212);
end;
/

在downstream site开始启动apply进程：

conn strmadmin/strmadmin

SQL> exec DBMS_APPLY_ADM.START_APPLY(apply_name => ‘DOWNSTREAM_APPLY’);

PL/SQL procedure successfully completed.

在downstream site启动capture进程：

conn strmadmin/strmadmin

SQL> exec DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => ‘DOWNSTREAM_CAPTURE’);

PL/SQL procedure successfully completed.

测试结果：

SQL> conn liu/liu
Connected.
SQL>
SQL> begin
2 for i in 1..10000 loop
3 insert into liu values (i);
4 commit;
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> alter system switch logfile;

[oracle@yang bdump]$ tail -f alert_cxx.log

RFS[8]: Successfully opened standby log 5: ‘/u01/app/slog5.log’
Mon Dec 26 21:53:58 2011
RFS LogMiner: Registered logfile [/u01/arch/1_30_770906800.dbf] to LogMiner session id [41]
Mon Dec 26 21:54:04 2011
LOGMINER: Archived logfile found, transition to mining logfile: /u01/arch/1_30_770906800.dbf
Mon Dec 26 21:54:04 2011
LOGMINER: End mining logfile: /u01/arch/1_30_770906800.dbf
Mon Dec 26 21:54:04 2011
LOGMINER: Begin mining logfile: /u01/app/slog5.log

SQL> select count(*) from liu;

COUNT(*)
———-
28935

SQL> /

COUNT(*)
———-
29432

SQL> /

COUNT(*)
———-
29566

SQL> /

COUNT(*)
———-
29703

SQL> /

COUNT(*)
———-
29798

SQL> /

COUNT(*)
———-
29927

SQL> /

COUNT(*)
———-
30001

SQL> /

COUNT(*)
———-
30001

复制完成