Oracle Dataguard Snapshot Standby Testing

 

This is something every dba will know but still documenting for handy steps .

Automated 

*********

To snapshot standby -

alter database recover managed standby database cancel;

alter database convert to snapshot standby;

SQL> select CURRENT_SCN, SWITCHOVER_STATUS, DATABASE_ROLE, open_mode from v$database;

CURRENT_SCN SWITCHOVER_STATUS DATABASE_ROLE   OPEN_MODE

----------- -------------------- ---------------- --------------------

  0 NOT ALLOWED SNAPSHOT STANDBY MOUNTED

To physical standby -

alter database close;

select CURRENT_SCN, SWITCHOVER_STATUS, DATABASE_ROLE, open_mode from v$database;

CURRENT_SCN SWITCHOVER_STATUS DATABASE_ROLE   OPEN_MODE

----------- -------------------- ---------------- --------------------

  0 NOT ALLOWED SNAPSHOT STANDBY MOUNTED

alter database convert to physical standby;

shut immediate

startup mount

alter database recover managed standby database using current logfile disconnect from session;

SQL> select CURRENT_SCN, SWITCHOVER_STATUS, DATABASE_ROLE, open_mode from v$database;

CURRENT_SCN SWITCHOVER_STATUS DATABASE_ROLE   OPEN_MODE

----------- -------------------- ---------------- --------------------

     378629 NOT ALLOWED PHYSICAL STANDBY MOUNTED

MANUAL

******

PRIMARY

=======

-- Archive the current log and defer the log_archive_dest_2

alter system archive log current;

alter system set log_archive_dest_state_2=DEFER;

STANDBY

=======

-- Activating the standby

-- Stop managed recovery, create a guaranteed restore point and activate the standby. Ensure db_recovery_file_dest is set.

alter database recover managed standby database cancel;

alter system set log_archive_dest_state_2=DEFER;

create restore point before_testing guarantee flashback database;

alter database activate physical standby database;

alter database open;

select CURRENT_SCN, SWITCHOVER_STATUS, DATABASE_ROLE, open_mode from v$database;

select CONTROLFILE_TYPE from v$database; 

-- Converting back to standby

startup mount force

flashback database to restore point before_testing;

alter database convert to physical standby;

startup mount force

drop restore point before_testing;

alter database recover managed standby database using current logfile disconnect from session;

Reference -

How To Open Physical Standby For Read Write Testing and Flashback (Doc ID 805438.1)

 

Oracle database final_blocking_session when we have multiple blocking sessions

When  there are multiple blockings in database  its confusing to gind  actual blocking .  Oracle has simplified it by   final_blocking_session .

final_blocking_instance:  This column of gv$session is the instance identifier of the final blocking session. This column is valid only if final_blocking_session_status=valid.

final_blocking_session:  This column of gv$session is the session identifier of the final blocking session. This column is valid only if final_blocking_session_status=valid.

exec dbms_output.put_line('========== Session Details ==========');

set feedback on

set lines 300

set pages 1000

column TERMINAL format a30

column USERNAME format a15

column OSUSER format a20

column MACHINE format a20

column PROGRAM format a30

select inst_id,sid,FINAL_BLOCKING_SESSION ,final_blocking_instance ,serial#,username,osuser,machine,terminal,program,status,to_char(logon_time, 'dd-mon-yy hh24:mi:ss') , event, blocking_session , sql_id 

from gv$session 

where FINAL_BLOCKING_SESSION_STATUS='VALID'   or blocking_session is not NULL

order by LOGON_TIME,machine,program;

set pages 1000

set lines 120

set heading off

column w_proc format a50 tru

column instance format a20 tru

column inst format a28 tru

column wait_event format a50 tru

column p1 format a16 tru

column p2 format a16 tru

column p3 format a15 tru

column Seconds format a50 tru

column sincelw format a50 tru

column blocker_proc format a50 tru

column fblocker_proc format a50 tru

column waiters format a50 tru

column chain_signature format a100 wra

column blocker_chain format a100 wra

SELECT * 

FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE, 

 'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'<none>',blocker_osid)|| 

 ' from Instance '||blocker_instance BLOCKER_PROC,

 'Number of waiters: '||num_waiters waiters,

 'Final Blocking Process: '||decode(p.spid,null,'<none>',

 p.spid)||' from Instance '||s.final_blocking_instance FBLOCKER_PROC, 

 'Program: '||p.program image,

 'Wait Event: ' ||wait_event_text wait_event, 'P1: '||wc.p1 p1, 'P2: '||wc.p2 p2, 'P3: '||wc.p3 p3,

 'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,

 'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,

 '<none>',blocker_chain_id) blocker_chain

