Oracle | DBMS

Oracle DBMS_IR Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Warning: Demos on this page are for education purposes only: Do NOT run them!

If you ignore this advice you, and you alone, are solely responsible and liable for the damage you will have done.

And without doubt you will have done some so do not ignore this warning unless you like rebuilding servers.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
IR_FAILURE_CRITICAL	BINARY_INTEGER	1
IR_FAILURE_HIGH	BINARY_INTEGER	2
IR_FAILURE_LOW	BINARY_INTEGER	3

Data Types TYPE ir_failure_list_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE ir_failure_op_error IS RECORD ( failureID NUMBER, errorCode NUMBER); TYPE ir_error_list_type IS TABLE OF ir_failure_op_error INDEX BY BINARY_INTEGER; TYPE ir_repair_option_id IS RECORD ( repairID NUMBER, optionIdx NUMBER, spare1 NUMBER DEFAULT NULL, spare2 NUMBER DEFAULT NULL, spare3 NUMBER DEFAULT NULL, spare4 NUMBER DEFAULT NULL, spare5 NUMBER DEFAULT NULL); TYPE ir_repair_option_list IS TABLE OF ir_repair_option_id INDEX BY BINARY_INTEGER; TYPE ir_repair_feasibility IS RECORD ( failureIdx NUMBER, repairIdx NUMBER, feasibility BOOLEAN, dataLoss NUMBER DEFAULT NULL, repairTime NUMBER DEFAULT NULL, spare1 NUMBER DEFAULT NULL, spare2 NUMBER DEFAULT NULL, spare3 NUMBER DEFAULT NULL, spare4 NUMBER DEFAULT NULL, spare5 NUMBER DEFAULT NULL, -- mjs TODO: get max size of impact string impact VARCHAR2(2000) DEFAULT NULL); TYPE ir_repair_feasibility_list IS TABLE OF ir_repair_feasibility INDEX BY BINARY_INTEGER; TYPE ir_script_file_type IS TABLE OF VARCHAR2(513) INDEX BY BINARY_INTEGER;

Dependencies

DBMS_BACKUP_RESTORE

DBMS_SYS_ERROR

PLITBLM

Documented No

Exceptions

Error Code	Reason
ORA-51190	internal_error: for example: ORA-51190: Internal error [Fail set is empty], [0] from DBMS_IR
ORA-51191	too_many_opens_error
ORA-51192	not_open_error
ORA-51193	invalid parameter error number

First Available 12.1

Security Model Owned by SYS with execute granted to SYSBACKUP and the DBA role.

Source {ORACLE_HOME}/rdbms/admin/dbmsir.sql

Subprograms

ADDLINE	CONTROLFILECHECK	GETLINE
ADVISECANCEL	CREATESCRIPTFILE	OPENSCRIPTFILE
ADVISEDONE	CREATEWORKINGREPAIRSET	REEVALUATEOPENFAILURES
CHANGEPRIORITY	EXECSQLSCRIPT	STARTREPAIROPTION
CLOSEFAILURES	GETADVISEID	UPDATEFEASIBILITYANDIMPACT
CLOSESCRIPTFILE	GETERROR	UPDATEREPAIROPTION
COMPLETEREPAIROPTION	GETFEASABILITYANDIMPACT	WRITEFILE
CONSOLIDATEREPAIR	GETFILE

ADDLINE

Write a line to a script file dbms_ir.addLine( fileID IN NUMBER, line IN VARCHAR2);

See createScriptFile demo

ADVISECANCEL

Cancels an ADVISE conversation, releasing the context. This needs to be done if a conversation is going to be abandoned without successfully completing the command. This can be done anytime within the conversation after createWorkingRepairSet has been called and before adviseDone has been called. dbms_ir.adviseCancel(adviseID IN NUMBER);

set linesize 121

      col message format a60

      

      desc gv$ir_manual_checklist

      

      SELECT *

      FROM gv$ir_manual_checklist;

      

      desc gv$ir_repair

      

      SELECT repair_id, advise_id, summary, rank, time_detected, estimated_data_loss, repair_script

      FROM gv$ir_repair;

      

      col detailed_description format a80

      

      SELECT repair_id, advise_id detailed_description

      FROM gv$ir_repair;

      

      exec dbms_ir.adviseCancel(22);

ADVISEDONE

Called by RMAN to tell the server that an ADVISE has completed. Cause repair option information to be written to disk dbms_ir.adviseDone( adviseID IN NUMBER, generatedRepairs OUT ir_repair_option_list);

