General Status

select name, log_mode, open_mode 
  from v$database;

Archive Log Usage

This query summarizes Archive Log Switches for today. Change the where clause in this statement to change the time period analyzed.

select trunc(completion_time,'HH'), count(*)
from v$archived_log
where trunc(completion_time) = trunc(sysdate)
group by trunc(completion_time,'HH');

Transactions Per Second

Defined as User Commits + User Rollbacks.

-- Calculate Transactions Per Second
SET SERVEROUTPUT ON
DECLARE
    begindate date;
    enddate date;
    beginval number;
    endval number;
BEGIN

    select sysdate, sum(value) 
    into begindate, beginval
    from v$sysstat 
    where name in ('user commits','user_rollbacks');

    dbms_lock.sleep(5);

    select sysdate, sum(value) 
    into enddate, endval
    from v$sysstat 
    where name in ('user commits','user_rollbacks');

    dbms_output.put_line( (endval-beginval) || 
                          ' transactions');
    
    dbms_output.put_line( ((enddate-begindate) * 86400) || 
                          ' seconds');

    dbms_output.put_line( (endval-beginval) / ((enddate-begindate) * 86400) || 
                          ' transactions per second');

END;
/

Undo Status

alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; 
select usn, 
       state, 
       undoblockstotal "Total", 
       undoblocksdone "Done", 
       undoblockstotal-undoblocksdone "ToDo", 
       decode(cputime,
              0,
              'unknown',
              sysdate+(((undoblockstotal-undoblocksdone) / 
                       (undoblocksdone / cputime)) / 86400)) 
              "Estimated time to complete" 
from v$fast_start_transactions;