Oracle DBMS_IR
Version 12.1.0.1

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 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 DEFINER
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,
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.0
Security Model Owned by SYS with execute granted to the DBA and SYSBACKUP roles.
Source {ORACLE_HOME}/rdbms/admin/
Subprograms
 
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);
 
UPDATEFEASABILITYANDIMPACT
Used by RMAN to update the feasibility and impact of a set of repairs (in sever memory) during an ADVISE command dbms_ir.updateFeasabilityAndImpact(
adviseID   IN NUMBER,
repairList IN ir_repair_feasibility_list);
TBD
 
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

Related Topics
Automated Diagnostic Repository
DBMS_BACKUP_RESTORE
DBMS_HM
DBMS_RCVMAN
DBMS_SQLDIAG
Packages
RMAN

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved