10g Streams Replication Notes [newly added footnote] How to remove streams in 10g? I follow the instruction in "Making Data Flow - Use Oracle Streams to make data move itself" By Sanjay Mishra at www.oracle.com/technology/oramag/oracle/04-nov/o64streams.html to set up Streams Replication. It's a simplified version of "Single Database Capture and Apply Example", Chapter 16 of Streams Concepts and Admin guide of 10g documentation (online at http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10727/capappde.htm ). My environment: 10.1.0.2.0 (Enterprise Ed.), Windows XP SP1 Source DB is ORCL and destination ORCLREP, except in the bidirectional test where both are source and destination. Both databases run on the same laptop and each use their own listener LISTENER and LISTENERREP. ORCL is in archivelog mode. In bidirectional test, ORCLREP is also changed to archivelog mode. The following are observations or problem solutions in my experiment. *** Quick health check of Streams replication *** Note:273674.1 "Streams Configuration Report and Health Check Script" does comprehensive check. Chapter 14 "Monitoring a Streams Environment" of "Streams Concepts and Administration" manual does a similar job. They're both too long. A quick check can be done by: select * from dba_capture (on source; pay attention to status, and error_message if any; status should be 'ENABLED') select * from v$streams_capture (on source; state should be 'CAPTURING CHANGES') select * from v$propagation_sender (on source; schedule_status should be 'SCHEDULE ENABLED') select * from dba_apply (on destination; status should be 'ENABLED'; note error_message if any) select * from v$streams_apply_server (on dest.; state may be 'IDLE') select * from v$streams_apply_reader (on dest.; state should be 'DEQUEUE MESSAGES') select * from v$streams_apply_coordinator (on dest.; state should be 'APPLYING') If the status or state is not right and/or you get no row when you query one of the views, consider restarting capture and apply processes: exec dbms_capture_adm.start_capture('CAPTURE_STREAM') in source exec dbms_apply_adm.start_apply('APPLY_STREAM') in destination If schedule is disabled (see below), exec dbms_aqadm.enable_propagation_schedule('STREAMS_QUEUE', 'ORCLREP') *** Propagation stops because of disabled schedule *** For a few weeks, I haven't started ORCLREP database, but ORCL is opened regularly. Update scott.emp set ... in ORCL does not propagate to ORCLREP. I notice that v$propagation_sender.schedule_status is 'SCHEDULE DISABLED'! There's no job in dba_jobs (except the EM one). Restarting capture or apply process doesn't solve the problem. And SQL> select schedule_disabled, failures, last_error_msg 2 from dba_queue_schedules 3 where schema = 'STRMADMIN' and qname = 'STREAMS_QUEUE'; S FAILURES - ---------- LAST_ERROR_MSG ------------------------------------------------------------------------------- Y 16 ORA-12514: TNS:listener does not currently know of service requested in connect descriptor (Other observations: Captured_scn and applied_scn in dba_capture in ORCL are both 1072982 and start_scn and first_scn are both 620074. (Database SCN, dbms_flashback.get_system_change_number is 1082232). But on ORCLREP, all these numbers are much lower, but higher than 620074.) According to Note:273674.1, "After each failure, the next-time is incremented exponentially. The schedule becomes automatically disabled after 16 successive attempts to execute the schedule fail (FAILURES). Manual intervention (dbms_aqadm.enable_propagation_schedule) is required to re-enable the schedule after 16 failures. Information for this section comes from the DBA_QUEUE_SCHEDULES view." So the solution is exec dbms_aqadm.enable_propagation_schedule('STREAMS_QUEUE', 'ORCLREP'). After this, the propagation is performed and dba_jobs (not dba_scheduler_jobs) has a new entry whose log_user='STRMADMIN' (priv_user and schema_user are 'SYS') and what='next_date := sys.dbms_aqadm.aq$_propaq(job);'. Dba_queue_schedules.schedule_disabled changes to 'N', process_name changes from null to 'J000', session_id is populated, last_error_msg becomes null and failures changes to 0. v$propagation_sender.schedule_status changes to 'SCHEDULE ENABLED'. dba_jobs.job keeps incrementing; it seems the old job is removed and a new job is scheduled every few minutes with new (this|next)_(date|sec) *** Accidentally run DBMS_STREAMS_ADM.ADD_TABLE_RULES as another user *** I ran add_table_rules as SYS accidentally. Exec dbms_streams_adm.remove_rule won't work because the 3rd argument (streams name) is required but it's not found in dba_streams_rules. So I have to exec exec dbms_rule_adm.drop_rule_set('RULESET$_20',true) where 'RULESET$_20' is from dba_rule_set_rules and true drops rules in the ruleset. (Don't drop the other ruleset owned by SYS; they're used for scheduling.) *** Committed transaction propagates *** If a transaction does not commit, even switching logfiles does not propagate the change. Have to commit. *** dba_apply_progress *** dba_apply_progress has one row for the last apply operation (but the update of it may lag behind the actual user data update, till the job in dba_jobs runs), and its applied_message_number is actually SCN as seen in sys.sys.streams$_apply_progress, which also has the history of the apply operations. *** dba_(capture|apply)_parameters *** On both capture and apply side, this view contains the same info as in sys.streams$_process_params. But the latter additionally has undocumented parameters, those starting with _. *** Compare with SharePlex of Quest (Could be wrong) *** * SharePlex does not need supplemental logging but Streams does. Can't think of anything else for now. Will update later. *** Bidirectional replication *** I want to replicate not just from ORCL to ORCLREP, but also from ORCLREP to ORCL. I change ORCLREP to run in archivelog mode, and go back to the article "Making Data Flow". On the source side ORCLREP, * create a DB link pointing to ORCL (Step 4) * add supplemental logging to scott.emp (Step 6) * add table rules of capture streams type on scott.emp (Step 7) * add table propagation rules on scott.emp (Step 8, Listing 1) On destination side,* ORCL, grant all on emp to strmadmin in ORCL (Step 10) Back to ORCLREP, * run the procedure to set instantiation SCN in ORCL (Step 11, Listing 2; note that you run this procedure, which is in dbms_apply_adm, from source side ORCLREP but it actually runs inside destination ORCL through link @ORCL, because you want to pass source DB SCN to the procedure) In ORCL again, * add table rules of apply streams type on scott.emp (Step 12). That should work. However, I set SGA too small in ORCL and got multiple trace files in bdump showing ORA-4031, and empty v$streams_apply_(server|reader|coordinator) tells me all streams apply processes died. In addition, dba_apply.status in ORCL and dba_capture.status in ORCLREP shows 'DISABLED'. I set streams_pool_size=20m in ORCL (should've done it; it's said that without setting it, 10%, adjustable by _first_spare_parameter in 9.2.0.5, of shared pool is used for streams). Anyway, all I needed to do after giving enough memory is exec dbms_capture_adm.start_capture('CAPTURE_STREAM') in ORCLREP exec dbms_apply_adm.start_apply('APPLY_STREAM') in ORCL But manual update of missed replication is also done. *** Conflict *** With bidirectional rep. set up, I update scott.emp changing one row in ORCL without commit, change the same row but to a different value in ORCLREP without commit. Then commit in one DB and immediately commit in the other. Then in ORCL, I get 'ORA-26714: User error encountered while applying' in dba_apply.error_message and its status changes to 'ABORTED', and the apply server, reader and coordinator are all gone. Dba_apply_error.error_message says 'ORA-01403: no data found'. I have to restart apply process and manually synchronize the data. (More to come. Not sure when I'll have time.) Yong Huang ________________ How to remove streams? Tired of playing with a new feature? Me too. But unlike others, streams is a pain to remove. Metalink Note:276648.1 is only about "Remove Streams Procedure for 9.2.0.X", not 10g, and it requires downloading a zip file and running the package inside. Trying to drop user strmadmin, or any tables belonging to him? You get errors like ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables or you're advised to call REMOVE_STREAMS_CONFIGURATION() instead. When you run that, you get errors like ORA-04067: not executed, stored procedure "STRMADMIN.AQ$_STREAMS_QUEUE_TABLE_E" does not exist So here's what I did. Truncate all tables belonging to strmadmin and drop the user (Deleting from the tables first may also work). select 'truncate table STRMADMIN.' || table_name || ';' from dba_tables where owner = 'STRMADMIN'; ... drop user strmadmin cascade; What a great relief! It reminds me of my way to get rid of some malware programs such as those from 3721.com. I had to use notepad to open the .exe files, empty them and save as 0-size files. After a while, I was able to remove the stupid software. CREDIT: Yong Huang invented this method of removing malware! WARNING: This method of removing software is unsupported. Do not do it on production.