/*
|| SQL:  	MonitorLSBY.sql
|| Purpose: Offers several different queries for monitoring Logical 
||          Standby databases in an Oracle 11gR1 environment.
|| Version: 11.1.0.6.0
|| Author:  Jim Czuprynski (Fujitsu America)
*/ 

/*
|| Determining unsupported tables
*/

-----
-- View:    DBA_LOGSTDBY_UNSUPPORTED_TABLE
-- Purpose: Lists tables unsupported for Logical Standby
-----
TTITLE 'Tables Unsupported for Logical Standby|(from DBA_LOGSTDBY_UNSUPPORTED_TABLE)'
COL owner       FORMAT A20   HEADING 'Schema Owner'
COL table_name  FORMAT A30   HEADING 'Table Name'
SELECT 
     owner
    ,table_name
  FROM dba_logstdby_unsupported_table
 ORDER BY owner, table_name
;
TTITLE OFF

-----
-- View:    DBA_LOGSTDBY_UNSUPPORTED
-- Purpose: Lists tables unsupported for Logical Standby because at least one column 
--          has an unsupported datatype
-----
TTITLE 'Tables Unsupported for Logical Standby|Because of Columns With Unsupported Datatypes|(from DBA_LOGSTDBY_UNSUPPORTED)'
COL owner           FORMAT A12   HEADING 'Schema Owner'
COL table_name      FORMAT A24   HEADING 'Table Name'
COL column_name     FORMAT A30   HEADING 'Column Name'
COL data_type       FORMAT A20   HEADING 'Data Type' WRAP
COL attributes      FORMAT A30   HEADING 'Attributes' WRAP
SELECT 
     owner
    ,table_name
    ,column_name
    ,data_type
    ,attributes
  FROM dba_logstdby_unsupported
 ORDER BY owner, table_name, column_name
;
TTITLE OFF

-----
-- View:    DBA_LOGSTDBY_NOT_UNIQUE
-- Purpose: Lists tables unsupported for Logical Standby because they don't have 
--          unique row identifiers (e.g. primary / unique key)
-----
TTITLE 'Tables Unsupported for Logical Standby|Because of Missing Unique Row Identifiers|(from DBA_LOGSTDBY_NOT_UNIQUE)'
COL owner           FORMAT A20   HEADING 'Schema Owner'
COL table_name      FORMAT A30   HEADING 'Table Name'
COL bad_column      FORMAT A04   HEADING 'Bad|Col'
SELECT
     owner
    ,table_name
    ,bad_column 
  FROM dba_logstdby_not_unique
 WHERE TABLE_NAME NOT IN (SELECT DISTINCT table_name 
                            FROM dba_logstdby_unsupported)
;
TTITLE OFF

/*
|| Logical Standby Transaction Exclusion
*/

-----
-- View:    DBA_LOGSTDBY_SKIP
-- Purpose: Displays tables that will be skipped by Log Apply Services
-----
TTITLE 'Tables To Be Skipped During Log Apply|(from DBA_LOGSTDBY_SKIP)'
COL owner           FORMAT A20   HEADING 'Schema Owner'
COL name            FORMAT A30   HEADING 'Table Name'
COL error           FORMAT A05   HEADING 'Error'
COL statement_opt   FORMAT A30   HEADING 'Statement Type|To Be Skipped'
COL use_like        FORMAT A05   HEADING 'SQL|Wild|Card|Srch'
COL esc             FORMAT A04   HEADING 'ESC|Chr'
COL proc            FORMAT A30   HEADING 'Procedure Name|For LSBY Skip'
SELECT 
     owner
    ,name
    ,error
    ,statement_opt
    ,use_like
    ,esc
    ,proc
  FROM dba_logstdby_skip
 ORDER BY 1, 2
;
TTITLE OFF

