Oracle startup trigger tips

Answer:  The Oracle database startup trigger is very easy to implement.  Here is an example of a startup trigger.  Note that most DDL commands are not supported in Oracle startup triggers.

Here is the most common of the database startup triggers, one to pin popular PL/SQL packages inside the shared pool:

create or replace trigger
   pin_packs
after startup on database
begin
   -- Application-specific packages
   dbms_shared_pool.keep('MAIN_PACK');
   -- Oracle-supplied software packages
   dbms_shared_pool.keep('DBMS_ALERT');
end;
/

In this example of a startup trigger, we execute a SQL command at database startup time:

create or replace trigger
   CLEAR_BACKUP_FLAG
after startup on database
declare
err_msg varchar2(200);
begin
   execute immediate '<yourSQLcommand>';
   exception when others then
   err_msg := substr(SQLERRM, 1, 30);
   insert into is_backup_error values (err_msg);
end;
/

Here we use a startup trigger to run a job at database startup time:

create or replace trigger
   start_script
after startup on database
begin
dbms_scheduler.run_job('start_service');
end;
/

The Oracle docs offer this example of opening a database in read-only mode, using a startup trigger:

CREATE OR REPLACE TRIGGER
   manage_service
after startup on database
DECLARE
   role VARCHAR(30);
BEGIN
   SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
   IF role = 'PRIMARY' THEN
      DBMS_SERVICE.START_SERVICE('sales_rw');
   ELSE
      DBMS_SERVICE.START_SERVICE('sales_ro');
END IF;
END;