Introduction to Oracle Streams
Oracle Streams is a component provided in Oracle RDBMS that enables
information sharing through replication. This information is shared in form of messages propagated from one database to another database.
Oracle Streams provides the capabilities needed to build and operate
distributed enterprises and applications, data warehouses, and high
availability solutions. All of the capabilities of Oracle Streams can be used at
the same time. If the needs change, then one can implement a new capability of
Oracle Streams without sacrificing existing capabilities.
Although Oracle Streams has expensive mechanism between capturing and
applying changes from source to destination database. I have, broadly, covered
them in five steps as depicted below:
1.
Oracle Streams captures messages at source database either by change capture (used for large number of tables, at schema level or
whole database) mechanism or synchronous
capture (used when few number of tables are to be replicated).
2.
Messages are staged in a queue. A queue is basically a table keeping
information messages to be propagated.
3.
Oracle Streams uses Oracle Net to propagate
messages from source queue
and enqueue them at destination database.
4.
Oracle streams dequeue messages at destination database.
5.
Apply process will apply the changes to destination database.
The Oracle Streams Replication Configuration Procedures
The
easiest way to configure an Oracle Streams replication environment is by
running one of the following configuration procedures in the DBMS_STREAMS_ADM
package:
- MAINTAIN_GLOBAL configures an Oracle Streams environment that replicates changes at the database level between two databases.
- MAINTAIN_SCHEMAS configures an Oracle Streams environment that replicates changes to specified schemas between two databases.
- MAINTAIN_SIMPLE_TTS clones a simple tablespace from a source database at a destination database and uses Oracle Streams to maintain this tablespace at both databases.
- MAINTAIN_TABLES configures an Oracle Streams environment that replicates changes to specified tables between two databases.
- MAINTAIN_TTS clones a set of tablespaces from a source database at a destination database and uses Oracle Streams to maintain these tablespaces at both databases.
- PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP configure an Oracle Streams environment that replicates changes either at the database level or to specified tablespaces between two databases. These procedures must be used together, and instantiation actions must be performed manually, to complete the Oracle Streams replication configuration.
I will be using MAINTAIN_GLOBAL
procedure in this post with Local Capture
at source database. When source database is configured to capture changes, it
is called local capture. Whereas when destination database is configured to
capture changes from source database, it is called downstream capture.
Oracle streams components that will be configured by
MAINTAIN_GLOBAL procedure:
- This will configure a capture process that captures changes on source database
- This will configure a propagation that will send changes from captured database to destination database
- This will configure an apply process at destination database that will apply changes on destination database
Change Cycling
Since we are not setting up
bi-directional replication we are not concerned about change cycling, but it
would be good to know about it.
Change cycling happens when a
change is sent back to the database where it originated. Typically, change
cycling should be avoided because it can result in each change going through
endless loops back to the database where it originated. Such loops can result
in unintended data in the database.
Instantiation
Instantiation is the process of
preparing database objects for instantiation at a source database, during this,
system change number (called instantiation SCN) for a table is set which
specifies that only changes that were committed after the SCN at the source
database are applied by an apply process.
Instantiation also optionally provides
facility to copy the database objects from a source database to a destination
database along with setting the instantiation SCN for each instantiated
database object.
The MAINTAIN_GLOBAL, MAINTAIN_SCHEMAS
and MAINTAIN_TABLES procedures
provide options for instantiation. Instantiation is the process of preparing
database objects for instantiation at a source database, optionally copying the
database objects from a source database to a destination database, and setting
the instantiation SCN for each instantiated database object.
With MAINTAIN_GLOBAL
procedure, DBMS_STREAMS_ADM.INSTANTIATION_FULL_NETWORK and
DBMS_STREAMS_ADM.INSTANTIATION_FULL can
be used. The difference between these two is that INSTANTIATION_FULL
will create a dumpfile on source system and copy it to destination system for
datapump import. Whereas in INSTANTIATION_FULL_NETWORK,
a network datapump import is directly performed without using dumpfile.
Environment Information
|
Attribute
|
Source
|
Destination
|
|
Database Name
|
PINDBA
|
PINDBA
|
|
Database Global Name
|
PINDBA.EXAMPE.COM
|
PINRPL.EXAMPLE.COM
|
|
Database Unique Name
|
PINDBA
|
PINRPL
|
Pre-requisite Tasks
ü
Ensure Oracle
Net in configured between both databases
ü
Ensure
source database is in ARCHIVELOG mode
ü
Ensure to
set below parameters
compatible : Should be highest possible
global_names : Must be true
open_links : 4 or higher
processes : 100 or higher
sessions : (1.5 x processes) + 22
ü
Enable
supplemental logging at database level for key columns
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG
DATA
(PRIMARY
KEY, UNIQUE, FOREIGN KEY) COLUMNS;
ü
Check for
unsupported objects in database
SQL> SELECT * FROM DBA_STREAMS_UNSUPPORTED;
ü
Excluded schemas
are SYS, SYSTEM and CTXSYS
Setting-up Environment for Replication
Setup Admin User and Tablespace
1. Setup tablespace for Stream Administrator
User on source database
-- On Source Database
SQL>
CREATE TABLESPACE STREAM_TBS
DATAFILE
'/pindbadata/pindba/datafiles/stream_tbs01.dbf'
SIZE 25M
AUTOEXTEND ON;
2. Setup Stream Administrator User on both
source database
SQL> CREATE USER STREAMADM IDENTIFIED BY
STREAMADM;
SQL> GRANT CONNECT, RESOURCE, DBA,
SELECT_CATALOG_ROLE TO STREAMADM;
SQL> BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
GRANTEE => 'STREAMADM',
GRANT_PRIVILEGES => TRUE
);
END;
/
Run Pre-instantiation Setup
1. Connect to source database as Stream Administrator
user and run pre-instantiation
SQL> connect streamadm/streamadm@PINDBA
SQL> DECLARE
stream_tbs
DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
BEGIN
DBMS_STREAMS_ADM.PRE_INSTANTIATION_SETUP(
maintain_mode =>
'GLOBAL',
tablespace_names => stream_tbs,
source_database =>
'PINDBA.EXAMPLE.COM',
destination_database => 'PINRPL.EXAMPLE.COM',
perform_actions => TRUE,
bi_directional => FALSE,
include_ddl => TRUE,
start_processes => TRUE,
exclude_schemas => '*',
exclude_flags =>
DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED +
DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML +
DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);
END;
/
Most of the
parameters are self-explanatory. The values specified for the exclude_schemas
and exclude_flags
parameters. The asterisk (*) specified for exclude_schemas
indicates that certain database objects in every schema in the database might
be excluded from the replication environment. The value specified for the exclude_flags
parameter indicates that DML and DDL changes for all unsupported database
objects are excluded from the replication environment. Rules are placed in the
negative rule sets for the capture processes to exclude these database objects.
2. Perform Instantiation
2.1 Take backup of source database
2.2 Determine SCN number, note down the output
of below command
SQL>
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
until_scn NUMBER;
BEGIN
until_scn:=
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
DBMS_OUTPUT.PUT_LINE('Until SCN: ' ||
until_scn);
END;
/
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
2.3 Prepare environment for database
duplication
ü
Setup Oracle Home for destination database
ü
Setup parameter file for destination database
ü
Verify Oracle Net between source and destination
database
2.4 Run RMAN Duplicate/Restore, modify below
script as needed
RMAN>
RUN
{
SET UNTIL SCN <scn_obtained_in_2.2>;
DUPLICATE TARGET DATABASE TO PINRPL
NOFILENAMECHECK;
OPEN RESTRICTED;
}
2.5 Rename Global Name of Database
SQL>
ALTER DATABASE RENAME GLOBAL NAME TO PINRPL.EXAMPLE.COM
3. Create database links
-- On Source Database
SQL>
ALTER USER STREAMADM DEFAULT TABLESPACE STREAM_TBS
QUOTA UNLIMITED ON STREAM_TBS;
SQL>
CONNECT STREAMADM/STREAMADM
SQL>
CREATE DATABASE LINK PINRPL.EXAMPLE.COM CONNECT TO STREAMADM
IDENTIFIED BY STREAMADM USING 'PINRPL';
-- On Destination Database
SQL>
ALTER USER STREAMADM DEFAULT TABLESPACE STREAM_TBS
QUOTA UNLIMITED ON STREAM_TBS;
SQL>
CONNECT STREAMADM/STREAMADM
SQL>
CREATE DATABASE LINK PINDBA.EXAMPLE.COM CONNECT TO STREAMADM
IDENTIFIED BY STREAMADM USING 'PINDBA';
4. Create directory objects
-- On Source Database
$
mkdir /pindbadata/dbastream
SQL>
CREATE DIRECTORY STREAM_DBA AS '/pindbadata/dbastream';
-- On Destination Database
$
mkdir /pinrpldata/rplstream
SQL>
CREATE DIRECTORY STREAM_RPL AS '/pinrpldata/rplstream';
5. Connect to Source database as Stream
Administrator user and run post-instantiation
SQL>
connect streamadm/streamadm@PINDBA
SQL>
DECLARE
stream_tbs
DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
BEGIN
DBMS_STREAMS_ADM.POST_INSTANTIATION_SETUP(
maintain_mode => 'GLOBAL',
tablespace_names => stream_tbs,
source_database => 'PINDBA.EXAMPLE.COM',
destination_database =>
'PINRPL.EXAMPLE.COM',
perform_actions => TRUE,
bi_directional => FALSE,
include_ddl => TRUE,
start_processes => TRUE,
instantiation_scn => <scn_obtained_in_2.2 - 1>,
exclude_schemas => '*',
exclude_flags =>
DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED +
DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML
+
DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);
END;
/
The parameter values specified in both the PRE_INSTANTIATION_SETUP
and POST_INSTANTIATION_SETUP
procedures must match, except for the values of the following parameters: perform_actions,
script_name, script_directory_object, if Specified.
Also, instantiation_scn must be specified one less
than what value was obtained in step 2.2. This is because the RMAN DUPLICATE
command duplicates the database up to one less than the SCN value specified in
the UNTIL
SCN
clause.
6. Disable Restricted Session
SQL>
ALTER SYSTEM DISABLE RESTRICTED SESSION;
References
1.
Streams
Replication Administrator’s Guide 11gR2
[http://docs.oracle.com/cd/E11882_01/server.112/e10705/toc.htm]
No comments:
Post a Comment