-----
-- View:    DBA_LOGSTDBY_SKIP_TRANSACTION
-- Purpose: Displays which transactions will be skipped because Logical Standby 
--          filters are in place
-----
TTITLE 'Transactions Skipped Due To Logical Standby Filters In Place|(from DBA_LOGSTDBY_SKIP_TRANSACTION)'
COL xidusn  FORMAT 999999999    HEADING 'XID|Undo|Segment'
COL xidslt  FORMAT 999999999    HEADING 'XID|Slot|Number'
COL xidsqn  FORMAT 999999999    HEADING 'XID|Sequence|Number'
SELECT 
     xidusn
    ,xidslt
    ,xidsqn
  FROM dba_logstdby_skip_transaction
 ORDER BY 1, 2
;
TTITLE OFF

-----
-- View:    DBA_LOGSTDBY_LOG
-- Purpose: Shows redo logs registered for use of Logical Standby redo application
-----
TTITLE 'Redo Logs Registered For Logical Standby Redo Application|(from DBA_LOGSTDBY_LOG)'
COL sequence#   FORMAT 999999999    HEADING 'Sequence #'
COL first_dtm   FORMAT A11          HEADING 'First|Recorded|Change' WRAP
COL next_dtm    FORMAT A11          HEADING 'Next|Recorded|Change' WRAP
COL arl_reg_dtm FORMAT A11          HEADING 'Archive Redo Log|Registered On' WRAP
COL applied     FORMAT A08          HEADING 'Applied?'
COL logfilename FORMAT A50          HEADING 'Archived Redo Log File Name'
SELECT 
     sequence#
    ,TO_DATE(first_time,'yyyy-mm-dd hh24:mi:ss') first_dtm
    ,TO_DATE(next_time,'yyyy-mm-dd hh24:mi:ss') next_dtm
    ,TO_DATE(timestamp,'yyyy-mm-dd hh24:mi:ss') arl_reg_dtm
    ,applied
    ,LTRIM(file_name,50) logfilename
  FROM dba_logstdby_log
;
TTITLE OFF

/*
|| Logical Standby Transaction Processing
*/

-----
-- View:    V$LOGSTDBY_PROCESS
-- Purpose: Shows status of Log Apply Services background processes
-----
TTITLE 'Logical Standby Log Apply Service Processes|(from V$LOGSTDBY_PROCESS)'
COL sid             FORMAT 999999       HEADING 'SessID'
COL serial#         FORMAT 9999999      HEADING 'Serial#'
COL logstdby_id     FORMAT 9999999      HEADING 'Parallel|Query|Slave ID'
COL type            FORMAT A12          HEADING 'SQL Apply|Process Type' WRAP
COL status_code     FORMAT 9999999      HEADING 'Status|Code'
COL status          FORMAT A40          HEADING 'Status Description' WRAP
COL high_scn        FORMAT 999999999    HEADING 'Highest|Redo/|LCR SCN'
SELECT 
     sid
    ,serial#
    ,logstdby_id
    ,spid
    ,type
    ,status_code
    ,high_scn
    ,status
  FROM v$logstdby_process
 ORDER BY type
;
TTITLE OFF

-----
-- View:    V$LOGSTDBY_PROGRESS
-- Purpose: Determines which SCNs have already been applied via Logical Apply Services 
-----
TTITLE 'Logical Apply Services Progress|(from V$LOGSTDBY_PROGRESS)'
COL applied_scn     FORMAT 999999999    HEADING 'Redo|Applied|Thru|SCN'
COL applied_dtm     FORMAT A12          HEADING 'Redo Applied|Thru Time'
COL restart_scn     FORMAT 999999999    HEADING 'Apply|Restart|SCN'
COL restart_dtm     FORMAT A12          HEADING 'Apply|Restart|Time'
COL latest_scn      FORMAT 999999999    HEADING 'Latest|Redo|SCN'
COL latest_dtm      FORMAT A12          HEADING 'Latest Redo|Applied At'
COL mining_scn      FORMAT 999999999    HEADING 'Latest|Redo|Built|SCN'
COL mining_dtm      FORMAT A12          HEADING 'Latest Redo|Built Thru'
SELECT 
     applied_scn
    ,TO_CHAR(applied_time,'yyyy-mm-dd hh24:mi:ss') applied_dtm
    ,restart_scn
    ,TO_CHAR(restart_time,'yyyy-mm-dd hh24:mi:ss') restart_dtm
    ,latest_scn
    ,TO_CHAR(latest_time,'yyyy-mm-dd hh24:mi:ss') latest_dtm
    ,mining_scn
    ,TO_CHAR(mining_time,'yyyy-mm-dd hh24:mi:ss') mining_dtm
  FROM v$logstdby_progress
