如何检查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%';

原文地址：https://blog.csdn.net/jnrjian/article/details/140180406

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！