FW: Relocating SCAN VIP from one node to another node

一般來說, 會有這樣的需求大概會發生在幾個scenario

1.instance 要調整參數, 所以要重啟

2.Oracle RAC的某個節點需要進行patch , 需要停下來instance

3.服務切換測試

 在做RAC節點切換測試的時候, 其實把單台資料庫的instance 給shutdown, 服務也不會因此受影響, client還是可以透過scan-ip 連到第二台節點。

如果要把scan listener Relocate到剩餘可用的節點, 要以grid使用者, 下

#趕scan_listener 1

srvctl relocate scan -i 1 -n rac1

#趕scan_listener 2

srvctl relocate scan -i 2 -n rac1

#趕scan_listener 3

srvctl relocate scan -i 3 -n rac1

把scan listener 都趕到剩下的可用節點. 才不至於影響AP連線服務.

 

Ref:

https://lokeshaggarwal.wordpress.com/2012/12/28/relocating-scan-vip-from-one-node-to-another-node/

 

Relocating SCAN VIP from one node to another node

Here, I am giving you a demonstration of relocation SCAN from one node to another.

In the below section, we can see that 3 SCAN VIP are running .e 1 VIP is running on rac2 and 2 VIP are running on rac1.

[grid@rac2 bin]$ ./srvctl status scan

SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node rac2
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node rac1
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node rac1

[grid@rac2 bin]$ ./srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node rac2
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node rac1
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node rac1

So, now lets see the syntax for relocating the scan.

[grid@rac2 bin]$ ./srvctl relocate scan -h

Temporarily relocate SCAN VIP from one node of the cluster to another.

Usage: srvctl relocate scan -i <ordinal_number> [-n <node_name>]
-i <scan_ordinal_number> Ordinal number of IP address
-n <node_name> SCAN VIP node name
-h Print usages

Now, lets try to relocate SCAN VIP 1 to rac1.

[grid@rac2 bin]$ ./srvctl relocate scan -i 1 -n rac1

So, as per the below section we can see that now all the scan listener are runing on rac1.Hence, we can also conclude that it is not necessare that SCAN listener has to be run on every node.It can run on all nodes or on partially some nodes.

[grid@rac2 bin]$ ./srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node rac1
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node rac1
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node rac1
[grid@rac2 bin]$

[grid@rac2 bin]$ ./srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node rac1
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node rac1
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node rac1
[grid@rac2 bin]$

關於Oracle 11g RAC , 使用normal redundancy Voting disk 的理解

http://www.oracle.com/technetwork/products/clusterware/overview/grid-infra-thirdvoteonnfs-131158.pdf

關於Oracle 11g RAC , 使用normal redundancy Voting disk 的理解

    1.   如果有三個voting disk , offline disk的數量大於online 數量, 節點會自動被重啟。

2.放在NFS的voting disk並不會存放任何資料，只是拿來當仲裁使用

     3.11g ASM DiskGroup在設定成normal redunancy的時候，votingdisk1, votingdisk2可以是SAN的環境、votingdisk3可以在NAS上透過linux  DD指令的方式建出此檔案，再利用asmca的GUI, 把NFS上的voting file加進去到disk group去

[轉寄]11g RAC回復OCR/ Voting的方法

P.S. 如果是12c RAC, 除了修回OCR/ Voting以外, 要再修一個MGMTDB,
詳細修復步驟如下:

http://jaychu649.blogspot.tw/2015/12/12c-rac-mgmtdb.html

參考連結:

Ref: http://oracledbabhuvan.blogspot.tw/2011/11/restoring-ocr-disk-voting-disk-on-asm.html

RECOVERING OCR DISK & VOTING DISK ON ASM DISK GROUP FROM CORRUPTION OR LOSS

When we have lost or having a corruption issue on the OCR & VOTING Disk, we have follow the below procedure to bring it back.

When using an ASM disk group for CRS there are typically 3 different types of files located in the disk group that potentially need to be restored/recreated for function of the cluster.

•    Oracle Cluster Registry file (OCR)

•    Voting files

•    Shared SPFILE for the ASM instances

In this scenario, we are trying to restore the corrupted OCR Disk & Voting Disk from the backup.

Step #1 Stop cluster on each node(Root user).

# crsctl stop crs -f

Step #2 we are starting the cluster in the excusive mode(Root user)

As root start GI in exclusive mode on one node only:

In 11201 RAC, we have to use below option to start the cluster in the exclusive mode.

