Creating a Procedure to turn off Online Archiving - Need help!

Database

Creating a Procedure to turn off Online Archiving - Need help!

Hello Everyone,

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()

BEGIN

DECLARE V_SQL_STMT CHAR(2000) ;

FOR DBCURSOR AS TLC CURSOR FOR

SELECT DISTINCT DATABASENAME
FROM DBC.ArchiveLoggingObjsV

DO

SET V_SQL_STMT = 'LOGGING ONLINE ARCHIVE OFF FOR "' || UPPER(DBCURSOR.DATABASENAME) || '"; ' ;

CALL DBC.SYSEXECSQL(:V_SQL_STMT );

END FOR;

END;

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
3 REPLIES

Re: Creating a Procedure to turn off Online Archiving - Need help!

Ok nevermind! I forgot to use the OVERRIDE command in my syntax! All works now :)
Enthusiast

Re: Creating a Procedure to turn off Online Archiving - Need help!

Hi Thomas,

Can you please specify the syntax of OVERRIDE command
(in the above Stored Proc)?
Enthusiast

Re: Creating a Procedure to turn off Online Archiving - Need help!

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.