We've been having some problems lately with Online Archiving being kept on after a backup job has failed. Because of this, I've been trying to create a stored procedure that will a) check for any databases that have this feature turned on, and b) dynamically build and execute "LOGGING ONLINE ARCHIVE OFF FOR" statements for each of the databases listed.
I can manually turn on and off Online Archiving with the following statements:
LOGGING ONLINE ARCHIVE ON FOR DBASE1;
LOGGING ONLINE ARCHIVE OFF FOR DBASE1;
Here's what I've got for my stored procedure:
REPLACE PROCEDURE SYSDBA.ONLINE_ARCHIVING_OFF()
DECLARE V_SQL_STMT CHAR(2000) ;
FOR DBCURSOR AS TLC CURSOR FOR
SELECT DISTINCT DATABASENAME
SET V_SQL_STMT = 'LOGGING ONLINE ARCHIVE OFF FOR "' || UPPER(DBCURSOR.DATABASENAME) || '"; ' ;
CALL DBC.SYSEXECSQL(:V_SQL_STMT );
However when I call this procedure, it doesn't turn off online archiving. My questions are:
1) Does the "LOGGING ONLINE ARCHIVE ON FOR" statement work in stored procedures? If so, where am I making my mistake?
2) If this ends up not being able to be done through stored procedures, can anyone suggest a better way to approach this task?
Thank you for your time!
- Thomas Coffing III
Ok nevermind! I forgot to use the OVERRIDE command in my syntax! All works now :)
Can you please specify the syntax of OVERRIDE command
(in the above Stored Proc)?
OVERRIDE is not a command. It is an optional part of the syntax of the LOGGING ONLINE ARCHIVE OFF statement. See the SQL manual for details.