FROM v$wait_chains wc,

 gv$session s,

 gv$session bs,

 gv$instance i,

 gv$process p

WHERE wc.instance = i.instance_number (+)

 AND (wc.instance = s.inst_id (+) and wc.sid = s.sid (+)

 and wc.sess_serial# = s.serial# (+))

 AND (s.inst_id = bs.inst_id (+) and s.final_blocking_session = bs.sid (+))

 AND (bs.inst_id = p.inst_id (+) and bs.paddr = p.addr (+))

 AND ( num_waiters > 0

 OR ( blocker_osid IS NOT NULL

 AND in_wait_secs > 10 ) )

ORDER BY chain_id,

 num_waiters DESC)

WHERE ROWNUM < 101;

Oracle Database - Query opatch inventory using SQL interface

 

Listing alternate way to check opatch  details , using   sqlplus 

==> Sql to check output similar to  opatch lsinventory 

with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)

  select x.*

    from a,

         xmltable('InventoryInstance/patches/*'

            passing a.patch_output

            columns

               patch_id number path 'patchID',

               patch_uid number path 'uniquePatchID',

               description varchar2(80) path 'patchDescription',

               applied_date varchar2(30) path 'appliedDate',

               sql_patch varchar2(8) path 'sqlPatch',

               rollbackable varchar2(8) path 'rollbackable'

         ) x;

 select bugno, value, optimizer_feature_enable, is_default from v$system_fix_control  ;

==> Sql  to check detailed  output . similar to opatch lsinventory detail

with a as (select dbms_qopatch.get_opatch_bugs patch_output from dual)

  select x.*

    from a,

         xmltable('bugInfo/bugs/*'

            passing a.patch_output

            columns

               bug_id number path '@id',

               description varchar2(160) path 'description'

         ) x;

==> Checking Precise output 

select patch_id, patch_uid, target_version, status, description, action_time

from dba_registry_sqlpatch

where action = 'APPLY';  

Oracle Checking Hang sessions in Rac Database -- Rac Hang Manager

 

For  Rac  ,   checking  hung session is simplified using  Rac Hung Manager . For  Non  Rac  i personally use v$sess_io  or  try enabling session tracing  

 In 12.1.0.1, hang manager can detect hang between database and asm. 2.Deadlock or Closed Chain

Deadlock or close the chain. The only way to break the deadlock chain is to let some of these sessions complete their work or be terminated. 3.Hang or Open Chain

In the Oracle database, suspend (hang) refers to the waiting state entered by a process due to the inability to obtain the requested resources, which can be lifted only after the requested resources have been obtained, and the HM implements the management of hangs, including the monitoring, analysis, recording and resolution of hang.

The wait chain is made up of blocking processes and waiting processes, while one or more root blocking processes exist in the blocking process, which blocks all other processes, and if the root blocking process is busy with some operations, then perhaps the presence of such a wait chain is normal, if the blocking process is idle, Then perhaps the emergence of this wait chain is not normal, and the way to break the wait chain is to terminate the root blocking process. HM can proactively discover the existence of the waiting chain in the database, and from the perspective of the analysis of them, if found to really affect the performance of the data block hang, depending on the specific circumstances to determine whether to solve the problem, and even if not directly resolved, the corresponding diagnostic information will be recorded and continuous monitoring.

V$hang_info: This view contains details of the hang that was found by HM.

V$hang_session_info: This view contains the session information related to hang.

V$hang_statistics: This view contains statistics related to hang.

The work of HM is composed of seven stages

Phase 1 (Collection Phase): At this stage, the DIA0 process for each instance collects hang analyze information on a regular basis.

Phase 2 (Discovery phase): At this stage, the DIA0 process for each instance analyzes the collected hang Alalyze information, locates the session where hang is present, and sends the DIA0 process to the master node.

Phase 3 (Drawing phase): At this stage, the dia0 process of the master node draws the message from each instance of the DIA0 process, drawing the wait chain.

Phase 4 (Analysis Phase): At this stage, the master node dia0 the process according to the drawn wait chain and analyzes whether hang is indeed present.

Phase 5 (Validation phase): At this stage, the master node dia0 process executes phase 1-4 again, then compares the analysis results of phase 4 with this one, and verifies that hang is really happening.

Phase 6 (Positioning phase): At this stage, the results of the master node dia0 process More validation phase are positioned to the root blocking process of the wait chain.

