ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle10g R2 streams setup by setup

oracle10g R2 streams setup by setup

原创 Linux操作系统 作者:zwc1083 时间:2009-05-04 17:13:46 0 删除 编辑

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4

Information in this document applies to any platform.

Goal

This article shows an example on how to perform. some maintenance operations with Oracle Streams on an Oracle Database 10g Release 2 database.

These maintenance operations may include migrating an Oracle Database to a new Server, upgrading user-created applications, and applying Oracle Database patches.

Instantiating the Database Using the RMAN DUPLICATE Command is supported when the platform. at the source and destination databases are the same as well as the character set.

More details can be found in:

Oracle Streams Concepts and Administration 10g Release 2 (10.2)

Apendix C Online Database Maintenance with Streams

Solution

Source Database --> ORCL.US.ORACLE.COM , archive log mode

For both Source and Target databases, check out:

Note 418755.1 10.2.0.x.x Streams Recommendations

So that Software Versions, Database Parameters are setup accordingly.

Beginning the Maintenance Operation

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

1) Created an empty Oracle Database 10g Release 2 database (CHASE.US.ORACLE.COM). Set archive log mode on as bidirectional streams will be setup

2) ORCL runs in archive log mode

3) Configured Streams administrator at each database ORCL and CHASE

CREATE TABLESPACE streams_tbs DATAFILE '/bugmnt11/am/celclnx1/database//streams_tbs.dbf'

SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

CREATE USER strmadmin IDENTIFIED BY strmadmin

DEFAULT TABLESPACE streams_tbs

QUOTA UNLIMITED ON streams_tbs;

GRANT DBA TO strmadmin;

BEGIN

DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(

grantee => 'strmadmin',

grant_privileges => true);

END;

/

Setting Up Streams Prior to Instantiation

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The Source Database Is the Capture Database

Connected as the Streams administrator in SQL*Plus to the source database, created a database link to the destination database.

CONNECT strmadmin/strmadmin

SQL> create database link CHASE.US.ORACLE.COM

connect to strmadmin identified by strmadmin

using 'CHASE.US.ORACLE.COM';

Connected as the Streams administrator in SQL*Plus to the destination database, created a database link to the source database.

CONNECT strmadmin/strmadmin

SQL> create database link ORCL.US.ORACLE.COM

connect to strmadmin identified by strmadmin

using 'ORCL.US.ORACLE.COM';

While connected as the Streams administrator in SQL*Plus to the source database, ran the PRE_INSTANTIATION_SETUP procedure:

DECLARE

empty_tbs DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;

BEGIN

DBMS_STREAMS_ADM.PRE_INSTANTIATION_SETUP(

maintain_mode => 'GLOBAL',

tablespace_names => empty_tbs,

source_database => 'ORCL.US.ORACLE.COM',

destination_database => 'CHASE.US.ORACLE.COM',

perform_actions => true,

script_name => NULL,

script_directory_object => NULL,

capture_name => 'capture_maint',

capture_queue_table => 'strmadmin.capture_q_table',

capture_queue_name => 'strmadmin.capture_q',

propagation_name => 'prop_maint',

apply_name => 'apply_maint',

apply_queue_table => 'strmadmin.apply_q',

apply_queue_name => 'strmadmin.apply_q_table',

bi_directional => true,

include_ddl => true,

start_processes => false,

exclude_schemas => 'DIP,OUTLN,STRMADMIN,DBSNMP,TSMSYS',

exclude_flags => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED +

DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML + DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);

END;

/

Instantiating the Database Using RMAN

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

1. Created a backup of the source database.

2. While connected as an administrative user in SQL*Plus to the source database, determine the until SCN for the RMAN DUPLICATE 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> 2 3 4 5 6 7

Until SCN: 274424

PL/SQL procedure successfully completed.

3. While still connected as an administrative user in SQL*Plus to the source database, archived the current online redo log.

ALTER SYSTEM ARCHIVE LOG CURRENT;

4. Prepared the environment for database duplication, which included preparing the destination database as an auxiliary instance for duplication.

Created a password file via ORAPWD

5.Used the RMAN DUPLICATE command with the OPEN RESTRICTED option to instantiate the source database at the destination database.

RMAN> connect target

connected to target database: ORCL (DBID=1156232723)

RMAN> connect auxiliary sys/oracle@chase.us.oracle.com

connected to auxiliary database: CHASE (not mounted)

 

RMAN> spool log to /bugmnt11/am/celclnx1/database/rman_output.log

RMAN> run

{ set until SCN 274424;

set newname for datafile 1 to

"/bugmnt11/am/celclnx1/database/chase/system01.dbf";

set newname for datafile 2 to

"/bugmnt11/am/celclnx1/database/chase/undotbs01.dbf";

set newname for datafile 3 to

"/bugmnt11/am/celclnx1/database/chase/sysaux01.dbf";

set newname for datafile 4 to

"/bugmnt11/am/celclnx1/database/chase/users01.dbf";

set newname for datafile 5 to

"/bugmnt11/am/celclnx1/database/chase/streams_tbs.dbf";

allocate auxiliary channel d1 type disk;

allocate auxiliary channel d2 type disk;

duplicate target database to chase

nofilenamecheck

open restricted;

}

RMAN>

6. Checked the GLOBAL_NAME of the duplicated database:

SQL> select * from global_name;

GLOBAL_NAME

----------------------------------------------------------------

CHASE.US.ORACLE.COM

7. Connected as the Streams administrator in SQL*Plus to the destination database, created a database link to the source database.

