oracle 11g streams 配置问题
主数据库：操作系统：Red Hat Enterprise Linux5.8
IP地址：10.80.1.40
数据库：Oracle 11.2.0.3.0
ORACLE_SID：devdb
Global_name：devdb
从数据库：
操作系统：Red Hat Enterprise Linux5.8
IP地址：10.80.1.50
数据库：Oracle 11.2.0.3.0
ORACLE_SID：orcl
Global_name：orcl
目标：使用oracle streams 将10.80.1.40数据库上用户seal的数据同步到目标数据库10.80.1.50上面，我用的是owner级downstream方法来配置的。
1.设置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 nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile;
alter system set streams_pool_size=300M scope=spfile;
alter system set utl_file_dir='*' scope=spfile;
alter system set open_links=4 scope=spfile;
alter system set "_job_queue_interval"=1 scope=spfile;
alter system set log_archive_format='arch%t_%s_%r.arc' scope=spfile;
2.主库上创建strmadmin用户专用表空间
create tablespacestreams_tbsdatafile'+ORACLE_DATA/devdb/datafile/streams_tbs01.dbf '
size 300m autoextend on maxsize unlimited segment space management auto;
主库上创建Stream管理用户:
create user strmadmin identified by strmadmin
default tablespacestreams_tbs;
主库上授权Stream管理用户:
grant connect,resource,dba,aq_administrator_role to strmadmin;
SQL> begin
2dbms_streams_auth.grant_admin_privilege(
3grantee=>'strmadmin',
4grant_privileges=>TRUE);
5end;
6/
3.在10.80.1.50上面的strmadmin用户和表空间配置同主库类似。
4.主库配置网络连接
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.80.1.50)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
5.从库配置网络连接
DEVDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.80.1.40)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = devdb)
)
)
6.用strmadmin用户登录，建立dblink
主库：create database link ORCLconnect to strmadmin identified by strmadmin using 'ORCL';
从库：create database link DEVDBconnect to strmadmin identified by strmadmin using 'DEVDB';
在10.80.1.40上 select * from gloal_name@orcl 查询结果为 ORCL
在10.80.1.50上 select * from gloal_name@devdb查询结果为 DEVDB
dblink 配置成功。
7.在从库10.80.1.50设置
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=/home/oracle/standby_archVALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)' SCOPE=SPFILE;
8.在主从库上同时配置
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(devdb,orcl)' SCOPE=SPFILE;
9.在主库查询redo情况
SQL> select THREAD#, GROUP#, BYTES/1024/1024 from V$LOG;
THREAD# GROUP# BYTES/1024/1024
---------- ---------- ---------------
1 1 50
1 2 50
1 3 50
10.在从库创建standby redo时，大小要和source site的redo大小一致，数量上要比source site的redo多一个
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
('/home/oracle/data/orcl/redo04.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
('/home/oracle/data/orcl/redo05.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
('/home/oracle/data/orcl/redo06.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7
('/home/oracle/data/orcl/redo07.log') SIZE 50M;
11.在downstream site上检查一下是否建立成功:
SQL> SELECT GROUP#, THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$STANDBY_LOG;
GROUP# THREAD#SEQUENCE# ARCHIV STATUS
---------- ---------- ---------- ------ --------------------
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED
7 0 0 YES UNASSIGNED
12.在主库上配置参数:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orclLGWR SYNC NOREGISTER
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=orcl'
SCOPE=SPFILE;
13. 在 10.80.1.50上：
1.创建队列
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;
/
pl/sql completed successfully
2.创建应用进程
conn strmadmin/strmadmin
begin
dbms_apply_adm.create_apply(
queue_name => 'strmadmin.DOWNSTREAM_Q',
apply_name => 'DOWNSTREAM_APPLY',
apply_captured => TRUE );
end;
/
pl/sql completed successfully
3.创建捕获进程
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 => 'devdb',
use_database_link => true,
first_scn => NULL,
logfile_assignment => 'implicit');
end;
/
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00352: Unable to access another database 'devdb'
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_UTILITY@devdb' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 453
ORA-06512: at "SYS.DBMS_CAPTURE_ADM", line 289
ORA-06512: at line 2
在从库10.80.1.50上面创建捕获进程时报错，这里报错后面也配不下去了，麻烦大家帮我看看是怎么回事？给下详细的排错步骤和相关的命令！谢谢
页:
[1]