如何检查oracle services 切换过 table(gv$(cursor(select * from v$diag_alert_ext)))

Rac Services used for fail over.

User195957 Posts: 125

Oct 3, 2011 3:53PM edited Oct 6, 2011 12:33PM 6 commentsAnswered

Hi all,How to check if any failed over services are running on other node instead of original node.How I can check on which date it got failed over to other node?
Please let me know the command or sql which can list time of failed over services.
I tried srvctl service status -d db
I also tried:
COLUMN instance_name FORMAT a13
COLUMN host_name FORMAT a9
COLUMN failover_method FORMAT a15
COLUMN failed_over FORMAT a11

SELECT DISTINCT
       v.instance_name AS instance_name,
       v.host_name AS host_name,
       s.failover_type AS failover_type,
       s.failover_method AS failover_method,
       s.failed_over AS failed_over
  FROM v$instance v, v$session s


But did not get the desired output.

Regards.

FlagQuoteOff TopicLike

Answers

  • BPeaslandDBA Posts: 11,665 Jadeite

    Oct 3, 2011 4:39PM

    How about this:
    SELECT inst_id,service_name,count(*)
    FROM gv$session
    GROUP BY inst_id,service_name
    ORDER BY 2,1;

    You will want to query GV$SESSION since you are on RAC>

    Alternatively, if you do "lsnrctl status", it will show you were the services are running.

    HTH,
    Brian
     

    -- Cheers, Brian

    Author, Oracle guy, and hockey fan

    http://www.peasland.net

    FlagQuoteOff TopicLike

  • User195957 Posts: 125

    Oct 4, 2011 7:49AM

    I am not asking about the status. I need to know when the service got fail over to other node. I need to know the time and date.

    FlagQuoteOff TopicLike

  • Helmut -Oracle Posts: 2,559 Bronze Crown

    Oct 4, 2011 10:37AM

    Why don't you look into the log files, especially into the alert file to find out the time and date? Once you know that the service actually failed over, you should be able to get date and time from the log files. I assume that that can be automated per script.

    FlagQuoteOff TopicLike

  • BPeaslandDBA Posts: 11,665 Jadeite

    Oct 5, 2011 11:51AM

    You can also check the CRS logs for this information.
    Cheers,
    Brian

    -- Cheers, Brian

    Author, Oracle guy, and hockey fan

    http://www.peasland.net

    FlagQuoteOff TopicLike

  • LeightonLNelson Posts: 225 Red Ribbon

    Oct 5, 2011 2:15PM

    Helmut is correct. The alert logs have the date/time when the services were failed over. You could try querying V$DIAG_ALERT_EXT for "ALTER SYSTEM set service_name" messages.
    Thanks,
    Leighton

    FlagQuoteOff TopicLike

  • User195957 Posts: 125

    Oct 6, 2011 12:33PM

    Thanks for the reply!!!Let me know any command by which we can filter the specific timestamp and specific word.

    Regards!!!

    FlagQuoteOff TopicLike

  • User_D0ISD Posts: 1 Newbie

    3:14PM

    select MESSAGE_TEXT, a.* from v$diag_alert_ext a where ORIGINATING_TIMESTAMP >sysdate-1 and MESSAGE_TEXT like '%service%';

----RAC one node

Yes, I need to find out when dtabase services switch over from one node of RAC , and this sql helps

select ORIGINATING_TIMESTAMP, MESSAGE_TEXT from table(gv$(cursor(select * from v$diag_alert_ext))) where ORIGINATING_TIMESTAMP >sysdate-0.1 and MESSAGE_TEXT like '%service%';

相关推荐

  1. WebGL BabylonJs 如何切换相机

    2024-07-10 07:38:02       19 阅读
  2. Linux如何切换root用户

    2024-07-10 07:38:02       19 阅读

最近更新

  1. docker php8.1+nginx base 镜像 dockerfile 配置

    2024-07-10 07:38:02       3 阅读
  2. Could not load dynamic library ‘cudart64_100.dll‘

    2024-07-10 07:38:02       3 阅读
  3. 在Django里面运行非项目文件

    2024-07-10 07:38:02       2 阅读
  4. Python语言-面向对象

    2024-07-10 07:38:02       2 阅读

热门阅读

  1. CSS 下拉菜单的设计与实现

    2024-07-10 07:38:02       9 阅读
  2. 快速排序算法Python实现

    2024-07-10 07:38:02       8 阅读
  3. python爬虫入门(二)之Requests库

    2024-07-10 07:38:02       7 阅读
  4. RTK_ROS_导航(4):ROS中空地图的生成与加载

    2024-07-10 07:38:02       10 阅读
  5. PCL + Qt + Ribbon 风格(窗口自由组合) demo展示

    2024-07-10 07:38:02       17 阅读
  6. Android Studio Download Gradle 时慢问题解决

    2024-07-10 07:38:02       6 阅读
  7. ASPICE是汽车软件开发中的质量保证流程

    2024-07-10 07:38:02       10 阅读
  8. 游戏开发面试题2

    2024-07-10 07:38:02       11 阅读
  9. 4.10-7.9

    2024-07-10 07:38:02       10 阅读