# crsctl start crs -excl

In 11202 RAC, we have to use below option to start the cluster in the exclusive mode.

# crsctl start crs -excl -nocrs   =>這邊講的是, 如果OCR與voting掛了, 可以透過此方式強制把clusterware開至單機模式(先不啟動CRS  daemon)

crsctl start crs -h

Usage:

 crsctl start crs[-excl [-nocrs]|-nowait]

    Start OHAS onthis server

where

    -excl     Start Oracle Clusterware in exclusivemode

    -nocrs      Start Oracle Clusterware in exclusivemode without starting CRS

     -nowait    Do not wait for OHAS to start

Note: A new option '-nocrs' has been introduced with  11.2.0.2, which prevents the start of the ora.crsd resource. It is vital that this option is specified; otherwise the failure to start the ora.crsd resource will tear down ora.cluster_interconnect.haip, which in turn will cause ASM to crash.

If you don't have the OCR DISK GROUP, then create it else move to restoring OCR DISK

Step #3 OCR RESTORE

To Know the OCR Location on the cluster environment

$ cat /etc/oracle/ocr.loc  -- In Linux

To Check whether ocrcheck is corrupted or not

# ocrcheck

Check whether ocrcheck is able to complete it successfully

OCR CHECK Ex

# ocrcheck

Status of Oracle Cluster Registry is as follows :

         Version                  :          3

         Total space (kbytes)     :     262120

         Used space (kbytes)      :       4404

         Available space (kbytes) :     257716

         ID                       : 1306201859

         Device/File Name         :  +OCR_VOTE

                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded

       

Note:

1) Check whether cluster registry integrity check is successful.

         

2) When you run as oracle user, logical corruption check will be bypassed. You can see this line end of the "ocrcheck" output.

"Logical corruption check bypassed due to non-privileged user"

To Know the OCR Location on the cluster environment

$ cat /etc/oracle/ocr.loc  -- In Linux

If the OCR DISK corrupted, then perform the below steps

Locate OCR LOG file location

$GRID_HOME /log/<hostname>/client/ocrcheck_<pid>.log

Locate the latest automatic OCR backup

$GRID_HOME\bin\ocrconfig –showbackup

Restore the latest OCR backup(root user)

# ocrconfig -restore $GRID_HOME/cdata/racsapie1/backup00.ocr  => 必須是OCR/ Voring 所屬的ASM DiskGroup被重新建出來並已經被掛載的狀況下. 指定才會成功

racsapie1  SCAN NAME for the cluster

Step #4 VOTING DISK RECREATE

          

Recreate the Voting file (root user)

The Voting file needs to be initialized in the CRS disk group

# crsctl replace votedisk +OCR_DISK   => 必須是OCR/ Voring 所屬的ASM DiskGroup被重新建出來並已經被掛載的狀況下. 指定才會成功

Note:

1) Above command will Re-create/move your voting disk in the specified ASM Disk Group, if you query the voting disk it will display your voting disk location in the DISK Group which has been specified above.

2)  Voting File is that it is no longer supported to take a manual backup of it with dd.  Instead, the Voting File gets backed up automatically into the OCR.

Query Voting Disk location

# $GRID_HOME/bin/crsctl query css votedisk

Note: You cannot create more than 1 voting disk in the same or on another/different Disk group disk when using External Redundancy in 11.2. The rules are as follows:

External = 1 voting disk

Normal= 3 voting disk

High= 5 voting disk

Step #5 Stop & start the cluster

Shutdown CRS : CRS is running in exclusive mode, it needs to be shutdown (Root User).

# crsctl stop crs -f

Start CRS : Start the CRS in one node, if everything is ok then start the CRS in other nodes (root user).

# crsctl start crs

CRS Status : Once it is start, you can check the status of the CRS(Root / Oracle user)

# crsctl stat res -t -init      : if you are checking for one node

# crsctl check cluster -all  : if you are checking for entire cluster.

Important Tips

Oracle Clusterware 11g Release 2 backs up the OCR automatically every four hours on a schedule that is dependent on when the node started

•    4-hour backups (3 max) –backup00.ocr, backup01.ocr, and backup02.ocr.

•    Daily backups (2 max) – day.ocr and day_.ocr

•    Weekly backups (2 max) – week.ocr and week_.ocr

You can use the ocrconfig command to view the current OCR backups as seen in this

Ocrconfig -showbackup auto

Note: automatic backups will not occur, when the cluster is down

