SQL> select * from dba_recoverable_script_errors;
SCRIPT_ID BLOCK_NUM ERROR_NUMBER
——————————– ———- ————
ERROR_MESSAGE
——————————————————————————–
ERROR_CRE
———
43CADDA5148638A9E040C2A396406660 6 -29341
ORA-29341: The transportable set is not self-contained
15-JAN-08
SQL>
SQL> exec dbms_tts.transport_set_check('EX1', FALSE, TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
——————————————————————————–
Sys owned object TE1 in tablespace EX1 not allowed in pluggable set
SQL> drop table te1 purge;
Table dropped.
SQL> exec dbms_tts.transport_set_check(‘EX1′, FALSE, TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
SQL>
SQL> conn strm/strm
Connected.
SQL> DECLARE
2 t_names DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
3 BEGIN
4 t_names(1) := ‘”EX1″‘;
5 DBMS_STREAMS_ADM.MAINTAIN_TABLESPACES(
6 tablespace_names => t_names,
7 source_directory_object => ‘”SRC_DIRS”‘,
8 destination_directory_object => ‘”DST_DIRS”‘,
9 destination_database => ‘EURO.ORACLE.COM’ ,
10 setup_streams => true,
11 script_name => ‘Strm_1200439935274.sql’,
12 script_directory_object => ‘”SRC_DIRS”‘,
13 dump_file_name => ‘Strm_1200439935274.dmp’,
14 capture_name => ‘”STREAMS_CAPTURE”‘,
15 propagation_name => ‘”STREAMS_PROPAGATION”‘,
16 apply_name => ‘”STREAMS_APPLY”‘,
17 source_queue_name => ‘”STRM”.”STREAMS_CAPTURE_Q”‘,
18 destination_queue_name => ‘”STRM”.”STREAMS_CAPTURE_Q”‘,
19 log_file => ‘Strm_1200439935274.log’,
20 bi_directional => true);
21 END;
22 /
DECLARE
*
ERROR at line 1:
ORA-23622: Operation SYS.DBMS_STREAMS_ADM.MAINTAIN_TABLESPACES is in progress.
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 105
ORA-06512: at “SYS.DBMS_STREAMS_MT”, line 2334
ORA-06512: at “SYS.DBMS_STREAMS_MT”, line 7451
ORA-06512: at “SYS.DBMS_STREAMS_ADM”, line 2205
ORA-06512: at line 5
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Pr oduction
With the Partitioning, OLAP and Data Mining options
[oracle@erd-tt-eproof1 ~]$ oerr ora 23622
23622, 00000, “Operation %s.%s.%s is in progress.”
// *Cause: An attempt was made to execute a procedure which was being
// executed in a parallel session or failed execution.
// *Action: Query the DBA_RECOVERABLE_SCRIPT view to identify the operation
// that is currently in progress for the specified invoking
// procedure. Complete the operation before proceeding.
[oracle@erd-tt-eproof1 ~]$ amer
SQL*Plus: Release 10.2.0.2.0 – Production on Tue Jan 15 17:42:27 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from DBA_RECOVERABLE_SCRIPT;
SCRIPT_ID CREATION_ INVOKING_PACKAGE_OWNER
——————————– ——— ——————————
INVOKING_PACKAGE INVOKING_PROCEDURE
—————————— ——————————
INVOKING_USER STATUS TOTAL_BLOCKS DONE_BLOCK_NUM
—————————— ———— ———— ————–
SCRIPT_COMMENT
——————————————————————————–
43CADDA5148638A9E040C2A396406660 15-JAN-08 SYS
DBMS_STREAMS_ADM MAINTAIN_TABLESPACES
STRM ERROR 16 5
SQL> select * from dba_recoverable_script_errors;
SCRIPT_ID BLOCK_NUM ERROR_NUMBER
——————————– ———- ————
ERROR_MESSAGE
——————————————————————————–
ERROR_CRE
———
43CADDA5148638A9E040C2A396406660 6 -29341
ORA-29341: The transportable set is not self-contained
15-JAN-08
SQL> exec DBMS_STREAMS_ADM.RECOVER_OPERATION(’43CADDA5148638A9E040C2A396406660′,’ROLLBACK’);
PL/SQL procedure successfully completed.
SQL> select * from dba_recoverable_script_errors;
no rows selected
SQL>
After the above troubleshooting, used EM, successfully configured tablespace replication using Oracle Streams.
The following is the script generated by EM:
set echo on;
ACCEPT strm_pwd_src PROMPT ‘Enter Password of Streams Admin “strm” at Source : ‘ HIDE
ACCEPT strm_pwd_dest PROMPT ‘Enter Password of Streams Admin “strm” at Destination : ‘ HIDE
connect “STRM”/&strm_pwd_src;
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => ‘”STREAMS_CAPTURE_QT”‘,
queue_name => ‘”STREAMS_CAPTURE_Q”‘,
queue_user => ‘”STRM”‘);
END;
/
connect “STRM”/&strm_pwd_dest@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=
(PROTOCOL=TCP)(HOST=erd-tt-eproof2.ctc.trb)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=euro.oracle.com)(server=DEDICATED)));
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => ‘”STREAMS_APPLY_QT”‘,
queue_name => ‘”STREAMS_APPLY_Q”‘,
queue_user => ‘”STRM”‘);
END;
/
connect “STRM”/&strm_pwd_src;
create or replace directory “EMSTRMTBLESPCEDIR_0″ AS ‘/home/oracle/oracle/oradata/AMER’;
DECLARE
t_names DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
BEGIN
t_names(1) := ‘”EX1″‘;
DBMS_STREAMS_ADM.MAINTAIN_TABLESPACES(
tablespace_names => t_names,
source_directory_object => ‘”SRC_DIRS”‘,
destination_directory_object => ‘”DST_DIRS”‘,
destination_database => ‘EURO.ORACLE.COM’ ,
setup_streams => true,
script_name => ‘Strm_1200440796848.sql’,
script_directory_object => ‘”SRC_DIRS”‘,
dump_file_name => ‘Strm_1200440796848.dmp’,
capture_name => ‘”STREAMS_CAPTURE”‘,
propagation_name => ‘”STREAMS_PROPAGATION”‘,
apply_name => ‘”STREAMS_APPLY”‘,
source_queue_name => ‘”STRM”.”STREAMS_CAPTURE_Q”‘,
destination_queue_name => ‘”STRM”.”STREAMS_CAPTURE_Q”‘,
log_file => ‘Strm_1200440796848.log’,
bi_directional => true);
END;
/
