Oracle stream--oracle Two database synchronization

1 Introduction Oracle Official network: http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10727/strmover.htm
Oracle Stream functionality is designed to improve the high availability of the database, which is known as Advance Replication in Oracle 9i and prior versions. Oracle Stream leverages advanced queuing technology to resolve archived logs and parse them into DDL and DML statements to achieve synchronization between databases. This technology can copy the entire database, the object in the database to another database, through the use of stream technology, the archive log mining, can be without any pressure on the main system, the database object level or even the entire database synchronization.

Parsing archived logs This technology is now widely used, quest company's Shareplex software and DSG Company's realsync are such products, some companies use such products to do application-level disaster tolerance. But Shareplex or realsync are very expensive, so you can try using the stream this oracle provides without the extra cost of the function. Oracle Stream's impact on the production library is very small, from the library can be different from the main operating system platform, you can use the Oracle stream to replicate several from the library, from the library can be used for queries, reports, disaster tolerance and other different functions. This article does not talk about technical details, just step-by-step way to take you to build the environment of the stream, details can be found online documents.

2 Overview
Primary database:

Operating system: Solaris 9
IP Address: 192.168.10.35
Database: Oracle 10.2.0.2
Oracle_sid:prod
Global_name:prod

From the database:
Operating system: AIX 5.2
IP Address: 192.168.10.43
Database: Oracle 10.2.0.3
oracle_sid:h10g
global_name:h10g
3 Environment Preparation
3.1 Setting initialization parameters
Using the Pfile modified Init<sid>.ora file, use the SPFile to modify the Spile file by using the ALTER system command. The following statements are executed by the primary and the database, respectively:
Sqlplus '/As Sysdba '
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=25M Scope=spfile;
Alter system set utl_file_dir= ' * ' scope=spfile;
Alter system set OPEN_LINKS=4 Scope=spfile;

Restart the database after the execution has completed.
3.2 Putting the database into archive mode
Sets the log_archive_dest_1 to the appropriate location, sets the Log_archive_start to True, enables the automatic archiving feature, and sets the command format Log_archive_format specifies the archive log.
Example:
Sqlplus '/As Sysdba '
Alter system set log_archive_dest_1= ' Location=/yang/arch ' scope=spfile;
Alter system set LOG_ARCHIVE_START=TRUE Scope=spfile;
Alter system set log_archive_format= ' Arch%t_%s_%r.arc ' scope=spfile;
Shutdown immediate;
startup Mount;
ALTER DATABASE Archivelog;
ALTER DATABASE open;

After the database is placed in archive mode, you can test it as follows:
Sql> Archive Log List
Database Log Mode Archive mode
Automatic Archival Enabled
Archive Destination/yang/arch
Oldest online log sequence 534
Next log sequence to archive 536
Current log Sequence 536

Note the marked red part.

3.3 Create stream Admin user
3.3.1 Create the main environment stream manage users
#以sysdba身份登录
Connect/as SYSDBA

#创建主环境的Stream专用表空间
Create tablespace tbs_stream datafile '/yang/oradata/prod/tbs_stream01.dbf '
Size 100m autoextend on MaxSize unlimited segment spaces Management auto;

#将logminer的数据字典从system表空间转移到新建的表空间 to prevent the system table space from being filled
Execute dbms_logmnr_d.set_tablespace (' Tbs_stream ');

#创建Stream管理用户
Create user strmadmin identified by strmadmin
Default tablespace tbs_stream temporary tablespace temp;

#授权Stream管理用户
Grant Connect,resource,dba,aq_administrator_role to Strmadmin;

Begin
Dbms_streams_auth.grant_admin_privilege (
Grantee => ' Strmadmin ',
Grant_privileges => true);
End
/
3.3.2 Create an Admin user from the environment stream
#以sysdba身份登录
Connect/as SYSDBA

