CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_IR
Version 11.1
 
General Information

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

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

Dependencies
dbms_backup_restore gv$ir_manual_checklist
dbms_sys_error gv$ir_repair
gv$hm_recommendation plitblm
Constants
Name Data Type Value
IR_FAILURE_CRITICAL BINARY_INTEGER 1
IR_FAILURE_HIGH BINARY_INTEGER 2
IR_FAILURE_LOW BINARY_INTEGER 3

Exceptions
Number Name
-51190 internal_error
-51191 too_many_opens_error
-51192 not_open_error

Defined 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;
Object Privileges Execute is granted to the DBA role
 
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. Will cause the repair option information to be written to disk. dbms_ir.adviseDone(
adviseID         IN  NUMBER,
generatedRepairs OUT ir_repair_option_list);
Internal for RMAN
 
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. dbms_ir.closeFailures(failureList IN VARCHAR2, errorID OUT NUMBER);
TBD
 
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

E
xecute 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\ORADATA\ORABASE\CONTROL03.CTL') THEN
    dbms_output.put_line('True');
  ELSE
    dbms_output.put_line('False');
  END IF;
END;
/
 
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
 
REEVALUATEOPENFAILURES (new 11.1.0.7 overload)

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);
 
UPDATEFEASABILITYANDUPDATE
Used by RMAN to update the feasibility and impact of a set of repairs (which are in the memory of the server) during an ADVISE command. dbms_ir.updateFeasabilityAndImpact(
adviseID   IN NUMBER,
repairList IN ir_repair_feasibility_list );
TBD
 
UPDATEREPAIROPTION

Update an ADVISE repair option with its script name, data loss, repair time, and impact.
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
 
Script File Procedures
 
ADDLINE
Write a line to a script file dbms_ir.addLine(fileID IN NUMBER, line IN VARCHAR2);
See createScriptFile demo
 
CLOSESCRIPTFILE
Close a repair script file dbms_ir.closeScriptFile(fileID IN NUMBER);
See createScriptFile demo
 
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;
/
 
EXECSQLSCRIPT
Execute the specified sql script dbms_ir.execSQLScript(filename IN VARCHAR2);
TBD
 
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
 
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
RMAN
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [66 users online]    © 2010 psoug.org