• Devops AWS
  • Git Tutorial
  • Hadoop Tutorial
  • Apache Spark
  • Docker
  • Amazon RDS
  • Blockchain
  • BIG-DATA
  • AWS-Architect
  • COVID-19
  • Proxy List
  • AWS-Certified
  • Cloud-Computing
  • AWS certifications
AWS Certified Solutions Architect - Professional
  • Home
    • Home – Layout 1
    • Home – Layout 2
    • Home – Layout 3
    • Home – Layout 4
    • Home – Layout 5
    • Home – Layout 6
  • News
    • All
    • Business
    • Politics
    • Science
    • World
    Load data into AWS Redshift from AWS S3

    Load data into AWS Redshift from AWS S3

    probable Oracle Net admin error

    DIA-28546: connection initialization failed, probable Net8 admin error

    The data on the coronavirus pandemic is updated daily.

    Covid-19

    The data on the coronavirus pandemic is updated daily.

    Covid-19 update

    AWS Certified Cloud Practitioner

    AWS Certified Cloud Practitioner

    Database Fundamentals for AWS

    Database Fundamentals for AWS

    Trending Tags

    • Trump Inauguration
    • United Stated
    • White House
    • Market Stories
    • Election Results
  • Tech
    • All
    • Apps
    • Gadget
    • Mobile
    • Startup
    sellers.json

    sellers.json

    A Performance Dashboard for Apache Spark

    A Performance Dashboard for Apache Spark

    Step-by-Step Tutorial for Apache Spark Installation

    Apache Spark 3.0 Memory Monitoring Improvements

    Getting started with Amazon S3 and Python

    Getting started with Amazon S3 and Python

    Implementing an SSAS Tabular Model for Data Analytics

    Implementing an SSAS Tabular Model for Data Analytics

    Configure ODBC drivers for MySQL

    Configure ODBC drivers for MySQL

    Trending Tags

    • Nintendo Switch
    • CES 2017
    • Playstation 4 Pro
    • Mark Zuckerberg
  • Entertainment
    • All
    • Gaming
    • Movie
    • Music
    • Sports
    free A proxy server

    Fresh Proxy Lists update 2020-06-28

    DIA-28073: The column "string" has an unsupported data type or

    ORA-16627: operation disallowed since no member would remain to support

    Fresh Proxy Lists update

    The Legend of Zelda: Breath of the Wild gameplay on the Nintendo Switch

    macOS Sierra review: Mac users get a modest update this year

    Hands on: Samsung Galaxy A5 2017 review

    Heroes of the Storm Global Championship 2017 starts tomorrow, here’s what you need to know

  • Lifestyle
    • All
    • Fashion
    • Food
    • Health
    • Travel
    covid-19 update

    Fresh Proxy Lists update 2020-06-18

    ORA-39866: Data files for Pluggable Database string must be offline to

    ORA-16693: requirements not met for enabling fast-start failover

    The data on the coronavirus pandemic is updated daily.

    Covid-19

    The data on the coronavirus pandemic is updated daily.

    Covid-19 update

    AWS Certified Cloud Practitioner

    AWS Certified Cloud Practitioner

    Covid-19 -update

    Covid-19 -update-2020-06-16

    Trending Tags

    • Golden Globes
    • Game of Thrones
    • MotoGP 2017
    • eSports
    • Fashion Week
  • Review
    covid-19 update

    Fresh Proxy Lists update 2020-06-18

    AWS Cloud Practitioner

    AWS Cloud Practitioner course

    AWS Cloud Practitioner Essentials

    AWS Cloud Practitioner Essentials

    Machine Learning Tutorial

    Machine Learning Tutorial

    Cloud AWS – Amazon Web Services

    Cloud AWS – Amazon Web Services

    AWS Amazon

    formation AWS Amazon