#创建Stream专用表空间, I used ASM from the library, this step can also see 3.3.1
Create tablespace tbs_stream datafile ' +vgdata/h10g/datafile/tbs_stream01.dbf '
Size 100m autoextend on MaxSize unlimited segment spaces Management auto;
#同样, transfer the Logminer data dictionary from the system tablespace to the newly created table space to prevent the system tablespace from being filled
Execute dbms_logmnr_d.set_tablespace (' Tbs_stream ');

#创建Stream管理用户
Create user strmadmin identified by strmadmin
Default tablespace tbs_stream temporary tablespace temp;

#授权Stream管理用户
Grant Connect,resource,dba,aq_administrator_role to Strmadmin;

Begin
Dbms_streams_auth.grant_admin_privilege (
Grantee => ' Strmadmin ',
Grant_privileges => true);
End
/

3.4 Configuring Network Connections
3.4.1 Configure the main environment Tnsnames.ora
The primary database (Tnsnames.ora) is added to the configuration from the database.
h10g =
(DESCRIPTION =
(Address_list =
(address = (PROTOCOL = TCP) (HOST = 192.168.10.43) (PORT = 1521))
)
(Connect_data =
(SID = h10g)
(SERVER = dedicated)
)
)
3.4.2 configuration from the environment Tnsnames.ora
Add the configuration of the primary database from the database (Tnsnames.ora).
PROD =
(DESCRIPTION =
(Address_list =
(address = (PROTOCOL = TCP) (HOST = 192.168.10.35) (PORT = 1521))
)
(Connect_data =
(SID = prod)
(SERVER = dedicated)
)
)
3.5 Enable append Log
You can enable the Append log (supplemental log) based on the database level or table level. In an Oracle stream environment that is replicated based on schema granularity, you no longer need to enable the Append log if all the table under the schema has a reasonable primary key (Primary key).

#启用Database Append Log
ALTER DATABASE add supplemental log data;

#启用Table追加日志
ALTER TABLE Add Supplement log Group Log_group_name (table_column_name) always;

3.6 Creating Dblink
According to the official Oracle 10GR2 stream document, the name of the database chain established against the primary database must be the same as the global_name from the database.
If you need to modify Global_name, perform "ALTER DATABASE rename global_name to xxx".
3.6.1 Create a chain of primary database databases
#以strmadmin身份, log on to the primary database.
Connect Strmadmin/strmadmin
#建立数据库链
Create DATABASE link h10g connect to strmadmin identified by Strmadmin using ' h10g ';
3.6.2 Create a chain from a database database
#以strmadmin身份, log in from the database.
Connect Strmadmin/strmadmin
#建立数据库链
Create DATABASE link prod connect to strmadmin identified by Strmadmin using ' prod ';
3.7 Creating a stream queue
3.7.1 Create master stream queues
#以strmadmin身份, log on to the primary database.
Connect Strmadmin/strmadmin

Begin
Dbms_streams_adm.set_up_queue (
Queue_table => ' prod_queue_table ',
Queue_name => ' Prod_queue ');
End
/
3.7.2 Create a backup stream queue
#以strmadmin身份, log in from the database.
Connect Strmadmin/strmadmin

Begin
Dbms_streams_adm.set_up_queue (
Queue_table => ' h10g_queue_table ',
Queue_name => ' H10g_queue ');
End
/

3.8 Creating a capture process
#以strmadmin身份, log on to the primary database. To be reminded, this document is an example of an HR user.
Connect Strmadmin/strmadmin

Begin
Dbms_streams_adm.add_schema_rules (
Schema_name => ' hr ',
Streams_type => ' Capture ',
Streams_name => ' Capture_prod ',
Queue_name => ' Strmadmin.prod_queue ',
Include_dml => True,
Include_ddl => True,
INCLUDE_TAGGED_LCR => False,
Source_database => NULL,
Inclusion_rule => true);
End
/
3.9 Instantiating a replicated database
In the primary database environment, execute the following shell statement. If the HR user from the library does not exist, establish an HR empty user.
Exp Userid=hr/hr@prod file= '/tmp/hr.dmp ' object_consistent=y rows=y

