Oracle simple stream one-way replication configuration for users

If the STATUS is ENABLED, the Apply process runs normally. If the STATUS is DISABLED, the Apply process is stopped and only needs to be restarted.

If the STATUS is ENABLED, the Apply process runs normally. If the STATUS is DISABLED, the Apply process is stopped and only needs to be restarted.

The following procedure is used to synchronize streamm data between two machines. It's easy to see that stream has a lot to watch.

1. Environment Introduction

Primary database SID: obpm

Operating System: win 2003

IP Address: 192.168.0.1

Global_name:

Oracle version: 10.2.0.1

Slave database SID: orcl

Operating System: win 2003

IP Address: 192.168.0.2

Global_name:

Oracle version: 10.2.0.1

2. Set initialization parameters in Master/Slave databases (perform the following operations in Master/Slave databases)

1) sqlplus/as sysdba ---- Log On as A dba

2) alter system set aq_tm_processes = 2 scope = both; --- enable time monitoring for queue messages

Alter system set global_names = true scope = both; --- set global name to true

--- Alter system set job_queue_processes = 10 scope = both; -- set the maximum number of scheduled job processes to 10. The default value is 10.

Alter system set undo_retention = 3600 scope = both; -- sets the rollback period. The default value is 900.

Alter system set streams_pool_size = 25 M scope = spfile; -- this parameter is not required when sga is set to automatic adjustment.

3) Restart two databases

3. Set the primary database to archive mode.

1) archive log list; -- check whether the file is archived. If the file is archived, ignore the 3rd point.

2) shutdown immediate;

Startup mount;

Alter database archivelog;

Alter system set LOG_ARCHIVE_DEST_1 = 'location = D: \ arc \ '; --- set the archive directory

Alter system switch logfile; -- check whether the arc file is generated in the corresponding directory

Alter database open;

4. Create a stream management user for Master/Slave Data (perform the following operations on both master and slave databases)

1) sqlplus/as sysdba;

2) create tablespace tbs_stream datafile 'e: \ tbs_stream01.dbf' size 100 m autoextend on maxsize unlimited segment space management auto; --- create a dedicated Stream tablespace for the master Environment

3) execute dbms_logmnr_d.set_tablespace ('tbs _ stream'); -- transfers the data dictionary of logminer from the system tablespace to the new tablespace to prevent the system tablespace from being fully occupied

4) create user strmadmin identified by strmadmin default tablespace tbs_stream temporary tablespace temp; -- create a user

5) grant dba to strmadmin; --- grant dba permissions directly.

5. master database network environment settings

1) Add the slave database configuration in the master database (tnsnames. ora. (You can also configure it using the oracle Graphics Tool net manager)

Orcl =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

2) test connectivity (run the following command in cmd)

Sqlplus strmadmin/strmadmin @ orcl

3) create an orcl dblink

Sqlplus strmadmin/strmadmin

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

---- When global_names is true, the name of the database chain created by the master database must be the same as that of the slave database global_name.

Select sysdate from dual @ orcl; -- test whether dblink is valid

6. Set the network environment of the slave Database

1) Add the configuration of the primary database from the database (tnsnames. ora. (You can also configure it using the oracle Graphics Tool net manager)

Obpm =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = obpm)

)

)

2) test connectivity (run the following command in cmd)

Sqlplus strmadmin/strmadmin @ obpm

3) create an obpm dblink

Sqlplus strmadmin/strmadmin

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

---- When global_names is true, the name of the database chain created by the master database must be the same as that of the slave database global_name.

Select sysdate from dual @ obpm; -- test whether dblink is valid