Verifying OCR integrity of all of the cluster nodes by running the following CVU command:

$ cluvfy comp ocr -n all -verbose

使用Oracle Virtual Box 安裝Oracle RAC的disk 環境設定

http://oracle-base.com/articles/12c/oracle-db-12cr1-rac-installation-on-oracle-linux-6-using-virtualbox.php#create_shared_disks

--Create Disks
"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" createhd --filename ocr.vdi --size 256 --format VDI --variant Fixed
"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" createhd --filename voting.vdi --size 256 --format VDI --variant Fixed
"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" createhd --filename asmdisk1.vdi --size 4096 --format VDI --variant Fixed
"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" createhd --filename asmdisk2.vdi --size 4096 --format VDI --variant Fixed

--Attach Disks to RAC1
"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" storageattach RAC1 --storagectl "SATA" --port 1 --device 0 --type hdd --medium ocr.vdi --mtype shareable
"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" storageattach RAC1 --storagectl "SATA" --port 1 --device 0 --type hdd --medium voting.vdi --mtype shareable
"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" storageattach RAC1 --storagectl "SATA" --port 1 --device 0 --type hdd --medium asmdisk1.vdi --mtype shareable
"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" storageattach RAC1 --storagectl "SATA" --port 1 --device 0 --type hdd --medium asmdisk2.vdi --mtype shareable

--Set disks to shareable
"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" modifyhd ocr.vdi --type shareable

"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" modifyhd voting.vdi --type shareable

"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" modifyhd asmdisk1.vdi --type shareable

"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" modifyhd asmdisk2.vdi --type shareable

--Attach Disks to RAC2
"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" storageattach RAC2 --storagectl "SATA" --port 1 --device 0 --type hdd --medium ocr.vdi --mtype shareable
"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" storageattach RAC2 --storagectl "SATA" --port 1 --device 0 --type hdd --medium voting.vdi --mtype shareable
"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" storageattach RAC2 --storagectl "SATA" --port 1 --device 0 --type hdd --medium asmdisk1.vdi --mtype shareable
"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" storageattach RAC2 --storagectl "SATA" --port 1 --device 0 --type hdd --medium asmdisk2.vdi --mtype shareable


P.S.
如果要resize 某硬碟至30GB (thin provision)
"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" modifyhd G:\RAC1\RAC1.vdi --resize 30720

"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" modifyhd G:\RAC2\RAC2.vdi --resize 30720

如果要複製虛擬機:
"C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" clonehd G:\RAC2\RAC2.vdi G:\RAC1\RAC1.vdi

[11g RAC] Rellocate scan listener still failed : TNS-12541

Ref:
http://heliosguneserol.wordpress.com/2012/10/19/how-to-relocate-scan_listener-from-one-node-to-another-node-on-rac-system/

透過host-vip,1521 port可以連至DB,
但透過scan-ip, 1521 port就連接失敗, 感覺跟上面連結的文章描述有點像, 於是想到有幾個方法
1.stop listener, start listener
==>無效
2.stop listener, stop scan vip, start scan vip, start listener
==>無效
3.relocate three scan listener (考量有可能scan ip 從node1 transfer到node2, 但listener 於各nodes運作正常)
  As grid:
  srvctl relocate scan -i 1
  srvctl relocate scan -i 2
  srvctl relocate scan -i 3

大概觀察了一分鐘, 再用rac-scan 去連接, 結果還是不行

最後才發現, 是scan listener log 達到4gb 大小, 真是傻眼...

看來越簡單的細節, 我們越容易忽略...Sigh.....

==============================================

[grid@rac2 ~]$ps -ef|grep tnsl
oracle    5830     1  0 12:38 ?        00:00:00 /u01/app//grid/grid112/bin/tnslsnr LISTENER_SCAN3 -inherit
oracle    5833     1  0 12:38 ?        00:00:00 /u01/app//grid/grid112/bin/tnslsnr LISTENER_SCAN2 -inherit
oracle    5836     1  0 12:38 ?        00:00:00 /u01/app//grid/grid112/bin/tnslsnr LISTENER -inherit
oracle    7423  7372  0 15:14 pts/1    00:00:00 grep tnsl
[grid@rac2 ~]$

[grid@rac2 ~]$lsnrctl status LISTENER_SCAN2

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-DEC-2011 15:14:04

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN2
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                15-DEC-2011 12:38:45
Uptime                    0 days 2 hr. 35 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/grid/grid112/network/admin/listener.ora
Listener Log File         /u01/app/grid/grid112/log/diag/tnslsnr/rac2/listener_scan2/alert/log.xml