Imp userid=system/manager@h10g file= '/tmp/hr.dmp ' ignore=y commit=y log= '/tmp/hr.log ' streams_instantiation=y FROMUSER=HR TOUSER=HR
3.10 Creating the propagation process
#以strmadmin身份, log on to the primary database.
Connect Strmadmin/strmadmin

Begin
Dbms_streams_adm.add_schema_propagation_rules (
Schema_name => ' hr ',
Streams_name => ' prod_to_h10g ',
Source_queue_name => ' Strmadmin.prod_queue ',
Destination_queue_name => ' strmadmin.h10g_queue@h10g ',
Include_dml => True,
Include_ddl => True,
INCLUDE_TAGGED_LCR => False,
Source_database => ' prod ',
Inclusion_rule => true);
End
/

#修改propagation休眠时间为0, which means that LCR is propagated in real time.
Begin
Dbms_aqadm.alter_propagation_schedule (
Queue_name => ' Prod_queue ',
Destination => ' h10g ',
Latency => 0);
End
/
3.11 Creating the application process
#以strmadmin身份, log in from the database.
Connect Strmadmin/strmadmin

Begin
Dbms_streams_adm.add_schema_rules (
Schema_name => ' hr ',
Streams_type => ' Apply ',
Streams_name => ' apply_h10g ',
Queue_name => ' Strmadmin.h10g_queue ',
Include_dml => True,
Include_ddl => True,
INCLUDE_TAGGED_LCR => False,
Source_database => ' prod ',
Inclusion_rule => true);
End
/
3.12 Start Stream
#以strmadmin身份, log in from the database.
Connect Strmadmin/strmadmin

#启动Apply进程
Begin
Dbms_apply_adm.start_apply (
Apply_name => ' apply_h10g ');
End
/

#以strmadmin身份, log on to the primary database.
Connect Strmadmin/strmadmin

#启动Capture进程
Begin
Dbms_capture_adm.start_capture (
Capture_name => ' Capture_prod ');
End
/
3.13 Stop Stream
#以strmadmin身份, log on to the primary database.
Connect Strmadmin/strmadmin

#停止Capture进程
Begin
Dbms_capture_adm.stop_capture (
Capture_name => ' Capture_prod ');
End
/

#以strmadmin身份, log in from the database.
Connect Strmadmin/strmadmin

#停止Apply进程
Begin
Dbms_apply_adm.stop_apply (
Apply_name => ' apply_h10g ');
End
/
3.14 Clear All configuration information
To understand the stream configuration information, you need to perform 3.13 to stop the stream process.

#以strmadmin身份, log on to the primary database.
Connect Strmadmin/strmadmin
exec dbms_streams_adm.remove_streams_configuration ();

#以strmadmin身份, log in from the database.
Connect Strmadmin/strmadmin
exec dbms_streams_adm.remove_streams_configuration ();
4 test scenarios
This document establishes a stream replication environment for HR users and, if not specifically stated, the following test scenarios are performed as HR users.
4.1 Build a table test
Primary database
Sql> CREATE TABLE TTT (ID number PRIMARY KEY,
2 name VARCHAR2 (50)
3)
4/

Table created.

From the database
Sql> desc TTT
Name Null? Type
---------- -------- -------------
ID not NULL number
NAME VARCHAR2 (50)
Insert a row of data into a 4.2 table
Primary database
sql> INSERT into TTT values (1, ' sdfsdfsdfsdf ');
1 row created.
Sql> commit;
Commit complete.

Sql>
From the database
Sql> select * from TTT;
ID NAME
---------- --------------------
1 SDFSDFSDFSDF
4.3 Change the structure of the table and add a column
Primary database
sql> ALTER TABLE TTT ADD (age number (2));
Table Altered

From the database
Sql> desc TTT
Name Null? Type
----------- -------- --------------
ID not NULL number
NAME VARCHAR2 (50)
Age Number (2)
4.4 Changing a table to a table space
Primary database
Sql> SELECT Table_name,tablespace_name from User_tables
2 WHERE table_name= ' TTT ';

