Session altered.

TIME
12-AUG-2019 04:58:58

1 row selected.

SQL>
SQL> -- In the following output the DATABASE_ROLE should be PRIMARY as that is what this script is intended to be run on.
SQL> -- PLATFORM_ID should match the PLATFORM_ID of the standby(s) or conform to the supported options in
SQL> -- Note: 413484.1 Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration
SQL> -- Note: 1085687.1 Data Guard Support for Heterogeneous Primary and Logical Standbys in Same Data Guard Configuration
SQL> -- OPEN_MODE should be READ WRITE.
SQL> -- LOG_MODE should be ARCHIVELOG.
SQL> -- FLASHBACK can be YES (recommended) or NO.
SQL> -- If PROTECTION_LEVEL is different from PROTECTION_MODE then for some reason the mode listed in PROTECTION_MODE experienced a need to downgrade.
SQL> -- Once the error condition has been corrected the PROTECTION_LEVEL should match the PROTECTION_MODE after the next log switch;
SQL>
SQL> SELECT database_role role, name, db_unique_name, platform_id, open_mode, log_mode, flashback_on, protection_mode, protection_level FROM v$database;

ROLE NAME DB_UNIQUE_NAME PLATFORM_ID OPEN_MODE LOG_MODE FLASHBACK_ON PROTECTION_MODE PROTECTION_LEVEL
PRIMARY CBS cbs 2 READ WRITE ARCHIVELOG NO MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

1 row selected.

SQL>
SQL> -- FORCE_LOGGING is not mandatory but is recommended.
SQL> -- REMOTE_ARCHIVE should be ENABLE.
SQL> -- SUPPLEMENTAL_LOG_DATA_PK and SUPPLEMENTAL_LOG_DATA_UI must be enabled if the standby associated with this primary is a logical standby.
SQL> -- During normal operations it is acceptable for SWITCHOVER_STATUS to be SESSIONS ACTIVE or TO STANDBY.
SQL> -- DG_BROKER can be ENABLED (recommended) or DISABLED.;
SQL>
SQL> column force_logging format a13 tru
SQL> column remote_archive format a14 tru
SQL> column supplemental_log_data_pk format a24 tru
SQL> column supplemental_log_data_ui format a24 tru
SQL> column dataguard_broker format a16 tru
SQL>
SQL> SELECT force_logging, remote_archive, supplemental_log_data_pk, supplemental_log_data_ui, switchover_status, dataguard_broker FROM v$database;

FORCE_LOGGING REMOTE_ARCHIVE SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI SWITCHOVER_STATUS DATAGUARD_BROKER
YES ENABLED NO NO TO STANDBY DISABLED

1 row selected.

SQL>
SQL> -- The following query gives us information about catpatch. From this we can tell if the catalog version doesn''t match the image version it was started with.
SQL>
SQL> column version format a10 tru
SQL>
SQL> SELECT version, modified, status FROM dba_registry WHERE comp_id = 'CATPROC';

VERSION MODIFIED STATUS
12.1.0.2.0 07-AUG-2019 08:15:44 VALID

1 row selected.

SQL>
SQL> -- Check how many threads are enabled and started for this database. If the number of instances below does not match then not all instances are up.
SQL>
SQL> SELECT thread#, instance, status FROM v$thread;

THREAD# INSTANCE STATUS
1 cbs1 OPEN
2 cbs2 OPEN

2 rows selected.

SQL>
SQL> -- The number of instances returned below is the number currently running. If it does not match the number returned in Threads above then not all instances are up.
SQL> -- VERSION should match the version from CATPROC above.
SQL> -- ARCHIVER can be (STOPPED | STARTED | FAILED). FAILED means that the archiver failed to archive a log last time, but will try again within 5 minutes.
SQL> -- LOG_SWITCH_WAIT the ARCHIVE LOG/CLEAR LOG/CHECKPOINT event log switching is waiting for.
SQL> -- Note that if ALTER SYSTEM SWITCH LOGFILE is hung, but there is room in the current online redo log, then the value is NULL.
SQL>
SQL> column host_name format a32 wrap
SQL>
SQL> SELECT thread#, instance_name, host_name, version, archiver, log_switch_wait FROM gv$instance ORDER BY thread#;