SQL> connect strmadmin/strmadmin

Connected.

SQL> create database link orcl.us.oracle.com

2 connect to strmadmin identified by strmadmin

3 using 'orcl.us.oracle.com';

Database link created.

SQL> select * from global_name;

GLOBAL_NAME

----------------------------------------------------------------

CHASE.US.ORACLE.COM

 

SQL> select * from global_name@orcl.us.oracle.com;

GLOBAL_NAME

----------------------------------------------------------------

ORCL.US.ORACLE.COM

Setting Up Streams After Instantiation

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

To set up Streams after instantiation, ran the POST_INSTANTIATION_SETUP procedure.

The POST_INSTANTIATION_SETUP procedure must be run at the database where the PRE_INSTANTIATION_SETUP procedure was run

The parameter values specified in 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,

start_processes

SQL> connect strmadmin/strmadmin

Connected.

SQL> show user

USER is "STRMADMIN"

SQL> select * from global_name;

GLOBAL_NAME

----------------------------------------------------------------

ORCL.US.ORACLE.COM

SQL> DECLARE

empty_tbs DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;

BEGIN

DBMS_STREAMS_ADM.POST_INSTANTIATION_SETUP(

maintain_mode => 'GLOBAL',

tablespace_names => empty_tbs,

source_database => 'ORCL.US.ORACLE.COM',

destination_database => 'CHASE.US.ORACLE.COM',

perform_actions => true,

script_name => NULL,

script_directory_object => NULL,

capture_name => 'capture_maint',

capture_queue_table => 'strmadmin.capture_q_table',

capture_queue_name => 'strmadmin.capture_q',

propagation_name => 'prop_maint',

apply_name => 'apply_maint',

apply_queue_table => 'strmadmin.apply_q',

apply_queue_name => 'strmadmin.apply_q_table',

bi_directional => true,

include_ddl => true,

start_processes => false,

instantiation_scn => 274424,

exclude_schemas => 'DIP,OUTLN,STRMADMIN,DBSNMP,TSMSYS',

exclude_flags => DBMS_STREAMS_ADM.EXCLUDE_FLAGS_UNSUPPORTED +

DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DML + DBMS_STREAMS_ADM.EXCLUDE_FLAGS_DDL);

END;

/

PL/SQL procedure successfully completed.

Finishing the Maintenance Operation

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

While connected as an administrative user in SQL*Plus to the destination database, used the ALTER SYSTEM statement to disable the RESTRICTED SESSION:

SQL> CONNECT SYSTEM/oracle

Connected.

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

System altered.

While connected as the Streams administrator in SQL*Plus to the destination database, started the apply process.

SQL> connect strmadmin/strmadmin

Connected.

SQL> show user

USER is "STRMADMIN"

SQL> select * from global_name;

GLOBAL_NAME

----------------------------------------------------------------

CHASE.US.ORACLE.COM

SQL> BEGIN

DBMS_APPLY_ADM.START_APPLY(

apply_name => 'apply_maint');

END;

/

PL/SQL procedure successfully completed.

SQL> select apply_name, status from dba_apply;

APPLY_NAME STATUS

------------------------------ --------

APPLY_MAINT ENABLED

While connected as the Streams administrator in SQL*Plus to the capture database, started the apply process.

SQL> show user

USER is "STRMADMIN"

SQL> select * from global_name;

GLOBAL_NAME

----------------------------------------------------------------

ORCL.US.ORACLE.COM

SQL> select apply_name, status from dba_apply;

APPLY_NAME STATUS

------------------------------ --------

APPLY_MAINT DISABLED

SQL> BEGIN

DBMS_APPLY_ADM.START_APPLY(

apply_name => 'apply_maint');

END;

/

PL/SQL procedure successfully completed.

While connected as the Streams administrator in SQL*Plus to the capture database, start the capture process.

CHASE

-----

SQL> show user

USER is "STRMADMIN"

SQL> select * from global_name;

GLOBAL_NAME

----------------------------------------------------------------

CHASE.US.ORACLE.COM

SQL> select capture_name, status from dba_capture;

CAPTURE_NAME STATUS

------------------------------ --------

CAPTURE_MAINT DISABLED

SQL> BEGIN

DBMS_CAPTURE_ADM.START_CAPTURE(

capture_name => 'capture_maint');

END;

/

PL/SQL procedure successfully completed.

SQL> select capture_name, state from v$streams_capture;

CAPTURE_NAME STATE

------------------------------ ------------------------------

CAPTURE_MAINT      CAPTURING CHANGES

SQL>

ORCL

----

SQL> show user

USER is "STRMADMIN"

SQL> select capture_name, status from dba_capture;

CAPTURE_NAME STATUS

------------------------------ --------

CAPTURE_MAINT DISABLED

SQL> BEGIN

DBMS_CAPTURE_ADM.START_CAPTURE(

capture_name => 'capture_maint');

END;

/

PL/SQL procedure successfully completed.

SQL> select * from global_name;

GLOBAL_NAME

----------------------------------------------------------------

ORCL.US.ORACLE.COM

SQL> select capture_name, state from v$streams_capture;

CAPTURE_NAME STATUS

------------------------------ --------

CAPTURE_MAINT    CAPTURING CHANGES

Changes flow back and forth between ORCL and CHASE databases .

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14876437/viewspace-592554/,如需转载,请注明出处,否则将追究法律责任。

下一篇: linux NIS 配置手冊
请登录后发表评论 登录
全部评论

注册时间:2009-02-16

  • 博文量
    91
  • 访问量
    43661