My script failed with the following error:
ERROR at line 1: ORA-23616: Failure in executing block 8 for script 954444D7D90C47DBE040A8C07702188D with ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "SYS.DBMS_RECO_SCRIPT_INVOK", line 139 ORA-06512: at "SYS.DBMS_STREAMS_RPC", line 465 ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 659 ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 682 ORA-06512: at "SYS.DBMS_STREAMS_MT", line 7972 ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 2526 ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 2593 ORA-06512: at line 2
In 11.2 there is a package called DBMS_RECOVERABLE_SCRIPT that can be used to manage recoverable scripts. Following my original post I now know this package should not be called directly. See below for further details.
There is also a set of data dictionary views including:
DBA_RECOVERABLE_SCRIPT contains one row for each recoverable script. The primary key is the SCRIPT_ID - in my case "954444D7D90C47DBE040A8C07702188D" which is obviously system-generated.
The following query reports the current status of the script:
SQL> SELECT status, total_blocks, done_block_num 2 FROM dba_recoverable_script 3 WHERE script_id = '954444D7D90C47DBE040A8C07702188D'; STATUS TOTAL_BLOCKS DONE_BLOCK_NUM ------------ ------------ -------------- ERROR 11 7
So this query confirms that my script has failed with an error having completed seven of its eleven blocks.
The recoverable script is divided into blocks. DBA_RECOVERABLE_SCRIPT_BLOCKS contains one row for each block in the recoverable script. The primary key is formed by the SCRIPT_ID and BLOCK_NUM columns. Each block consists of a FORWARD_BLOCK CLOB which contains the actions required to complete the block and an UNDO_BLOCK CLOB which contains the actions required to reverse out the block. There is also a STATUS column and a BLOCK_COMMENT column.
The following script provides a more detailed summary of the execution of the script:
SQL> SELECT block_num,status
2 FROM dba_recoverable_script_blocks
3* WHERE script_id = '954444D7D90C47DBE040A8C07702188D' ORDER BY 1
BLOCK_NUM STATUS
---------- ------------
1 EXECUTED
2 EXECUTED
3 EXECUTED
4 EXECUTED
5 EXECUTED
6 EXECUTED
7 EXECUTED
8 ERROR
9 NOT EXECUTED
10 NOT EXECUTED
11 NOT EXECUTED
The error was in block 8.
Let's find out what the block 8 was trying to do.
SELECT forward_block
FROM dba_recoverable_script_blocks
WHERE script_id = '954444D7D90C47DBE040A8C07702188D'
AND block_num = 8;
--
-- Get tag value to be used for Apply
--
DECLARE
found BINARY_INTEGER := 0;
tag_num NUMBER;
apply_nm VARCHAR2(30);
apply_nm_dqt VARCHAR2(32);
BEGIN
SELECT apply_name INTO apply_nm
FROM dba_apply_progress
WHERE source_database = 'EAST.JULIANDYKE.COM';
apply_nm_dqt := '"' || apply_nm || '"';
-- Use the apply object id as the tag
SELECT o.object_id INTO tag_num
FROM dba_objects o
WHERE o.object_name= apply_nm
AND o.object_type='APPLY';
LOOP
BEGIN
found := 0;
SELECT 1 INTO found FROM dba_apply
WHERE apply_name != apply_nm
AND apply_tag = hextoraw(tag_num);
EXCEPTION WHEN no_data_found THEN
EXIT;
END;
EXIT WHEN (found = 0);
tag_num := tag_num + 1;
END LOOP;
-- alter apply
dbms_apply_adm.alter_apply(
apply_name => apply_nm_dqt,
apply_tag => hextoraw(tag_num));
END;
Now let's check the error message in DBA_RECOVERABLE_SCRIPT_ERRORS for this block.
SQL> SELECT error_message 2 FROM dba_recoverable_script_errors 3 WHERE script_id = '954444D7D90C47DBE040A8C07702188D' 4 AND block_num = 8; ERROR_MESSAGE ---------------------------------------------------------------------------- ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "SYS.DBMS_RECO_SCRIPT_INVOK", line 139 ORA-06512: at "SYS.DBMS_STREAMS_RPC", line 465
So this confirms what we already knew - an exact fetch has probably returned more than one row.
Not particularly useful if you captured the original failure in SQL*Plus; very useful if the error was embedded in application code or the exception was not captured correctly.
DBA_RECOVERABLE_SCRIPT_PARAMS contains a list of parameters for the script together with their values; in this case there were 22. In this example the list of parameters is very similar to the list of parameters for the MAINTAIN_TABLES procedure in the DBMS_STREAMS_ADM package.
The other view is DBA_RECOVERABLE_SCRIPT_HIST which contains a history of script executions. When I was testing this feature in a newish database, this view was empty suggesting that rows are only added to the history view when the entire script has executed successfully or when it is deleted.
Back to the error - well I'm not impressed. Prior to testing Oracle Streams I had set up Change Data Capture of which more another time. On the apply database the statement:
SELECT apply_name,source_database FROM dba_apply_progress;
returned the following output:
APPLY_NAME SOURCE_DATABASE ------------------------------ ------------------------------ CDC$A_CHANGE_SET3 EAST.JULIANDYKE.COM APPLY$_EAST_5 EAST.JULIANDYKE.COMI guess there are a couple of ways to fix this - delete the CDC configuration or modify the FORWARD_BLOCK for the script. Neither solution is particularly user-friendly. However, at least I know what the problem is...
I decided to drop the CDC apply process using:
BEGIN
dbms_apply_adm.stop_apply ('CDC$A_CHANGE_SET3',force=>TRUE);
dbms_apply_adm.drop_apply ('CDC$A_CHANGE_SET3');
END;
/
Now the query of DBA_APPLY_PROGRESS only returns one row (the correct one).
Alternatively I suspect I could have corrected the recoverable script using the MODIFY_FORWARD_BLOCK in the DBMS_RECOVERABLE_SCRIPT package.
We now need to restart the recoverable script. The obvious thing to do here is to look up the package in the PL/SQL Packages and Types Reference, but unfortunately this package is not documented. So back to trial and error...
The obvious candidate is the RUN procedure in the DBMS_RECOVERABLE_SCRIPT package. This turns out to have been the wrong choice as noted in the comment from Pat McElroy; I should have used the RECOVER_OPERATION procedure in the DBMS_STREAMS_ADM package which can be used to roll forward, roll back or purge a recoverable script. This makes a lot more sense - I was uncomfortable using the RUN procedure as I was not certain whether it would re-execute the successful steps for a second time.
I originally executed the MAINTAIN_TABLES procedure as the GP user (part of my Formula 1 database). When I attempted to execute the RUN procedure as this user I got an error because it could not directly access the DBMS_RECOVERABLE_SCRIPT package. To speed things up I switched to the SYS user and run:
GRANT EXECUTE ON DBMS_RECOVERABLE_SCRIPT TO GP; CREATE PUBLIC SYNONYM DBMS_RECOVERABLE_SCRIPT FOR SYS.DBMS_RECOVERABLE_SCRIPT;
Then as the GP user I was able to execute the following command:
BEGIN dbms_recoverable_script.run (script_id=> '954444D7D90C47DBE040A8C07702188D'); END; /
After a few seconds this script returned the following:
PL/SQL procedure successfully completed.
Do not try this at home - as noted above I should have used the RECOVER_OPERATION procedure in the DBMS_STREAMS_ADM package.
So Oracle thinks it worked. Do I agree? A review of the dictionary table reveals the following:
DBA_RECOVERABLE_SCRIPT is now empty; the script I was executing has disappeared.
DBA_RECOVERABLE_SCRIPT_ERRORS is also empty. Good - no more errors.
DBA_RECOVERABLE_SCRIPT_BLOCKS still contains the 11 blocks for the script we have been executing.
DBA_RECOVERABLE_SCRIPT_PARAMS still contains the 22 parameters for the script we have been executing.
DBA_RECOVERABLE_SCRIPT_HIST now contains one row for the script we have successfully executed. The primary key is SCRIPT_ID. We can check the status of this script using:
SQL> SELECT status, total_blocks, done_block_num 2 FROM dba_recoverable_script_hist 3 WHERE script_id = '954444D7D90C47DBE040A8C07702188D'; STATUS TOTAL_BLOCKS DONE_BLOCK_NUM -------- ------------ -------------- EXECUTED 11 11All seems to make sense.
However I do think it would help if the DBMS_RECOVERABLE_SCRIPT package was added to the Package Types and Reference manual stating that scripts should not be run directly using this package and that the RECOVER_OPERATION procedure in the DBMS_STREAMS_ADM package should be run instead. The important thing for Oracle to remember here is that users are unlikely to encounter these scripts until something breaks at which point (like me) they will be under pressure to find a quick fix so they can proceed with the task in hand.