| 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 |
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\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 |