;
TTITLE OFF

-----
-- View:    V$LOGSTDBY_STATE
-- Purpose: Summarizes Logical Standby Database "running state"
-----
TTITLE 'Logical Standby Database: Running State|(from V$LOGSTDBY_STATE)'
COL primary_dbid    FORMAT 999999999999 HEADING 'Primary DB ID'
COL session_id      FORMAT 999999       HEADING 'SessID'
COL realtime_apply  FORMAT A24          HEADING 'Running In|Real-Time|Apply Mode?'
COL state           FORMAT A30          HEADING 'SQL Apply State' WRAP
SELECT 
     primary_dbid
    ,session_id
    ,realtime_apply
    ,state
  FROM v$logstdby_state
;
TTITLE OFF

-----
-- View:    V$LOGSTDBY_STATS
-- Purpose: Displays Logical Standby statistics and attributes
-----
TTITLE 'Logical Standby Database Statistics|(from V$LOGSTDBY_STATS)'
COL name            FORMAT A40          HEADING 'Statistic Name'
COL value           FORMAT A25          HEADING 'Value'
SELECT 
     name
    ,value
  FROM v$logstdby_stats
 ORDER BY name
;
TTITLE OFF

-----
-- View:    V$LOGSTDBY_TRANSACTION
-- Purpose: Lists all Logical Standby transactions that are currently in progress
-----
TTITLE 'Logical Standby Database Transactions Current Being Processed|(from V$LOGSTDBY_TRANSACTION)'
COL sid                 FORMAT 999999       HEADING 'SessID'
COL serial#             FORMAT 9999999      HEADING 'Serial#'
COL type                FORMAT A12          HEADING 'SQL Apply|Process Type' WRAP
COL mining_status       FORMAT A12          HEADING 'Mining|Status'
COL apply_status        FORMAT A12          HEADING 'Apply|Status'
COL primary_xid         FORMAT A20          HEADING 'SQL Apply|Start XID'
COL primary_start_scn   FORMAT A12          HEADING 'SQL Apply|Start|SCN'
COL primary_start_dtm   FORMAT A11          HEADING 'SQL Apply|Start Time' WRAP
SELECT 
     sid
    ,serial#
    ,type
    ,mining_status
    ,apply_status
    ,primary_xid
    ,primary_start_scn
    ,TO_DATE(primary_start_time,'yyyy-mm-dd hh24:mi:ss') primary_start_dtm
  FROM v$logstdby_transaction
 ORDER BY sid, serial#
;
TTITLE OFF

/*
|| Scheduled Tasks Running on Logical Standby Databases
*/

-----
-- View:    DBA_SCHEDULER_JOB_ROLES
-- Purpose: Shows which Scheduler Job objects run +only+ on a Logical Standby database
-----
TTITLE 'DBMS_SCHEDULER Jobs Scheduled on Logical Standby Databases|(from DBA_SCHEDULER_JOB_ROLES)'
COL owner           FORMAT A20   HEADING 'Job Owner'
COL job_name        FORMAT A30   HEADING 'Job Name'
COL database_role   FORMAT A12   HEADING 'Database|Role'
SELECT
     owner
    ,job_name
    ,database_role
  FROM dba_scheduler_job_roles
 WHERE database_role = 'STANDBY'
 ORDER BY 1, 2
;
TTITLE OFF