search
HomeDatabaseMysql TutorialOracle简单stream一个用户单向复制配置

如果STATUS状态是ENABLED,表示Apply进程运行正常; 如果STATUS状态是DISABLED,表示Apply进程处于停止状态,只需重新启动

以下我做streamm同步两台机器间的某一个用户的操作步骤。很简单,看到stream还有很多要看的。

  1.环境介绍

  主数据库SID : obpm

  操作系统 :    win 2003

  IP地址   :  192.168.0.1

  Global_name :

  Oracle version: 10.2.0.1

  从数据库SID: orcl

  操作系统 :    win 2003

  IP地址   :  192.168.0.2

  Global_name :

  oracle version: 10.2.0.1

  2.在主/从数据库设置初始化参数(在主从数据库都执行以下操作)

  1) sqlplus / as sysdba ----以dba身份登录

  2)alter system set aq_tm_processes=2 scope=both;   ---启用对队列消息的时间监视

  alter system set global_names=true scope=both;   ---设置全局名称为true

  ---alter system set job_queue_processes=10 scope=both; --设置定时作业进程最大为10个,默认是10

  alter system set undo_retention=3600 scope=both;   --设置回滚段时间,默认是900

  alter system set streams_pool_size=25M scope=spfile; --sga设置为自动调整情况下不需设置该参数

  3)重启动两个数据库

  3.设置主数据库为归档mode

  1)archive log list; --查是否归档,如是归档,请忽略第3点

  2)shutdown immediate;

  startup mount;

  alter database archivelog;

  alter system set LOG_ARCHIVE_DEST_1='LOCATION=D:\arc\';  ---设置归档目录

  alter system switch logfile; --相应目录检查是否生成arc文件

  alter database open;

  4.主/从数据新建stream管理用户(在主从数据库都执行以下操作)

  1)sqlplus / as sysdba;

  2)create tablespace tbs_stream datafile 'E:\tbs_stream01.dbf' size 100m autoextend on maxsize unlimited segment space management auto; ---创建主环境的Stream专用表空间

  3)execute dbms_logmnr_d.set_tablespace('tbs_stream'); --将logminer的数据字典从system表空间转移到新建的表空间,,防止撑满system表空间

  4)create user strmadmin identified by strmadmin default tablespace tbs_stream temporary tablespace temp;  --创建用户

  5)grant dba to strmadmin;---直接给dba权限.

  5.主数据库网络环境设置

  1)主数据库(tnsnames.ora)中添加从数据库的配置。(也可以通过oracle图形工具net manager配置)

  orcl =

  (DESCRIPTION =

  (ADDRESS_LIST =

  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))

  )

  (CONNECT_DATA =

  (SERVER = DEDICATED)

  (SERVICE_NAME = orcl)

  )

  )

  2)测试是否连的通(cmd下面输入以下命令)

  sqlplus strmadmin/strmadmin@orcl

  3)新建orcl的dblink

  sqlplus strmadmin/strmadmin

  create database link orcl connect to strmadmin identified by strmadmin using 'orcl';

  ----global_names为true的情况下,主数据库建立的数据库链的名字必须和从数据库的global_name相同

  select sysdate from dual@orcl;  --测试dblink是否有效

  6.从数据库网络环境设置

  1)从数据库(tnsnames.ora)中添加主数据库的配置。(也可以通过oracle图形工具net manager配置)

  obpm =

  (DESCRIPTION =

  (ADDRESS_LIST =

  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521))

  )

  (CONNECT_DATA =

  (SERVER = DEDICATED)

  (SERVICE_NAME = obpm)

  )

  )

  2)测试是否连的通(cmd下面输入以下命令)

  sqlplus strmadmin/strmadmin@obpm

  3)新建obpm的dblink

  sqlplus strmadmin/strmadmin

  create database link obpm connect to strmadmin identified by strmadmin using 'obpm';

  ----global_names为true的情况下,主数据库建立的数据库链的名字必须和从数据库的global_name相同

  select sysdate from dual@obpm;  --测试dblink是否有效