No Result
View All Result
  • Home
    • Home – Layout 1
    • Home – Layout 2
    • Home – Layout 3
    • Home – Layout 4
    • Home – Layout 5
    • Home – Layout 6
  • News
    • All
    • Business
    • Politics
    • Science
    • World
    Load data into AWS Redshift from AWS S3

    Load data into AWS Redshift from AWS S3

    probable Oracle Net admin error

    DIA-28546: connection initialization failed, probable Net8 admin error

    The data on the coronavirus pandemic is updated daily.

    Covid-19

    The data on the coronavirus pandemic is updated daily.

    Covid-19 update

    AWS Certified Cloud Practitioner

    AWS Certified Cloud Practitioner

    Database Fundamentals for AWS

    Database Fundamentals for AWS

    Trending Tags

    • Trump Inauguration
    • United Stated
    • White House
    • Market Stories
    • Election Results
  • Tech
    • All
    • Apps
    • Gadget
    • Mobile
    • Startup
    sellers.json

    sellers.json

    A Performance Dashboard for Apache Spark

    A Performance Dashboard for Apache Spark

    Step-by-Step Tutorial for Apache Spark Installation

    Apache Spark 3.0 Memory Monitoring Improvements

    Getting started with Amazon S3 and Python

    Getting started with Amazon S3 and Python

    Implementing an SSAS Tabular Model for Data Analytics

    Implementing an SSAS Tabular Model for Data Analytics

    Configure ODBC drivers for MySQL

    Configure ODBC drivers for MySQL

    Trending Tags

    • Nintendo Switch
    • CES 2017
    • Playstation 4 Pro
    • Mark Zuckerberg
  • Entertainment
    • All
    • Gaming
    • Movie
    • Music
    • Sports
    free A proxy server

    Fresh Proxy Lists update 2020-06-28

    DIA-28073: The column "string" has an unsupported data type or

    ORA-16627: operation disallowed since no member would remain to support

    Fresh Proxy Lists update

    The Legend of Zelda: Breath of the Wild gameplay on the Nintendo Switch

    macOS Sierra review: Mac users get a modest update this year

    Hands on: Samsung Galaxy A5 2017 review

    Heroes of the Storm Global Championship 2017 starts tomorrow, here’s what you need to know

  • Lifestyle
    • All
    • Fashion
    • Food
    • Health
    • Travel
    covid-19 update

    Fresh Proxy Lists update 2020-06-18

    ORA-39866: Data files for Pluggable Database string must be offline to

    ORA-16693: requirements not met for enabling fast-start failover

    The data on the coronavirus pandemic is updated daily.

    Covid-19

    The data on the coronavirus pandemic is updated daily.

    Covid-19 update

    AWS Certified Cloud Practitioner

    AWS Certified Cloud Practitioner

    Covid-19 -update

    Covid-19 -update-2020-06-16

    Trending Tags

    • Golden Globes
    • Game of Thrones
    • MotoGP 2017
    • eSports
    • Fashion Week
  • Review
    covid-19 update

    Fresh Proxy Lists update 2020-06-18

    AWS Cloud Practitioner

    AWS Cloud Practitioner course

    AWS Cloud Practitioner Essentials

    AWS Cloud Practitioner Essentials

    Machine Learning Tutorial

    Machine Learning Tutorial

    Cloud AWS – Amazon Web Services

    Cloud AWS – Amazon Web Services

    AWS Amazon

    formation AWS Amazon

No Result
View All Result
AWS Certified Solutions Architect - Professional
No Result
View All Result
Home aws-senior

Oracle DBA Hanganalyze ,system state dump,wait_chains

aws-senior by aws-senior
26 August 2019
in aws-senior
0
eod_performance_oem_snapshot

eod_performance_oem_snapshot

0
SHARES
9
VIEWS
Share on FacebookShare on TwitterShare on Linjedin

Many times Oracle database get hangs due to locks or latching issue and Nobody can login to the system. It becomes critical and  And Usually we do shutdown abort to immediately resolve the issue. But  it is good to take system state dump/hanganalyze  at 1 min interval to analyze the locking or latching  issue afterwards before doing shutdown abort so that we can find the fix for the problem and avoid that in future.Oracle Support also usually ask for these when the database hangs for some reason

Here are the steps to take system state dump

In RAC system this need to be executed on all the instance

Syntax for system state dump:

Logon to sqlplus as sysdba
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug dump systemstate 266
…….. Wait at least 1 min
SQL> oradebug dump systemstate 266
…….. Wait at lease 1 min
SQL> oradebug dump systemstate 266
SQL> oradebug tracefile_name

With releases 11g & RAC and above You should attach to the DIAG process and change its file size limit

The following commands will create the system state dump on each node in the diag trace file.No need to execute on all instances

sqlplus "/ as sysdba"
select SPID from v$process where program like '%DIAG%';
oradebug setospid <OS_PID> -- pid of the diag process
oradebug unlimit
oradebug dump systemstate 266
oradebug tracefile_name
exit

Similarly Hanganalyze is a good utility to analysis.

eod_performance_oem_snapshot
eod_performance_oem_snapshot

Here are the steps

Syntax for hanganalyze for single instance

Logon to sqlplus as sysdba
oradebug setmypid;
oradebug unlimit;
oradebug hanganalyze 3;
oradebug tracefile_name

Syntax for hanganalyze for RAC

Logon to sqlplus as sysdba
oradebug setmypid;
oradebug unlimit;
oradebug setinst all
oradebug -g def hanganalyze 3
oradebug tracefile_name

Sometimes it is not possible to even login with sqlplus “/ as sysdba ” at the time of hang, In that situation we can use below command to enter with 11g onwards

sqlplus -prelim / as sysdba

V$wait_chains

