tech_memo / oracle_RAC

tech_memo/ORACLE

SQLでRAC構成インスタンスとステータスの確認 †

• GV$INSTANCEを見ればわかる

  select inst_id, instance_number, instance_name, status, host_name from gv$instance;

  • ちなみに、gv$sessionというグローバルビューもある

全RACインスタンスに接続しているセッション情報を取得 †

• gv$sessionというグローバルビューから確認可能
  • http://d.hatena.ne.jp/nijimass/20080420/1208649455

Document †

RAC ポリシーベース管理(11gR2) †

• http://www.oracle.com/technetwork/jp/database/enterprise-edition/db11gr2-05-134785-ja.pdf

RAC間のインターコネクタのIP †

• V$CLUSTER_INTERCONNECTSから把握できる

  SQL> desc V$CLUSTER_INTERCONNECTS
   Name                                      Null?    Type
   ----------------------------------------- -------- ----------------------------
   NAME                                               VARCHAR2(15)
   IP_ADDRESS                                         VARCHAR2(16)
   IS_PUBLIC                                          VARCHAR2(3)
   SOURCE                                             VARCHAR2(31)
  
  SQL> select * from V$CLUSTER_INTERCONNECTS;
  
  NAME            IP_ADDRESS       IS_ SOURCE
  --------------- ---------------- --- -------------------------------
  bond1:1         169.254.168.119  NO

指定インスタンスに接続 †

• tnsnames.ora

  RAC =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = rac-cluster-scan)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = rac)
     )
   )
  
  RAC1 =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = racserver1)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = rac)
       (INSTANCE_NAME = rac1)
     )
   )
  
  RAC2 =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = racserver2)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = rac)
       (INSTANCE_NAME = rac2)
     )
   )

インストール・セットアップ †

• tech_memo/oracle_RAC/Install

アンインストール †

• tech_memo/ORACLE/oracle_RAC/Uninstall

RAC Performance †

• http://proligence.com/oow09_rac.pdf
• チューニング
  • http://www.oracle.com/technetwork/jp/ondemand/b-10-ractuning-1448392-ja.pdf

RAC奮闘記 †

• tech_memo/oracle_RAC/problem

起動・停止 †

• http://www.doppo1.net/oracle/admin/rac_operation.html
  • 完全停止の場合はemctlでEMを両ノード落とす！crsctlは両ノードで実行する！psでoracleとgridのプロセスがいないのを確認する！
• インスタンス指定しての停止

  srvctl stop instance -d rac -i rac2

  • racserver2だけ落として、しばらくしてracserver2をstartしたあと、scan_listener経由の接続ができなくなった。

    m-nawata@myserver% sqlplus NAWASON/NAWASON@RAC                                                                                                       [~]
    
    SQL*Plus: Release 11.2.0.2.0 Production on Tue Mar 26 08:23:20 2013
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved. 
    
    ERROR:
    ORA-12541: TNS:no listener

  • srvctlでlistenrの再起動を行ってもうまくいかず。vipも再起動しようとしたら、停止はできたが、起動できなくなった。サーバ再起動してもダメ。（サーバ再起動時はcrsctlでリソース全落とししてから）
  • /etc/hostsにvipとprivate-ipの記述がなぜかなくなっていた。追記して再度、再起動したら、racserver1だけvipが起動していた。racserver2はsrvctlでupできた。謎。

• サーバ再起動時にはまったこと。
  • clusterwareがうまく動作しない(statusが見れない)。racserver1,racserver2のvipが起動していなかった。srvctlでracserver1とracserver2のvipを手動で起動するとうまくいった。

thin driver †

• SIDの前がRACかSingleかで、「/」か「:」になる
  • RAC

    jdbc:oracle:thin:@rac-cluster-scan:1521/RACDB

  • Single

    jdbc:oracle:thin:@dbserver:1521:TESTDB

tnsnames.ora †

# tnsnames.ora Network Configuration File:  
# Generated by Oracle configuration tools.

NB01 =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = hcs00c10)(PORT = 1522))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = nba.npa.go.jp)
     (INSTANCE_NAME = nb01)
   )
 )

NB02 =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = hcs00c11)(PORT = 1522))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = nba.npa.go.jp)
     (INSTANCE_NAME = nb02)
   )
 )

NB03 =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = hcs00i10)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = nb03.npa.go.jp)
     (INSTANCE_NAME = nb03)
   )
 )

EXTPROC_CONNECTION_DATA =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
   )
   (CONNECT_DATA =
     (SID = PLSExtProc)
     (PRESENTATION = RO)
   )
 )

NBA_RAC1_LISTENER =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = hcs00c10)(PORT = 1522))
   (ADDRESS = (PROTOCOL = TCP)(HOST = hcs00c30)(PORT = 1522))
   (ADDRESS = (PROTOCOL = TCP)(HOST = hcs00c11)(PORT = 1522))
   (ADDRESS = (PROTOCOL = TCP)(HOST = hcs00c31)(PORT = 1522))
   (FAILOVER = ON)
   (LOAD_BALANCE = OFF)
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = nba.npa.go.jp)
     (FAILOVER_MODE =
       (BACKUP = NBA_RAC2_LISTENER)(TYPE = SELECT)
       (METHOD = BASIC)(RETRIES = 180)(delay = 3)
     )
   )
 )

NBA_RAC2_LISTENER =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = hcs00c11)(PORT = 1522))
   (ADDRESS = (PROTOCOL = TCP)(HOST = hcs00c31)(PORT = 1522))
   (ADDRESS = (PROTOCOL = TCP)(HOST = hcs00c10)(PORT = 1522))
   (ADDRESS = (PROTOCOL = TCP)(HOST = hcs00c30)(PORT = 1522))
   (FAILOVER = ON)
   (LOAD_BALANCE = OFF)
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = nba.npa.go.jp)
     (FAILOVER_MODE =
       (BACKUP = NBA_RAC1_LISTENER)(TYPE = SELECT)
       (METHOD = BASIC)(RETRIES = 180)(delay = 3)
     )
   )
 )

Arch †

• 超入門
  • http://oralce.blog91.fc2.com/blog-entry-7.html