Guidance:
A few days ago, we used the package to complete the streams configuration. However, it would be more intuitive to use the OEM in management. Of course, it would be almost the same if we knew all the related packages. In 9i OEM, it seems that the function of streams is not very strong. It's just some simple applications. Next, we have time to build 10gr2, and we want to skip 10gr1. The following is a script used to create a single table copy on the OEM.
Source database: gates
Target database: Clone
1. Set Operations
-Turn on supplemental logging and switch log file at source database.
-Create and set default tablespace for logminer at source database.
-Capture the following tables in the source database, then spread and apply the changes to the target database.
"Scott". "bonus"
2. Export/import operations
-Export all objects selected from the source database.
-Import them to the target database.
3. Start the operation
-First, start the application process in the target database.
-Start the capture process in the source database.
--
Setup operations:
Rem
Rem the following sections setup streams at the destination Database
Rem
Accept dest_strmadmin_passwd prompt 'enter the target database
Password of strmadmin in Clone: 'hide
Connect strmadmin/& dest_strmadmin_passwd @ clone
Rem
Rem addapply rules fortables at the destination Database
Rem
Begin
Dbms_streams_adm.add_table_rules (
Table_name => '"Scott". "bonus "',
Streams_type => 'application ',
Streams_name => 'strmadmin _ gates_vongates _',
Queue_name => '"strmadmin". "streams_queue "',
Include_dml => true,
Include_ddl => true,
Source_database => 'gates. vongates. com ');
End;
/
Rem
Rem the following sections setup streams at the source database
Rem
Accept source_strmadmin_passwd prompt 'enter the source database
Password of strmadmin in Gates: 'hide
Connect sys/& source_dba_passwd @ gatesassysdba
Rem
Rem turn onsupplemental Logging
Rem
Alterdatabaseaddsupplemental logdata (primarykey, uniqueindex)
Columns;
Rem
Rem switch logfile
Rem
Altersystem switch logfile;
Rem
Rem createlogminer tablespace
Rem
Createtablespace logmnrts datafile 'logmnrts _ gates. dbf'
Size 25 m Reuse
Autoextend onmaxsize unlimited;
Rem
Rem setlogminer tablespace
Rem
Begin
Dbms_logmnr_d.set_tablespace ('logmninsts ');
End;
/
Connect strmadmin/& source_strmadmin_passwd @ gates
Rem
Rem addcapture rules fortables at the source database
Rem
Begin
Dbms_streams_adm.add_table_rules (
Table_name => '"Scott". "bonus "',
Streams_type => 'capture ',
Streams_name => 'strmadmin _ capture ',
Queue_name => '"strmadmin". "streams_queue "',
Include_dml => true,
Include_ddl => true,
Source_database => 'gates. vongates. com ');
End;
/
Rem
Rem addpropagation rules fortables at the source database
Rem
Begin
Dbms_streams_adm.add_table_propagation_rules (
Table_name => '"Scott". "bonus "',
Streams_name => 'strmadmin _ pagate ',
Source_queue_name => '"strmadmin". "streams_queue "',
Destination_queue_name => '"strmadmin". "streams_queue" @ clone. vongates. com ',
Include_dml => true,
Include_ddl => true,
Source_database => 'gates. vongates. com ');
End;
/
-- Export/import operations:
Expuserid = "strmadmin" @ gatestables = "Scott". "bonus"
File = tables. dmp grants = y rows = y log = exporttables. Log
Object_consistent = y indexes = y
IMP userid = "strmadmin" @ clonefull = y constraints = y file = tables. dmp
Ignore = y grants = y rows = y commit = y log = importtables. Log
Streams_configuration = y streams_instantiation = y
-- Startup operations:
Accept dest_strmadmin_passwd prompt 'enter the target database
Password of strmadmin in Clone: 'hide
Connect strmadmin/& dest_strmadmin_passwd @ clone
Rem
Rem start apply process at the destination Database
Rem
Declare
V_started
Number ;
Begin
Selectdecode (status, 'enabled ',
1 ,
0 ) Intov_started
Fromdba_apply whereapply_name = 'strmadmin _ gates_vongates _';
If (v_started =
0 ) Then
Dbms_apply_adm.start_apply (apply_name => 'strmadmin _ gates_vongates _');
Endif;
End;
/
Accept source_strmadmin_passwd prompt 'enter the source database
Password of strmadmin in Gates: 'hide
Connect strmadmin/& source_strmadmin_passwd @ gates
Declare
V_started
Number ;
Begin
Selectdecode (status, 'enabled ',
1 ,
0 ) Intov_started
Fromdba_capture wherecapture_name = 'capture ';
If (v_started =
0 ) Then
Dbms_capture_adm.start_capture (capture_name => 'capture ');
Endif;
End;
/
This article is transferred from
Http://oracle.itpub.net/post/20957/146515