ls -lh /u01/app//grid/grid112/log/diag/tnslsnr/rac2/listener_scan2/trace/*.log   ==> 4GB

=============================================

HP UX syslog "Rebooting for cluster integrity" powerpath

安裝Oracle RAC 之前, 如果沒先處理好powerpath 等等的multi-path 軟體問題,

可能在dbca 之後一小時內會發生node reboot情形

以下連結供參考Ref:

https://forums.oracle.com/thread/927929

https://forums.oracle.com/thread/993066

https://forums.oracle.com/thread/581397

最後查到這篇才證實了我的猜測

==> 294430.1

LONG LATENCIES TO THE VOTING DISKS : EMC PowerPath path error detection and I/O repost and redirect greater than default misscount 

The most common problems relate to multi-path IO software drivers, and the reconfiguration times resulting from a failure in the IO path.

Hardware and (re)configuration issues that introduce these latencies should be corrected.

Reboot原因說明如下

Disk LUN 在I/O重新導向的狀況下...只要符合以下任一條件, 就會node reboot

Takes more than Disktimeout seconds (200 sec) or

Takes more than Misscount Seconds (30 sec)

* By default Misscount is less than Disktimeout seconds

只有在網路互ping超過misscount或Voting disk超過disktimeout時機器才會reboot.

misscount :   Default Value is 60 Sec (Linux) and 30 Sec in Unix platform

disktimeout : Default Value is 200. (Disk IO)

Oracle 10g/11g RAC Service names ---to avoid dead lock

提供service_name : crmsrv 以避免deadlock 情形:

讓orcl1 為active , orcl2為standby instance for Service

srvctl add service -d orcl -s service_test -r orcl1 -a orcl2

srvctl start service -d orcl -s service_test

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

順道一提~ 如果2 node Oracle RAC只要提供一個instance service :

設定方法如下

Oracle RAC (2 nodes) supports active-passive mode enabling 1 node to "do the work"

whilst node 2 remains comparatively idle.

During a failure of the active database instance, users failover to the passive instance.

This feature can be configured by setting the following database initialisation

parameter to 1 on both instances:

active_instance_count = 1 

Oracle 10g RAC -- node 1 毀損重新加入之步驟

0.Prerequisite:

 Configure /etc/hosts

 Configure ssh : 設定ssh 對等性

 Install OCFS rpm

 Configure /etc/fstab : OCFS filesystem

 Configure IP/Network : 網卡bonding ... etc

 Configure OS kernel : /etc/sysctl.conf

1.Create user , group

2.Verify if db1 env is ok

  執行cluvfy 工具

  ./cluvfy stage -pre -n db1 -r 10gR2 -verbose

3.如果有ASM,刪除ASM instance,

  $srvctl remove asm -n db1;

4.移除clusterware resource

   srvctl stop nodeapps -n db1

   srvctl remove nodeapps -n db1

5.在db2上執行updateNodeList script 更新DB資訊

  /oracle/db102/oui/bin/runInstaller -updateNodeList -noClusterEnabled ORACLE_HOME=/oracle/db102 CLUSTER_NODES=db2 CRS=false "INVENTORY_LOCATION=/oracle/oraInventory" LOCAL_NODE=db2

6.在db2上執行updateNodeList script更新CRS資訊

/oracle/crs102/oui/bin/runInstaller -updateNodeList -noClusterEnabled ORACLE_HOME=/oracle/crs102 "CLUSTER_NODES=db2" CRS=TRUE "INVENTORY_LOCATION=/oracle/oraInventory" LOCAL_NODE=db2

7.在db2上執行rootdeletenode.sh script

  /oracle/crs102/bin/olsnodes -n -i

  As root :

      /oracle/crs102/install/rootdeletenode.sh db1,1;

8.檢查是否已刪除Clusterware resource

  As root:

  [root@db2 bin]# ./crs_stat  |grep -i db1

  NAME=ora.db1.LISTENER_db1.lsnr

  NAME=ora.db1.vip

  [root@db2 bin]# ./crs_unregister ora.db1.LISTENER_db1.lsnr

  [root@db2 bin]# ./crs_unregister ora.db1.vip

  [root@db2 bin]# ./crs_stat  |grep -i db1

  檢查DB節點資訊

  As root:

  /oracle/crs102/bin/olsnodes -n

