Oracle Stream Synchronization Data

1 Introduction

Oracle Official Network:

Http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10727/strmover.htm

The Oracle Stream feature is designed to improve the high availability of the database, which is known as the Advance Replication in Oracle 9i and earlier versions. Oracle Stream leverages advanced queuing technology to enable synchronization between databases by parsing archive logs and parsing archived logs into DDL and DML statements. This technology can copy the whole database, the object in the database to another database, through the use of stream technology, the mining of archived logs, can be no pressure on the primary system, to achieve the database object level or even the entire database synchronization.
Parsing archive Logs This technology is now widely used, quest company's Shareplex software and DSG company RealSync are such products, some companies use such products to do application-level disaster recovery. But Shareplex or realsync are very expensive, so you can try using stream, a feature that Oracle offers without extra money. The impact of Oracle stream on the production library is very small, from the library can be different from the main library operating system platform, you can use Oracle stream to copy a few 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 hand-in-step way to take you to the stream of the environment to build up, the details of the content can be checked online documentation.
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 modify Init.ora file, use SPFile to modify the Spile file by using the ALTER system command. The main, from the database executes the following statements, respectively:

Restart the database after execution is complete.
3.2 Placing the database in archive mode
Set the log_archive_dest_1 to the appropriate location, set Log_archive_start to True, that is, enable the automatic archiving function, set the Log_archive_format to specify the format of the Archive log command.
Example:

After the database is placed in archive mode, you can test it as follows:

The part of the note-Mark Red.
3.3 Creating a Stream administrative user
3.3.1 Creating a Master Environment Stream Admin user
#以sysdba身份登录
Connect/as SYSDBA
#创建主环境的Stream专用表空间

#将logminer的数据字典从system表空间转移到新建的表空间 to prevent full system table space

#创建Stream管理用户

#授权Stream管理用户

3.3.2 Create an Admin user from the environment stream
#以sysdba身份登录
Connect/as SYSDBA
#创建Stream专用表空间, I used ASM from the library, and this step can also be found in 3.3.1

#同样, transfer the Logminer data dictionary from the system table space to the newly created tablespace to prevent full system table space

#创建Stream管理用户

#授权Stream管理用户

3.4 Configuring Network Connections
3.4.1 Configuring the main environment Tnsnames.ora
Add the configuration from the database in the primary database (Tnsnames.ora).

3.4.2 configuration from the environment Tnsnames.ora
Add the configuration of the primary database from the database (Tnsnames.ora).

3.5 Enable append Log
Append logs (supplemental log) can be enabled based on the database level or table level. In an Oracle stream environment where replication is based on schema granularity, it is no longer necessary to enable append logging if all table under the schema is confirmed to have a reasonable primary key (Primary key).
#启用Database Append Log

#启用Table追加日志

3.6 Creating Dblink
According to the official Oracle 10GR2 stream document, the database chain established for the primary database must have the same name as the Global_name from the database.
If you need to modify Global_name, execute "ALTER DATABASE rename global_name to xxx".
3.6.1 Creating a database chain for the primary database
#以strmadmin身份, log in to the master database.
Connect Strmadmin/strmadmin
#建立数据库链
Create DATABASE link h10g connect to strmadmin identified by Strmadmin using ' h10g ';
3.6.2 creating a chain from the database database
#以strmadmin身份, log on 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 Creating a master stream queue
#以strmadmin身份, log in to the master database.

3.7.2 Creating a backup stream queue
#以strmadmin身份, log on from the database.

3.8 Creating the capture process
#以strmadmin身份, log in to the master database. As a reminder, this document is an example of an HR user.

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 empty user for the HR.

3.10 Creating the propagation process
#以strmadmin身份, log in to the master database.

#修改propagation休眠时间为0, which means that LCR is propagated in real time.

3.11 Creating an App process
#以strmadmin身份, log on from the database.

3.12 Start Stream
#以strmadmin身份, log on from the database.
Connect Strmadmin/strmadmin
#启动Apply进程
Begin
Dbms_apply_adm.start_apply (
Apply_name = ' apply_h10g ');
End
/
#以strmadmin身份, log in to the master database.
Connect Strmadmin/strmadmin
#启动Capture进程
Begin
Dbms_capture_adm.start_capture (
Capture_name = ' Capture_prod ');
End
/
3.13 Stop Stream
#以strmadmin身份, log in to the master database.
Connect Strmadmin/strmadmin
#停止Capture进程
Begin
Dbms_capture_adm.stop_capture (
Capture_name = ' Capture_prod ');
End
/
#以strmadmin身份, log on 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 do 3.13 to stop the stream process.
#以strmadmin身份, log in to the master database.
Connect Strmadmin/strmadmin
exec dbms_streams_adm.remove_streams_configuration ();
#以strmadmin身份, log on 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)
4.2 Inserting a row of data in a 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, 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 to build 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 for 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 Deleting an 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 Deleting a table test
Primary database
sql> DROP TABLE TTT;
Table dropped
From the database
Sql> DESC TTT;
Object TTT does not exist.
4.10 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
4.11 Inserting a row of data in a 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
------------------------------ -------------------------------- -

Oracle Stream Synchronization data