Oracle 12c DBMS_RECOVERABLE

Oracle DBMS_RECOVERABLE_SCRIPT Version 12.1.0.2

General Information

Library Note

Morgan's Library Page Footer

The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,

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

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