/*
-- Ever managed to put a database in archivelog mode and then forget to put log_archive_start in the init.ora?
-- So you have to bounce the server (or it crashes) and upon restart, after the online logs have been filled,
-- it hangs. The users complain, your boss isn't happy and you've forgotten completely all about it...
--
-- We've all done it.
--
-- Notice how in 10g log_archive_start just isn't needed anymore? If the database is in archivelog mode it will
-- archive the logs automatically.
--
-- And who ever used manual archiving anyway?
--
-- This trigger starts automatic archiving as soon as the database opens, providing the database is in archivelog
-- mode and log_archive_start is still set to false.
-- So giving a little bit of 10g functionality as far back as 8i and preventing those self inflicted embarrassing
-- moments when it dawns on you that you didn't set it in the init.ora X months ago.
--
-- What is the RAISE_APPLICATION_ERROR for? Well, all exceptions generated by database level triggers end up in the
-- alert log, so this is just a convenient way of getting the error logged. Since DCL is not dependant on COMMIT
-- or ROLLBACK, the exception does not stop the ALTER SYSTEM from succeeding.
-- And yes, I could have used DBMS_SYSTEM.KSDWRT (but thats undocumented) or UTL_FILE (lots more code).
--
-- This code has been tested and works as intended on Oracle 8174 and 9205 EE.
-- (this code is not needed on a 10g or greater system)
--
-- Get this tip and others, along with useful PL/SQL utilities at
-- www.chrispoole.co.uk
--
*/





CREATE OR REPLACE TRIGGER ensure_archiving
AFTER STARTUP ON DATABASE
DECLARE
lm v$database.log_mode%TYPE;
las v$parameter.value%TYPE;
BEGIN

SELECT log_mode INTO lm FROM v$database;
SELECT value INTO las FROM v$parameter WHERE name = 'log_archive_start';

IF (lm='ARCHIVELOG') AND (las='FALSE')
THEN

EXECUTE IMMEDIATE 'alter system archive log start';
RAISE_APPLICATION_ERROR(-20000, 'Database in ARCHIVELOG mode but automatic archiving not enabled!');

END IF;

END;
/