database startup trigger branches depending on resetlogs

This logon TRIGGER branches depending ON whether the CURRENT database OPEN was normal OR reset logs.

This port IS IN response TO "Know if database has been activated by failover or switchover"
BY Franck Pachot
https://community.oracle.com/message/13385190

CREATE OR REPLACE TRIGGER dbstartup_trig
after startup ON database
DECLARE
        l_resetlogs_startup_time        dba_hist_database_instance.startup_time%TYPE;
        l_startup_time                  dba_hist_database_instance.startup_time%TYPE;
        l_first_change# NUMBER;
        l_first_time    DATE;
        l_sequence#     NUMBER;
        l_msg clob;

        FUNCTION ts ( p_ts IN TIMESTAMP ) RETURN VARCHAR2
        IS
        BEGIN
                RETURN TO_CHAR( p_ts ,'yyyy-mm-dd hh24:mi:ss.ff3' );
        END ts;
BEGIN
        alertlog_pkg.printf( 'systimestamp=%s', ts( SYSTIMESTAMP ) );

        SELECT MIN(startup_time), MAX(startup_time)
        INTO l_resetlogs_startup_time, l_startup_time
        FROM dba_hist_database_instance
        WHERE startup_time > ( SELECT resetlogs_time FROM v$database );

        alertlog_pkg.printf( 'l_resetlogs_startup_time=%s', ts( l_resetlogs_startup_time ) );
        alertlog_pkg.printf( 'startup_time=%s', ts( l_startup_time ) );

        IF l_startup_time = l_resetlogs_startup_time
        THEN
                alertlog_pkg.printf( 'started up with resetlogs' );
        ELSE
                alertlog_pkg.printf( 'started up without resetlogs' );
        END IF;

        SELECT MAX( sequence# ), MAX ( first_change# ), MAX ( first_time )
        INTO l_sequence#, l_first_change#, l_first_time
        FROM v$log;

        alertlog_pkg.printf( 'sequence#=%s', l_sequence# );
        alertlog_pkg.printf( 'first_change#=%s', l_first_change# );
        alertlog_pkg.printf( 'first_time=%s', ts( l_first_time ) );


END dbstartup_trig;
/

Normal OPEN AND resetlogs OPEN:


SQL*Plus: RELEASE 12.1.0.2.0 Production ON Mon Nov 2 22:04:39 2015

Copyright (c) 1982, 2014, Oracle.  ALL rights reserved.


Connected TO:
Oracle Database 12c Enterprise Edition RELEASE 12.1.0.2.0 - 64bit Production
WITH the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
AND REAL Application Testing options

SQL> shutdown IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 6190792704 bytes
Fixed Size                  2938064 bytes
Variable Size            1191185200 bytes
Database Buffers         4982833152 bytes
Redo Buffers               13836288 bytes
Database mounted.
Database opened.
SQL> @ flashback.db.TO.rp.SQL

no rows selected

flashback database IS enabled

no rows selected

a guaranteed restore point EXISTS

NAME
--------------------------------------------------------------------------------
RP_20151102_2100

RP_20151102_2100
ORACLE instance started.

Total System Global Area 6190792704 bytes
Fixed Size                  2938064 bytes
Variable Size            1191185200 bytes
Database Buffers         4982833152 bytes
Redo Buffers               13836288 bytes
Database mounted.
old   1: flashback database TO restore point &&rpname
NEW   1: flashback database TO restore point RP_20151102_2100

Flashback complete.


Database altered.

TRIGGER output after normal OPEN:

Mon Nov 02 22:06:24 2015
SYSTIMESTAMP=2015-11-02 22:06:23.553
l_resetlogs_startup_time=2015-11-02 21:02:01.000
startup_time=2015-11-02 22:05:56.000
started up without resetlogs
sequence#=2
first_change#=33762826
first_time=2015-11-02 22:04:33.000

TRIGGER output after OPEN resetlogs:

Mon Nov 02 22:08:08 2015
SYSTIMESTAMP=2015-11-02 22:08:08.282
l_resetlogs_startup_time=2015-11-02 22:07:21.000
startup_time=2015-11-02 22:07:21.000
started up WITH resetlogs
sequence#=1
first_change#=33753627
first_time=2015-11-02 22:07:08.000