Oracle 12cR2 DBMS_RECOVERABLE

Oracle DBMS_RECOVERABLE_SCRIPT Version 12.2.0.1

General Information

Library Note

Morgan's Library Page Header

"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"

~ Sen. Tammy Duckworth

Purpose Undocumented but related to Streams

AUTHID CURRENT_USER

Data Types TYPE connect_info IS RECORD ( username VARCHAR2(30), password VARCHAR2(30), connect_identifier VARCHAR2(30), connect_key VARCHAR2(??), i_customer_id BINARY_INTEGER); TYPE connect_info_set IS TABLE OF connect_info;

Dependencies

CDB_RECOVERABLE_SCRIPT	DBA_RECOVERABLE_SCRIPT_HIST	DBMS_STREAMS_AUTO_INT
CDB_RECOVERABLE_SCRIPT_BLOCKS	DBA_RECOVERABLE_SCRIPT_PARAMS	DBMS_STREAMS_MC
CDB_RECOVERABLE_SCRIPT_ERRORS	DBMS_ASSERT	DBMS_STREAMS_MT
CDB_RECOVERABLE_SCRIPT_HIST	DBMS_LOGREP_UTIL	DBMS_STREAMS_SM
CDB_RECOVERABLE_SCRIPT_PARAMS	DBMS_RECO_SCRIPT_INT	DBMS_SYS_ERROR
DBA_RECOVERABLE_SCRIPT	DBMS_RECO_SCRIPT_INVOK	DBMS_UTILITY
DBA_RECOVERABLE_SCRIPT_BLOCKS	DBMS_RECO_SCRIPT_LIB	PLITBLM
DBA_RECOVERABLE_SCRIPT_ERRORS	DBMS_STANDARD	RECO_SCRIPT$

Documented No

First Available 10gR1

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/prvthrse.plb

Subprograms

ADD_FORWARD_BLOCK	INSERT_PARAM	RUN
CREATE_SCRIPT	MODIFY_FORWARD_BLOCK	UPDATE_COMMENT
DROP_SCRIPT	MODIFY_UNDO_BLOCK	UPDATE_STATUS

ADD_FORWARD_BLOCK

Undocumented dbms_recoverable_script.add_forward_block( script_id IN RAW, block IN CLOB, dblink_forward IN VARCHAR2, block_comment IN VARCHAR2, block_num IN NUMBER);

TBD

CREATE_SCRIPT

Creates a recoverable script dbms_recoverable_script.create_script( invoking_pkg_owner IN VARCHAR2, invoking_pkg IN VARCHAR2, invoking_proc IN VARCHAR2, script_comment IN VARCHAR2, script_id OUT RAW);

CREATE OR REPLACE PACKAGE testpkg AUTHID CURRENT_USER IS

        PROCEDURE display_string(teststr IN VARCHAR2);

      END testpkg;

      /

      

      CREATE OR REPLACE PACKAGE BODY testpkg IS

        PROCEDURE display_string(teststr IN VARCHAR2) IS

        BEGIN

          dbms_output.put_line(teststr);

        END;

      END testpkg;

      /

      

      SELECT * FROM dba_recoverable_script;

      SELECT * FROM dba_recoverable_script_blocks;

      SELECT * FROM dba_recoverable_script_errors;

      SELECT * FROM dba_recoverable_script_hist;

      SELECT * FROM dba_recoverable_script_params;

      

      set serveroutput on

      

      DECLARE

       r RAW(32);

      BEGIN

        dbms_recoverable_script.create_script('Test', 'A', 'B', 'C', r);

        dbms_output.put_line(r);

      END;

      /

      

      SELECT * FROM dba_recoverable_script;

      SELECT * FROM dba_recoverable_script_blocks;

      SELECT * FROM dba_recoverable_script_errors;

      SELECT * FROM dba_recoverable_script_hist;

      SELECT * FROM dba_recoverable_script_params;

      

      -- replace the raw value with the one you receive

      exec dbms_recoverable_script.drop_script(TO_RAW(TO_BLOB('CB68CBC3E4264A1680AE4620CFE6CD66')));

      

      

      DECLARE

       r RAW(32);

      BEGIN

        dbms_recoverable_script.create_script('Test1', 'DISPLAY_STRING', 'TESTPKG', 'SYS', r);

        dbms_output.put_line(r);

        dbms_recoverable_script.insert_param(r, 'TESTSTR', 'Morgan', 1);

        dbms_recoverable_script.update_comment(r, 'Test2');

        dbms_recoverable_script.run(r); 

        dbms_recoverable_script.drop_script(r);

      END;

      /

      

      SELECT * FROM dba_recoverable_script;

      SELECT * FROM dba_recoverable_script_blocks;

      SELECT * FROM dba_recoverable_script_errors;

      SELECT * FROM dba_recoverable_script_hist;

      SELECT * FROM dba_recoverable_script_params;

DROP_SCRIPT

Drops a recoverable script dbms_recoverable_script.drop_script( script_id IN RAW, flags IN BINARY_INTEGER, ignore_remote_error IN BOOLEAN, check_owner IN BOOLEAN, force IN BOOLEAN);

See CREATE_SCRIPT Demo Above

INSERT_PARAM

Undocumented dbms_recoverable_script.insert_param( script_id IN RAW, name IN VARCHAR2, value IN VARCHAR2, param_index IN NUMBER);

See CREATE_SCRIPT Demo Above

MODIFY_FORWARD_BLOCK

Undocumented dbms_recoverable_script.modify_forward_block( script_id IN RAW, block_num IN VARCHAR2, forward_block IN CLOB, dblink_forward IN VARCHAR2, action IN VARCHAR2);

TBD

MODIFY_UNDO_BLOCK

Undocumented dbms_recoverable_script.modify_undo_block( script_id IN RAW, block_num IN NUMBER, undo_block IN CLOB, dblink_undo IN VARCHAR2, action IN VARCHAR2);

TBD

RUN

Run a recovery script

Overload 1 dbms_recoverable_script.run( script_id IN RAW, forward IN BOOLEAN, remote_state IN CLOB);

See CREATE_SCRIPT Demo Above

Overload 2 dbms_recoverable_script.run( script_id IN RAW, forward IN BOOLEAN, remote_state IN CLOB, flags IN BINARY_INTEGER, conn_info IN dbms_recoverable_script.connect_info_set);

TBD

UPDATE_COMMENT

Modify a script comment dbms_recoverable_script.update_comment( script_id IN RAW, script_comment IN VARCHAR2);

See CREATE_SCRIPT Demo Above

UPDATE_STATUS

Undocumented dbms_recoverable_script.update_status( script_id IN RAW, script_status IN NUMBER);

TBD