9.在任意保留節點中執行racgons命令，刪除ONS設定：

  [oracle@db2 orcl]$ /oracle/crs102/bin/racgons remove_config db1:6200

  racgons: Existing key value on db1 = 6200.

  racgons: db1:6200 removed from OCR.

10.Clone CRS HOME

   在db2,以oracle用戶進入$CRS_HOME/oui/bin目錄,執行addNode.sh

  

12.Configure the New ONS

  cd /oracle/crs102/bin

  ./racgons add_config db1:6200 db2:6200

13.Clone ORA_HOME

  在db2,以oracle用戶進入$ORACLE_HOME/oui/bin目錄,執行addNode.sh

14.新增db1 listener

  在db1上運行netca,選擇cluster database,按步驟設定

15.在db2上新增新的instance

  先選擇Cluster database...然後instance management然後add an instance然後...

最後檢查

[oracle@db1 log]$ olsnodes -n

db2  2

db1  3

As Oracle:

以crs_stat -t 查詢resource 狀態 , 確定皆已online.

Oracle 10g RAC stop resources SOP

srvctl start nodeapps -n rac1
srvctl start nodeapps -n rac2
srvctl start asm -n rac1
srvctl start asm -n rac2
srvctl start database -d devdb
srvctl start service -d devdb
crs_stat -t



srvctl stop service -d devdb
srvctl stop database -d devdb -o immediate
srvctl stop asm -n rac1
srvctl stop asm -n rac2
srvctl stop nodeapps -n rac1
srvctl stop nodeapps -n rac2
crs_stat -t

當然 也可以使用
crsctl stop crs
crsctl start crs
去做某個DB節點的開關

Aix Oracle 10.2.0.4 RAC ORA-00240: control file enqueue held for more than 120 seconds

https://odenysenko.wordpress.com/2009/06/25/asm-performance-with-huge-number-of-files/#more-11

    由於沒太多跡象顯示10g 上有類似的問題 , 從oracle support 看到11g 新增ASM空間有可能因為此BUG 造成DB hang 住 , 考量該bug , 目前先朝 Tuning ASM instance 方向處理.
    調整後 已正常運作一陣子.

後記與分析 :

發現redo log switch 時, archiver arc0 發生了 KSV master wait 事件 , 也意指

在 ASM file metadata operation 過程中 , 遇到該問題 , 這一段相關的記憶體為

ASM 的db_cache_size , 故需依照Oracle 建議將其放大

以下為IBM Tuning ASM 文件

http://www.filibeto.org/~aduritz/truetrue/oracle/aix/impl-oracle-10gRAC-asm-aix.pdf

Oracle Parameters

The following parameters are suggested in the document Oracle Database 10g Release 2

Automatic Storage Management Overview and Technical Best Practices:

Parameters to be included in the spfile for databases using ASM:

1. Increase Processes by 16

2. Increase Large_Pool by 600k

3. Increase Shared Pool by (1M per 100GB of usable space) +

2M (assumes external redundancy is chosen)

Parameters to be included in the spfile of the ASM instances:

1. ASM_POWER_LIMIT=1

The Best Practices guide suggests this value to make ASM rebalance operations a low

priority; however, this can potentially cause rebalance operations to occur during peak

volume times. This parameter can also be set to 0 to prevent accidental rebalance

operations from occurring during peak loads, which may be preferable for many

environments. This parameter can be raised specifically when rebalancing is desired.

2. Processes=25 + 15n, where “n” is the number of databases which will use ASM.

==>

tuning 建議計算如下:

vi /oracle/admin/+ASM/pfile

processes=70 (有三個instances , test , pdsms , RMAN , processes=25+ 15*3 =70 )

large_pool_size=60M

shared_pool_size=64M ( ASM total disk size : 6.2TB ==> shared pool : 62MB + 2MB=64MB)

ASM_POWER_LIMIT=1 ==> default value

以下為Oracle 原廠tuning 建議:

Oracle Database 11g Release 1 and 10g Release 2

Automatic Storage Management Overview and Technical Best Practices (ASM_11gR1_BestPractices_v34.pdf)

Processes = 25 + (10 + [max number of concurrent database file creations, and file extend operations possible] )*n

The number n is the number of databases connecting to ASM

processes= 25 + (10 + 80) * 2 =205 , 80 由datafiles 數量 加總 logfile 數量得來 , 假如你的環境只有10 個datafile 跟 6 個redo log , 可將80 替換為 16。

shared_pool_size=128M

large_pool_size=12M

db_cache_size=64M