RMAN Internal

CHANGEPRIORITY

Change the priority of one or more IR failures. Will attempt to change all the failures in the list, even if errors prevent changing some of the failures.

Overload 1 dbms_ir.changePriority( failureList IN ir_failure_list_type, newPriority IN BINARY_INTEGER, errorList OUT ir_error_list_type );

TBD

Overload 2 dbms_ir.changePriority( failureList IN VARCHAR2, newPriority IN BINARY_INTEGER, errorID OUT NUMBER);

TBD

CLOSEFAILURES

Close one or more IR failures. Will attempt to close all the failures in the list, even if errors prevent changing some of the failures. Closing a parent failure will cause all the children to be closed.
Overload 1 dbms_ir.closeFailures( failureList IN ir_failure_list_type, errorList OUT ir_error_list_type);

TBD

Overload 2 dbms_ir.closeFailures( failureList IN VARCHAR2, errorID OUT NUMBER);

TBD

CLOSESCRIPTFILE

Close a repair script file dbms_ir.closeScriptFile(fileID IN NUMBER);

See createScriptFile demo

COMPLETEREPAIROPTION

Called after completing a repair. It updates the status of the repair in ADR. If the repair was successful it also reevaluates all open failures. dbms_ir.completeRepairOption( repairID IN NUMBER, repairSucceeded IN BOOLEAN);

set linesize 121

      col message format a60

      

      desc gv$ir_manual_checklist

      

      SELECT *

      FROM gv$ir_manual_checklist;

      

      desc gv$ir_repair

      

      SELECT repair_id, advise_id, summary, rank, time_detected, estimated_data_loss, repair_script

      FROM gv$ir_repair;

      

      col detailed_description format a80

      

      SELECT repair_id, advise_id detailed_description

      FROM gv$ir_repair;

      

      exec dbms_ir.completeRepairOption(22, TRUE);

CONSOLIDATEREPAIR

Called by RMAN to consolidate the repair options for an ADVISE command dbms_ir.consolidateRepair(adviseID IN NUMBER);

set linesize 121

      col message format a60

      

      desc gv$ir_manual_checklist

      

      SELECT *

      FROM gv$ir_manual_checklist;

      

      desc gv$ir_repair

      

      SELECT repair_id, advise_id, summary, rank, time_detected, estimated_data_loss, repair_script

      FROM gv$ir_repair;

      

      col detailed_description format a80

      

      SELECT repair_id, advise_id detailed_description

      FROM gv$ir_repair;

      

      exec dbms_ir.consolidateRepair(22);

CONTROLFILECHECK

Execute IR crosscheck for control file dbms_ir.controlFileCheck(cfname IN VARCHAR2) RETURN BOOLEAN;

set linesize 121

      col name format a15

      col value format a80

      

      SELECT name, value

      FROM gv$parameter

      WHERE name = 'control_files';

      

      set serveroutput on

      

      BEGIN

        IF dbms_ir.controlFileCheck('c:\oracle\product\oradata\orabase\control03.CTL') THEN

          dbms_output.put_line('True');

        ELSE

          dbms_output.put_line('False');

        END IF;

      END;

      /

CREATESCRIPTFILE

Called by RMAN to create and open a file to write a repair script dbms_ir.createScriptFile( fileID OUT NUMBER, fileName OUT VARCHAR2);

set serveroutput on

      

      DECLARE

       fid   NATURAL;

       fname VARCHAR2(100);

      BEGIN

        dbms_ir.createScriptFile(fid, fname );

        dbms_output.put_line(fname);

        dbms_output.put_line(fid);

      

        dbms_ir.addLine(fid, 'This is a test' );

        dbms_ir.closeScriptFile(fid);

      EXCEPTION

        WHEN OTHERS THEN

          dbms_ir.closeScriptFile(fid);

      END;

      /

CREATEWORKINGREPAIRSET

Creates an intermediate working repair set for ADVISE command. dbms_ir.createWorkingRepairSet(adviseID IN NUMBER);

exec dbms_ir.createWorkingRepairSet(22);

EXECSQLSCRIPT

Execute the specified sql script dbms_ir.execSQLScript(filename IN VARCHAR2);

TBD

GETADVISEID

Start an ADVISE command and get the ADVISE identifier

Overload 1 dbms_ir.getAdviseID( failureList IN ir_failure_list_type, adviseID OUT NUMBER);

TBD

Overload 2 dbms_ir.getAdviseID( failureList IN VARCHAR2, adviseID OUT NUMBER);