Phase 7 (resolution Phase): At this stage, the master node dia0 process determines whether hang can be resolved based on the value of the parameter _hang_resoluton_scope.

Trace log files for the DIA0 process

Main trace file (<SID>_DIA0_<PID>.TRC): This log file records the details of the DIA0 process, including the process of discovering, analyzing, and handling the hang.

History Tracker File (<sid>_dia0_<pid>_ N.TRC): Because the trace log file of the DIA0 process constantly generates information as the database runs, it can make the log file very large, and the DIA0 process periodically writes log information to its history log file, where n is a positive integer and increases over time.

Incident Log file: If HM resolves the hang by terminating the process, the ORA-32701 error is first recorded in the Alert.log, and because of the existence of the ADR, the DIA0 process also produces a incident log file that records the details of the problem.

Parameters of HM

_hang_detection_enabled: This parameter determines whether the HM attribute is enabled in the database, and the default value is true.

_hang_detection_interval: This parameter specifies the time interval for which HM collects hang analyze information, and the default value is 32s.

_hang_verification_interval: This parameter specifies the time interval for the HM Validation hang, and the default value is 46s.

_hang_resolution_scope: This parameter specifies the range that HM can operate when the hang is resolved, the default value is process, and the allowable values are as follows:

OFF: The HM will only continue to monitor hang, and will not do anything to fix hang.

Process: Indicates that HM can resolve hang by terminating the root blocking process, but the root blocking process here cannot be an important background process for the database because it causes the instance to crash.

Instance: Indicates that HM can resolve the hang by terminating the instance

We can get complete  list from DBA_HANG_MANAGER_PARAMETERS

Related parameters:

NAME                                               VALUE                          ISDEFAULT ISMOD      ISADJ
-------------------------------------------------- ------------------------------ --------- ---------- -----
_hang_analysis_num_call_stacks                     3                              TRUE      FALSE      FALSE
_hang_base_file_count                              5                              TRUE      FALSE      FALSE
_hang_base_file_space_limit                        10000000                       TRUE      FALSE      FALSE
_hang_bool_spare1                                  TRUE                           TRUE      FALSE      FALSE
_hang_delay_resolution_for_libcache                TRUE                           TRUE      FALSE      FALSE
_hang_detection_enabled                            TRUE                           TRUE      FALSE      FALSE
_hang_detection_interval                           32                             TRUE      FALSE      FALSE
_hang_hang_analyze_output_hang_chains              TRUE                           TRUE      FALSE      FALSE
_hang_hiload_promoted_ignored_hang_count           2                              TRUE      FALSE      FALSE
_hang_hiprior_session_attribute_list                                              TRUE      FALSE      FALSE
_hang_ignored_hang_count                           1                              TRUE      FALSE      FALSE
_hang_ignored_hangs_interval                       300                            TRUE      FALSE      FALSE
_hang_int_spare2                                   FALSE                          TRUE      FALSE      FALSE
_hang_log_verified_hangs_to_alert                  FALSE                          TRUE      FALSE      FALSE
_hang_long_wait_time_threshold                     0                              TRUE      FALSE      FALSE
_hang_lws_file_count                               5                              TRUE      FALSE      FALSE
_hang_lws_file_space_limit                         10000000                       TRUE      FALSE      FALSE
_hang_monitor_archiving_related_hang_interval      300                            TRUE      FALSE      FALSE
_hang_msg_checksum_enabled                         TRUE                           TRUE      FALSE      FALSE
_hang_resolution_allow_archiving_issue_termination TRUE                           TRUE      FALSE      FALSE
_hang_resolution_confidence_promotion              FALSE                          TRUE      FALSE      FALSE
_hang_resolution_global_hang_confidence_promotion  FALSE                          TRUE      FALSE      FALSE
_hang_resolution_policy                            HIGH                           TRUE      FALSE      FALSE
_hang_resolution_promote_process_termination       FALSE                          TRUE      FALSE      FALSE
_hang_resolution_scope                             PROCESS                        TRUE      FALSE      FALSE
_hang_short_stacks_output_enabled                  TRUE                           TRUE      FALSE      FALSE
_hang_signature_list_match_output_frequency        10                             TRUE      FALSE      FALSE
_hang_statistics_collection_interval               15                             TRUE      FALSE      FALSE
_hang_statistics_collection_ma_alpha               30                             TRUE      FALSE      FALSE
_hang_statistics_high_io_percentage_threshold      15                             TRUE      FALSE      FALSE
_hang_verification_interval                        46                             TRUE      FALSE      FALSE