THREAD# INSTANCE_NAME HOST_NAME VERSION ARCHIVE LOG_SWITCH_WAIT
1 cbs1 cbsdb1 12.1.0.2.0 STARTED  
2 cbs2 cbsdb2 12.1.0.2.0 STARTED  

2 rows selected.

SQL>
SQL> -- Check how often logs are switching. Log switches should not regularly be occuring in < 20 mins.
SQL> -- Excessive log switching is a performance overhead. Whilst rapid log switching is not in itself a Data Guard issue it can affect Data guard.
SQL> -- It may also indicate a problem with log shipping. Use redo log size = 4GB or redo log size >= peak redo rate x 20 minutes.
SQL>
SQL> SELECT fs.log_switches_under_20_mins, ss.log_switches_over_20_mins FROM (SELECT SUM(COUNT (ROUND((b.first_time - a.first_time) * 1440) )) "LOG_SWITCHES_UNDER_20_MINS" FROM v$archived_log a, v$archived_log b WHERE a.sequence# + 1 = b.sequence# AND a.dest_id = 1 AND a.thread# = b.thread# AND a.dest_id = b.dest_id AND a.dest_id = (SELECT MIN(dest_id) FROM gv$archive_dest WHERE target='PRIMARY' AND destination IS NOT NULL) AND ROUND((b.first_time - a.first_time) * 1440) < 20 GROUP BY ROUND((b.first_time - a.first_time) * 1440)) fs, (SELECT SUM(COUNT (ROUND((b.first_time - a.first_time) * 1440) )) "LOG_SWITCHES_OVER_20_MINS" FROM v$archived_log a, v$archived_log b WHERE a.sequence# + 1 = b.sequence# AND a.dest_id = 1 AND a.thread# = b.thread# AND a.dest_id = b.dest_id AND a.dest_id = (SELECT MIN(dest_id) FROM gv$archive_dest WHERE target='PRIMARY' AND destination IS NOT NULL) AND ROUND((b.first_time - a.first_time) * 1440) > 19 GROUP BY ROUND((b.first_time - a.first_time) * 1440)) ss;

LOG_SWITCHES_UNDER_20_MINS LOG_SWITCHES_OVER_20_MINS
143 39

1 row selected.

SQL>
SQL> column minutes format a12
SQL>
SQL> SELECT (CASE WHEN bucket = 1 THEN '<= ' || TO_CHAR(bucket * 5) WHEN (bucket >1 AND bucket < 9) THEN TO_CHAR(bucket * 5 - 4) || ' TO ' || TO_CHAR(bucket * 5) WHEN bucket > 8 THEN '>= ' || TO_CHAR(bucket * 5 - 4) END) "MINUTES", switches "LOG_SWITCHES" FROM (SELECT bucket , COUNT(b.bucket) SWITCHES FROM (SELECT WIDTH_BUCKET(ROUND((b.first_time - a.first_time) * 1440), 0, 40, 8) bucket FROM v$archived_log a, v$archived_log b WHERE a.sequence# + 1 = b.sequence# AND a.dest_id = b.dest_id AND a.thread# = b.thread# AND a.dest_id = (SELECT MIN(dest_id) FROM gv$archive_dest WHERE target = 'PRIMARY' AND destination IS NOT NULL)) b GROUP BY bucket ORDER BY bucket);

MINUTES LOG_SWITCHES
<= 5 118
6 TO 10 8
11 TO 15 12
16 TO 20 5
21 TO 25 2
26 TO 30 1
31 TO 35 3
36 TO 40 1
>= 41 32

9 rows selected.

SQL>
SQL> -- Check the number and size of online redo logs on each thread.
SQL>
SQL> SELECT thread#, group#, sequence#, bytes, archived ,status FROM v$log ORDER BY thread#, group#;

THREAD# GROUP# SEQUENCE# BYTES ARC STATUS
1 5 112 2147483648 YES INACTIVE
1 6 113 2147483648 YES ACTIVE
1 7 114 2147483648 NO CURRENT
2 8 76 2147483648 YES INACTIVE
2 9 77 2147483648 YES INACTIVE
2 10 78 2147483648 NO CURRENT

6 rows selected.

SQL>
SQL> -- The following query is run to see if standby redo logs have been created in preparation for switchover.
SQL> -- The standby redo logs should be the same size as the online redo logs.
There should be (( # of online logs per thread + 1) * # of threads) standby redo logs.
SQL> -- A value of 0 for the thread# means the log has never been allocated.
SQL>
SQL> SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;
no rows selected
SQL>
SQL> -- This query produces a list of defined archive destinations. It shows if they are enabled, what process is servicing that destination,
SQL> -- if the destination is local or remote.
SQL>
SQL> column destination format a35 wrap
SQL> column process format a7
SQL> column ID format 99
SQL> column mid format 99
SQL>
SQL> SELECT thread#, dest_id, destination, target, schedule, process FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

THREAD# DEST_ID DESTINATION TARGET SCHEDULE PROCESS
1 1 +DATA/cbs/archive PRIMARY ACTIVE ARCH
1 3 GGCBS STANDBY ACTIVE LGWR
2 1 +DATA/cbs/archive PRIMARY ACTIVE ARCH
2 3 GGCBS STANDBY ACTIVE LGWR

4 rows selected.

SQL>
SQL> -- This select will give further detail on the destinations as to what options have been set.
SQL> -- Register indicates whether or not the archived redo log is registered in the remote destination control fileOptions.
SQL>
SQL> set numwidth 8
SQL> column archiver format a8
SQL> column affirm format a6
SQL> column error format a55 wrap
SQL> column register format a8
SQL>
SQL> SELECT thread#, dest_id, gvad.archiver, transmit_mode, affirm, async_blocks, net_timeout, max_failure, delay_mins, reopen_secs reopen, register, binding FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

THREAD# DEST_ID ARCHIVER TRANSMIT_MOD AFFIRM ASYNC_BLOCKS NET_TIMEOUT MAX_FAILURE DELAY_MINS REOPEN REGISTER BINDING
1 1 ARCH SYNCHRONOUS NO 0 0 0 0 300 YES OPTIONAL
1 3 LGWR ASYNCHRONOUS NO 61440 30 0 0 10 NO OPTIONAL
2 1 ARCH SYNCHRONOUS NO 0 0 0 0 300 YES OPTIONAL
2 3 LGWR ASYNCHRONOUS NO 61440 30 0 0 10 NO OPTIONAL

4 rows selected.

SQL>
SQL> -- The following select will show any errors that occured the last time an attempt to archive to the destination was attempted.
SQL> -- If ERROR is blank and status is VALID then the archive completed correctly.
SQL>
SQL> SELECT thread#, dest_id, gvad.status, error, fail_sequence FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

THREAD# DEST_ID STATUS ERROR FAIL_SEQUENCE
1 1 VALID   0
1 3 VALID   0
2 1 VALID   0
2 3 VALID   0

4 rows selected.

SQL>
SQL> -- The query below will determine if any error conditions have been reached by querying the v$dataguard_status view (view only available in 9.2.0 and above).
SQL>
SQL> column message format a80
SQL>
SQL> SELECT gvi.thread#, timestamp, message FROM gv$dataguard_status gvds, gv$instance gvi WHERE gvds.inst_id = gvi.inst_id AND severity in ('Error','Fatal') ORDER BY timestamp, thread#;

THREAD# TIMESTAMP MESSAGE
1 11-AUG-2019 18:33:48 Error 3135 for archive log file 5 to 'GGCBS'
2 11-AUG-2019 18:33:48 Error 3135 for archive log file 8 to 'GGCBS'
1 11-AUG-2019 18:53:48 Error 3135 for archive log file 5 to 'GGCBS'
2 11-AUG-2019 18:53:48 Error 3135 for archive log file 10 to 'GGCBS'
1 11-AUG-2019 19:06:16 Error 3135 for archive log file 5 to 'GGCBS'
2 11-AUG-2019 19:06:16 Error 3135 for archive log file 9 to 'GGCBS'

6 rows selected.

SQL>
SQL> -- Query v$managed_standby to see the status of processes involved in the shipping redo on this system.
SQL> -- Does not include processes needed to apply redo.
SQL>
SQL> SELECT inst_id, thread#, process, pid, status, client_process, client_pid, sequence#, block#, active_agents, known_agents FROM gv$managed_standby ORDER BY thread#, pid;

INST_ID THREAD# PROCESS PID STATUS CLIENT_P CLIENT_PID SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
1 1 ARCH 20243 CLOSING ARCH 20243 110 81921 0 0
1 1 ARCH 20245 CLOSING ARCH 20245 112 1 0 0
1 1 ARCH 20247 CLOSING ARCH 20247 98 1 0 0
1 1 ARCH 20250 CLOSING ARCH 20250 113 49152 0 0
1 1 LNS 20256 WRITING LNS 20256 114 3388 0 0
2 2 ARCH 24235 CLOSING ARCH 24235 74 1 0 0
2 2 ARCH 24237 CLOSING ARCH 24237 75 1 0 0
2 2 ARCH 24239 CLOSING ARCH 24239 76 1 0 0
2 2 LNS 24241 WRITING LNS 24241 78 34110 0 0
2 2 ARCH 24243 CLOSING ARCH 24243 75 1 0 0

10 rows selected.

SQL>
SQL> -- The following query will determine the current sequence number and the last sequence archived.
SQL> -- If you are remotely archiving using the LGWR process then the archived sequence should be one higher than the current sequence.
SQL> -- If remotely archiving using the ARCH process then the archived sequence should be equal to the current sequence.
SQL> -- The applied sequence information is updated at log switch time.
SQL> -- The "Last Applied" value should be checked with the actual last log applied at the standby, only the standby is guaranteed to be correct.
SQL>
SQL> SELECT cu.thread#, cu.dest_id, la.lastarchived "Last Archived", cu.currentsequence "Current Sequence", appl.lastapplied "Last Applied" FROM (select gvi.thread#, gvd.dest_id, MAX(gvd.log_sequence) currentsequence FROM gv$archive_dest gvd, gv$instance gvi WHERE gvd.status = 'VALID' AND gvi.inst_id = gvd.inst_id GROUP BY thread#, dest_id) cu, (SELECT thread#, dest_id, MAX(sequence#) lastarchived FROM gv$archived_log WHERE resetlogs_change# = (SELECT resetlogs_change# FROM v$database) AND archived = 'YES' GROUP BY thread#, dest_id) la, (SELECT thread#, dest_id, MAX(sequence#) lastapplied FROM gv$archived_log WHERE resetlogs_change# = (SELECT resetlogs_change# FROM v$database) AND applied = 'YES' GROUP BY thread#, dest_id) appl WHERE cu.thread# = la.thread# AND cu.thread# = appl.thread# AND cu.dest_id = la.dest_id AND cu.dest_id = appl.dest_id ORDER BY 1, 2;

THREAD# DEST_ID Last Archived Current Sequence Last Applied
1 3 113 114 105
2 3 77 78 71

2 rows selected.

SQL>
SQL> -- The following select will attempt to gather as much information as possible from the standby.
SQL> -- Standby redo logs are not supported with Logical Standby until Version 10.1.
SQL> -- The ARCHIVED_SEQUENCE# from a logical standby is the sequence# created by the apply, not the sequence# sent from the primary.
SQL>
SQL> set numwidth 8
SQL> column dest_id format 99
SQL> column Active format 99
SQL>
SQL> SELECT dest_id, database_mode, recovery_mode, protection_mode, standby_logfile_count, standby_logfile_active FROM v$archive_dest_status WHERE destination IS NOT NULL;

DEST_ID DATABASE_MODE RECOVERY_MODE PROTECTION_MODE STANDBY_LOGFILE_COUNT STANDBY_LOGFILE_ACTIVE
1 OPEN IDLE MAXIMUM PERFORMANCE 0 0
3 OPEN IDLE MAXIMUM PERFORMANCE 8 0

2 rows selected.

SQL>
SQL> -- Non-default init parameters. For a RAC DB Thread# = * means the value is the same for all threads (SID=*)
SQL> -- Threads with different values are shown with their individual thread# and values.
SQL>
SQL> column num noprint
SQL>
SQL> SELECT num, '*' "THREAD#", name, value FROM v$PARAMETER WHERE NUM IN (SELECT num FROM v$parameter WHERE (isdefault = 'FALSE' OR ismodified <> 'FALSE') AND name NOT LIKE 'nls%'
2 MINUS
3 SELECT num FROM gv$parameter gvp, gv$instance gvi WHERE num IN (SELECT DISTINCT gvpa.num FROM gv$parameter gvpa, gv$parameter gvpb WHERE gvpa.num = gvpb.num AND gvpa.value <> gvpb.value AND (gvpa.isdefault = 'FALSE' OR gvpa.ismodified <> 'FALSE') AND gvpa.name NOT LIKE 'nls%') AND gvi.inst_id = gvp.inst_id AND (gvp.isdefault = 'FALSE' OR gvp.ismodified <> 'FALSE') AND gvp.name NOT LIKE 'nls%')
4 UNION
5 SELECT num, TO_CHAR(thread#) "THREAD#", name, value FROM gv$parameter gvp, gv$instance gvi WHERE num IN (SELECT DISTINCT gvpa.num FROM gv$parameter gvpa, gv$parameter gvpb WHERE gvpa.num = gvpb.num AND gvpa.value <> gvpb.value AND (gvpa.isdefault = 'FALSE' OR gvpa.ismodified <> 'FALSE') AND gvp.name NOT LIKE 'nls%') AND gvi.inst_id = gvp.inst_id AND (gvp.isdefault = 'FALSE' OR gvp.ismodified <> 'FALSE') AND gvp.name NOT LIKE 'nls%' ORDER BY 1, 2;

THREAD# NAME VALUE
* processes 1000
* sga_target 82678120448
* control_files +DATA/cbs/control01.ctl, +DATA/cbs/control02.ctl
* db_block_size 16384
* compatible 12.1.0.2.0
* log_archive_dest_1 LOCATION=+DATA/cbs/archive
* log_archive_dest_3 SERVICE=GGCBS ASYNC NOREGISTER VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) REOPEN=10 DB_UNIQUE_NAME=GGCBS
* log_archive_dest_state_3 ENABLE
* log_archive_config DG_CONFIG=(cbs,ggcbs)
* cluster_database TRUE
1 thread 1
2 thread 2
* enable_goldengate_replication TRUE
1 undo_tablespace UNDOTBS1
2 undo_tablespace UNDOTBS2
* undo_retention 3600
1 instance_number 1
2 instance_number 2
* sec_case_sensitive_logon FALSE
* remote_login_passwordfile EXCLUSIVE
* db_domain  
* dispatchers (PROTOCOL=TCP) (SERVICE=cbsXDB)
1 local_listener (ADDRESS=(PROTOCOL=TCP)(HOST=10.215.227.9)(PORT=1521))
2 local_listener (ADDRESS=(PROTOCOL=TCP)(HOST=10.215.227.10)(PORT=1521))
* remote_listener cbsdb-scan:1521
* listener_networks  
* audit_file_dest /u01/app/ora12c/admin/cbs/adump
* audit_trail DB
* db_name cbs
* open_cursors 300
* pga_aggregate_target 27483176960
* diagnostic_dest /u01/app/ora12c

32 rows selected.

SQL>
SQL> spool off