TABLE_NAME Tablespace_name
------------------------------ ------------------------------
TTT USERS

Sql> ALTER TABLE TTT move tablespace tbs_stream;
Table Altered

Sql> SELECT Table_name,tablespace_name from User_tables
WHERE table_name= ' TTT ';
TABLE_NAME Tablespace_name
------------------------------ ------------------------------
TTT Tbs_stream

From the database
Sql> SELECT Table_name,tablespace_name from User_tables
WHERE table_name= ' TTT ';
TABLE_NAME Tablespace_name
------------------------------ ------------------------------
TTT Tbs_stream
4.5 The Name column on the table builds an index
Primary database
Sql> CREATE INDEX ttt_name_idx on TTT (name);
Index created

From the database
Sql> SELECT table_name, index_name from user_indexes WHERE table_name = ' TTT ';
TABLE_NAME Index_name
------------------------------ 　　　　------------------------------
TTT Ttt_name_idx
TTT sys_c005721

4.6 Rebuild Index Test
Primary database
sql> ALTER INDEX ttt_name_idx REBUILD;
Index Altered

From the database
Sql> SELECT table_name, index_name from user_indexes WHERE table_name = ' TTT ';
TABLE_NAME Index_name
------------------------------ 　　　　------------------------------
TTT Ttt_name_idx
TTT sys_c005721

4.7 Index to change a table space test
Primary database
sql> ALTER INDEX ttt_name_idx REBUILD tablespace tbs_stream;
Index Altered

From the database
Sql> SELECT Table_name,index_name,tablespace_name from user_indexes
WHERE table_name = ' TTT ';

TABLE_NAME Index_name Tablespace_name
------------------------------ ------------------------------ ------------------------------
TTT Ttt_name_idx Tbs_stream
TTT sys_c005721 USERS

4.8 Delete Index Test
Primary database
sql> DROP INDEX Ttt_name_idx;
Index dropped

From the database
Sql> SELECT Table_name,index_name,tablespace_name from user_indexes
WHERE table_name = ' TTT ';

TABLE_NAME Index_name Tablespace_name
------------------------------ ------------------------------ ------------------------------
TTT sys_c005721 USERS
4.9 Delete Table test
Primary database
sql> DROP TABLE TTT;
Table dropped

From the database
Sql> DESC TTT;
Object TTT does not exist.

4.10 To build a table test with LOB type fields
Primary database
Sql> CREATE TABLE Tttclob (ID number PRIMARY KEY, Memo CLOB);
Table created

From the database
Sql> DESC Tttclob;
Name Null? Type
----------- -------- --------------
ID not NULL number
MEMO CLOB

Insert a row of data into a 4.11 table
Primary database
Sql> INSERT into Tttclob VALUES (1, ' clob_test ');
1 row inserted
Sql> commit;
Commit Complete

From the database
Sql> SELECT * from Tttclob;

ID MEMO
---------- --------------------------------------------------------------------------------
1 clob_test

4.12 Creating a Type test
Primary database
Sql> CREATE or REPLACE TYPE ttttype;
2/
Type created

From the database
Sql> SELECT * from user_types WHERE type_name= ' Ttttype ';
Type_name type_oid typecode ATTRIBUTES METHODS predefined incomplete FINAL instantiable Supertype_owner supertype_name LO Cal_attributes Local_methods typeID
------------------------------ -------------------------------- ------------------------------ ---------- --------- - ---------- ---------- ----- ------------ ------------------------------ ------------------------------ ------------- --- ------------- --------------------------------
Ttttype 1b36aaf10da8301de040a8c0289a77b4 OBJECT 0 0 NO Yes Yes

4.13 Delete Type Test
Primary database
sql> DROP TYPE Ttttype;
Type dropped

From the database
Sql> SELECT * from user_types WHERE type_name= ' Ttttype ';
Type_name type_oid typecode ATTRIBUTES METHODS predefined incomplete FINAL instantiable Supertype_owner supertype_name LO Cal_attributes Local_methods typeID
------------------------------ -------------------------------- -