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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/14876437/viewspace-592554/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