From 11gR2 onwards,oracle has provided a dynamic performance view called v$wait_chains. This also contain same information which we gather by running the hanganalyze command. So instead of using hanganalyze, you can use the below query to find wait chains

This is done using dia0 background processes starts collecting hanganalyze information and stores this in memory in the “hang analysis cache”. It does this every 3 seconds for local hanganalyze information and every 10 seconds for global (RAC) hanganalyze information

There is no gv$ equivalent as v$wait_chains would report on multiple instances in a multi-instance (RAC) environment

There is no gv$ equivalent as v$wait_chains would report on multiple instances in a multi-instance (RAC) environment

SQL> SELECT chain_id, num_waiters, in_wait_secs, osid, blocker_osid, substr(wait_event_text,1,30)
 FROM v$wait_chains; 
SELECT decode( a.blocker_sid , NULL , '<chain id#' ||a.chain_id||'>' ) chain_id,
RPAD( '+' , LEVEL , '-' ) ||a.sid sid,
RPAD( ' ' , LEVEL , ' ' ) ||a.wait_event_text wait_event
FROM V$WAIT_CHAINS a
CONNECT BY PRIOR a.sid=a.blocker_sid
AND PRIOR a.sess_serial#=a.blocker_sess_serial#
AND PRIOR a.instance = a.blocker_instance START WITH a.blocker_is_valid='FALSE'
ORDER BY a.chain_id ,
LEVEL
/

Query for Top 100 wait chain processes

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,'',blocker_osid)|| 
 ' from Instance '||blocker_instance BLOCKER_PROC,
 'Number of waiters: '||num_waiters waiters,
 'Final Blocking Process: '||decode(p.spid,null,'',
 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,
 '',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.final_blocking_instance = 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;

Share this:

  • Facebook
  • LinkedIn
  • Twitter
aws-senior

aws-senior

http://www.aws-senior.com/

Aws-Senior.com

Aws-Senior.com
ADVERTISEMENT
  • Trending
  • Comments
  • Latest
Use our free ssl american proxy server update-update-2020-06-09

Fresh Proxy Lists update 2020-06-15

15 June 2020
HTTP Socks4 and Socks5 proxy lists-update

Fresh Proxy Lists update 2020-07-10

14 July 2020
How To Import A Schema on Amazon RDS

How To Import A Schema on Amazon RDS

5 April 2019
Install the AWS CLI on Windows

Install the AWS CLI on Windows

16 March 2019
probable Oracle Net admin error

ORA-16795: the standby database needs to be re-created

2

Overview of Amazon Web Services

1

Senior Account Manager – Middle East Public Sector

1
aws-senior.com

AWS Certified Solutions Architect – Niveau Professionnel

1
sellers.json

sellers.json

10 September 2020
Step-by-Step Tutorial for Apache Spark Installation

Apache Spark Deployment

3 September 2020
Apache Spark Installation

Step-by-Step Tutorial for Apache Spark Installation

3 September 2020
PySpark et le traitement des Big Data

Apache Spark

2 September 2020

Recent News

sellers.json

sellers.json

10 September 2020
Step-by-Step Tutorial for Apache Spark Installation

Apache Spark Deployment

3 September 2020
Apache Spark Installation

Step-by-Step Tutorial for Apache Spark Installation

3 September 2020
PySpark et le traitement des Big Data

Apache Spark

2 September 2020
AWS Certified Solutions Architect – Professional

aws-senior.com . Find user guides, developer guides, API references, tutorials, and more. Guides and API References. Compute. Amazon EC2 · AWS Batch

Follow Us

Facebook Twitter Instagram Behance Google+ Dribbble

Browse by Category

  • Apps (21)
  • aws-senior (277)
  • Business (21)
  • Entertainment (2)
  • Fashion (18)
  • Food (13)
  • Gadget (25)
  • Gaming (14)
  • Health (18)
  • Lifestyle (10)
  • Mobile (21)
  • Movie (9)
  • Music (12)
  • News (16)
  • Politics (20)
  • Review (14)
  • Science (16)
  • Sports (14)
  • Startup (23)
  • Tech (21)
  • Travel (20)
  • World (16)

Recent News

sellers.json

sellers.json

10 September 2020
Step-by-Step Tutorial for Apache Spark Installation

Apache Spark Deployment

3 September 2020
  • Devops AWS
  • Git Tutorial
  • Hadoop Tutorial
  • Apache Spark
  • Docker
  • Amazon RDS
  • Blockchain
  • BIG-DATA
  • AWS-Architect
  • COVID-19
  • Proxy List
  • AWS-Certified
  • Cloud-Computing
  • AWS certifications

© 2020 www.aws-senior.com - www.aws-senior.com www.aws-senior.com.

No Result
View All Result

© 2020 www.aws-senior.com - www.aws-senior.com www.aws-senior.com.