TBD

GETERROR

Return an error from a previous changePriority() or closeFailures() request where the failure list was passed in as a comma separated list of failure-ids dbms_ir.getError( errorId IN NUMBER, failureID OUT NUMBER, errorStr OUT VARCHAR2, done OUT BOOLEAN);

TBD

GETFEASABILITYANDIMPACT

Used by RMAN to get the feasibility and impact of a particular repair on the server dbms_ir.getFeasabilityAndImpact( repairType IN BINARY_INTEGER, parameterList IN VARCHAR2, feasibility OUT BOOLEAN, dataLoss OUT NUMBER, repairTime OUT NUMBER, impact OUT VARCHAR2);

Internal for RMAN

GETFILE

Returns the contents of an IR script file

Overload 1 dbms_ir.getFile( fileID IN NUMBER, contents OUT ir_script_file_type);

TBD

Overload 2 dbms_ir.getFile( fileID IN NUMBER, outBuf OUT VARCHAR2);

TBD

GETLINE

Read a line from a script file dbms_ir.getLine( fileID IN NUMBER, line OUT VARCHAR2, done OUT BOOLEAN);

TBD

OPENSCRIPTFILE

Open a repair script file
Overload 1 dbms_ir.openScriptFile( repairID IN NUMBER, fileID OUT NUMBER);

TBD

Overload 2 dbms_ir.openScriptFile( fileName IN VARCHAR2, fileID OUT NUMBER);

TBD

REEVALUATEOPENFAILURES

Reevaluate the status of open IR failures

Overload 1 dbms_ir.reevaluateOpenFailures( reevaluateCritical IN BOOLEAN DEFAULT TRUE, reevaluateHigh IN BOOLEAN DEFAULT TRUE, reevaluateLow IN BOOLEAN DEFAULT TRUE);

-- create job for reevaluate open failures for Database Repair Advisor

      

      BEGIN

        sys.dbms_scheduler.create_job(

        job_name=>'DRA_REEVALUATE_OPEN_FAILURES',

        job_type=>'STORED_PROCEDURE',

        job_action=>'dbms_ir.reevaluateopenfailures',

        schedule_name=>'MAINTENANCE_WINDOW_GROUP',

        job_class=>'DEFAULT_JOB_CLASS',

        enabled=>TRUE,

        auto_drop=>FALSE,

        comments=>'Reevaluate open failures for DRA');

      EXCEPTION

        WHEN OTHERS THEN

          IF sqlcode = -27477 THEN

            NULL;

          ELSE

            RAISE;

          END IF;

      END;

      /

Overload 2 dbms_ir.reevaluateOpenFailures( reevaluateCritical IN VARCHAR2, reevaluateHigh IN VARCHAR2, reevaluateLow IN VARCHAR2, timeout IN VARCHAR2);

TBD

STARTREPAIROPTION

Called prior to executing a repair option. Verifies that all the failures associated with the repair are still open and then updates the status of the repair to indicate that it is running. It does NOT execute the repair. It will signal an error if it is not ok to start the repair. dbms_ir.startRepairOption(repairID IN NUMBER);

set linesize 121

      col message format a60

      

      desc gv$ir_manual_checklist

      

      SELECT *

      FROM gv$ir_manual_checklist;

      

      desc gv$ir_repair

      

      SELECT repair_id, advise_id, summary, rank, time_detected, estimated_data_loss, repair_script

      FROM gv$ir_repair;

      

      col detailed_description format a80

      

      SELECT repair_id, advise_id detailed_description

      FROM gv$ir_repair;

      

      exec dbms_ir.startRepairOption(22);

UPDATEFEASIBILITYANDIMPACT (new 21c)

Used by RMAN to update the feasibility and impact of a set of repairs (which are in memory during an ADVISE command dbms_ir.updateFeasibilityAndImpact( adviseID IN NUMBER, repairList IN ir_repair_feasibility_list);

RMAN Internal

UPDATEREPAIROPTION

Used by RMAN to update the feasibility and impact of a set of repairs (in sever memory) during an ADVISE command dbms_ir.updateRepairOption( adviseID IN NUMBER, optionIdx IN NUMBER, scriptName IN VARCHAR2, dataLoss IN NUMBER DEFAULT NULL, repairTime IN NUMBER DEFAULT NULL, impact IN VARCHAR2 DEFAULT NULL);

TBD

WRITEFILE

Write multiple lines to a script file dbms_ir.writeFile( fileID IN NUMBER, contents IN ir_script_file_type);

TBD