linux

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
What are the differences in syntax between MySQL and other SQL dialects?What are the differences in syntax between MySQL and other SQL dialects?Apr 27, 2025 am 12:26 AM

MySQLdiffersfromotherSQLdialectsinsyntaxforLIMIT,auto-increment,stringcomparison,subqueries,andperformanceanalysis.1)MySQLusesLIMIT,whileSQLServerusesTOPandOracleusesROWNUM.2)MySQL'sAUTO_INCREMENTcontrastswithPostgreSQL'sSERIALandOracle'ssequenceandt

What is MySQL partitioning?What is MySQL partitioning?Apr 27, 2025 am 12:23 AM

MySQL partitioning improves performance and simplifies maintenance. 1) Divide large tables into small pieces by specific criteria (such as date ranges), 2) physically divide data into independent files, 3) MySQL can focus on related partitions when querying, 4) Query optimizer can skip unrelated partitions, 5) Choosing the right partition strategy and maintaining it regularly is key.

How do you grant and revoke privileges in MySQL?How do you grant and revoke privileges in MySQL?Apr 27, 2025 am 12:21 AM

How to grant and revoke permissions in MySQL? 1. Use the GRANT statement to grant permissions, such as GRANTALLPRIVILEGESONdatabase_name.TO'username'@'host'; 2. Use the REVOKE statement to revoke permissions, such as REVOKEALLPRIVILEGESONdatabase_name.FROM'username'@'host' to ensure timely communication of permission changes.

Explain the differences between InnoDB and MyISAM storage engines.Explain the differences between InnoDB and MyISAM storage engines.Apr 27, 2025 am 12:20 AM

InnoDB is suitable for applications that require transaction support and high concurrency, while MyISAM is suitable for applications that require more reads and less writes. 1.InnoDB supports transaction and bank-level locks, suitable for e-commerce and banking systems. 2.MyISAM provides fast read and indexing, suitable for blogging and content management systems.

What are the different types of JOINs in MySQL?What are the different types of JOINs in MySQL?Apr 27, 2025 am 12:13 AM

There are four main JOIN types in MySQL: INNERJOIN, LEFTJOIN, RIGHTJOIN and FULLOUTERJOIN. 1.INNERJOIN returns all rows in the two tables that meet the JOIN conditions. 2.LEFTJOIN returns all rows in the left table, even if there are no matching rows in the right table. 3. RIGHTJOIN is contrary to LEFTJOIN and returns all rows in the right table. 4.FULLOUTERJOIN returns all rows in the two tables that meet or do not meet JOIN conditions.

What are the different storage engines available in MySQL?What are the different storage engines available in MySQL?Apr 26, 2025 am 12:27 AM

MySQLoffersvariousstorageengines,eachsuitedfordifferentusecases:1)InnoDBisidealforapplicationsneedingACIDcomplianceandhighconcurrency,supportingtransactionsandforeignkeys.2)MyISAMisbestforread-heavyworkloads,lackingtransactionsupport.3)Memoryengineis

What are some common security vulnerabilities in MySQL?What are some common security vulnerabilities in MySQL?Apr 26, 2025 am 12:27 AM

Common security vulnerabilities in MySQL include SQL injection, weak passwords, improper permission configuration, and unupdated software. 1. SQL injection can be prevented by using preprocessing statements. 2. Weak passwords can be avoided by forcibly using strong password strategies. 3. Improper permission configuration can be resolved through regular review and adjustment of user permissions. 4. Unupdated software can be patched by regularly checking and updating the MySQL version.

How can you identify slow queries in MySQL?How can you identify slow queries in MySQL?Apr 26, 2025 am 12:15 AM

Identifying slow queries in MySQL can be achieved by enabling slow query logs and setting thresholds. 1. Enable slow query logs and set thresholds. 2. View and analyze slow query log files, and use tools such as mysqldumpslow or pt-query-digest for in-depth analysis. 3. Optimizing slow queries can be achieved through index optimization, query rewriting and avoiding the use of SELECT*.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool