Setting up an Oracle 11G R2 Real Application Cluster (RAC) using Single Client Access Name (scan IP)
<<Setup of Grid Infrastructure
Testing and Troubleshooting
Test whether the Real Application Cluster has been properly setup by using the following applications /u01/app/11.2.0/grid/bin .
|
1 |
crsctl status resource -w "TYPE co 'ora'" -t |
srvctl config scan
srvctl config scan_listener
srvctl config database -d instance_ismi
srvctl status database -d instance_ismi
crsctl check crs
crsctl stat res -t
crsctl stat res -t init
crs_stat -t
srvctl status asm
srvctl config asm -n node_name
crsctl status serverpool
crsctl status resource
crsctl check cluster -all
crsctl query css votedisk
cluvfy comp clocksync -n all -verbose
cluvfy comp clocksync
Logs
|
1 |
tail -1000f /u01/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log |
tail -1000f /u01/11.2.0/grid/log/$HOSTNAME/ohasd/ohasd.log
tail -1000f /u01/11.2.0/grid/log/$HOSTNAME/alert$HOSTNAME.log
tail -1000f /u01/11.2.0/grid/log/$HOSTNAME/agent/ohasd/oraagent_grid/oraagent_grid.log
tail -1000f /u01/11.2.0/grid/log/$HOSTNAME/crsd/crsd.log
tail -1000f /u01/11.2.0/grid/log/$HOSTNAME/agent/ohasd/orarootagent_root/orarootagent_root.log
tail -1000f /u01/11.2.0/grid/log/$HOSTNAME/cssd/ocssd.log
tail -2000f /u01/11.2.0/grid/log/$HOSTNAME/gpnpd/gpnpd.log
|
1 |
cd /u01/app/oracle/diag/rdbms/rac/rac1/trace |
|
1 2 3 |
SELECT inst_name FROM v$active_instances; select INSTANCE_NAME from GV$ASM_CLIENT; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 |
[root@rac2 bin] $ ./crsctl status resource -w "TYPE co 'ora'" -t -------------------------------------------------------------------------------- NAME TARGET STATE SERVER STATE_DETAILS -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.DATA.dg ONLINE ONLINE rac1 ONLINE ONLINE rac2 ora.LISTENER.lsnr ONLINE ONLINE rac1 ONLINE ONLINE rac2 ora.asm ONLINE ONLINE rac1 Started ONLINE ONLINE rac2 Started ora.eons ONLINE ONLINE rac1 ONLINE ONLINE rac2 ora.gsd OFFLINE OFFLINE rac1 OFFLINE OFFLINE rac2 ora.net1.network ONLINE ONLINE rac1 ONLINE ONLINE rac2 ora.ons ONLINE ONLINE rac1 ONLINE ONLINE rac2 -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE rac2 ora.LISTENER_SCAN2.lsnr 1 ONLINE ONLINE rac1 ora.LISTENER_SCAN3.lsnr 1 ONLINE ONLINE rac1 ora.oc4j 1 OFFLINE OFFLINE ora.rac.db 1 ONLINE ONLINE rac1 Open 2 ONLINE ONLINE rac2 Open ora.rac1.vip 1 ONLINE ONLINE rac1 ora.rac2.vip 1 ONLINE ONLINE rac2 ora.scan1.vip 1 ONLINE ONLINE rac2 ora.scan2.vip 1 ONLINE ONLINE rac1 ora.scan3.vip 1 ONLINE ONLINE rac1 |
Connecting
Operating system: Win7 x64
Oracle client: 11.2.0.1.0
tnsnames.ora
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
RAC = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.sysdba.org)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = rac.sysdba.org) ) ) |
|
1 |
C:Usersmehmet>sqlplus sys/PASSWORD@rac as sysdba |
The error output
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 15 15:50:14 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. ERROR: ORA-12537: TNS:connection closed Enter user-name: ERROR: ORA-12560: TNS:protocol adapter error Enter user-name: ERROR: ORA-12560: TNS:protocol adapter error SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus |
Solution 1.
The hosts file in C:WindowsSystem32driversetc.
|
1 2 3 |
192.168.137.20 rac1.sysdba.org rac1 192.168.137.21 rac2.sysdba.org rac2 |
In the tcp/ip section of the public ethernet card properties:
advanced ->DNS-> Append these DNS suffixes
Enter the domain name, in this example its sysdba.org
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
C:Usersmehmet>sqlplus sys/password@rac as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 15 16:10:18 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options |
Solution 2.
Upgrading to 11.2.0.2 by installing a patch.
Source: ORA-12545 or ORA-12537 While Connecting to RAC through SCAN name [ID 970619.1]
*******************************************************
RAM Settings
|
1 |
create pfile='/tmp/pfileSID_ISMI.txt' from spfile --spfile ın yedeğini alıyoruz. |
alter system set memory_target=20G scope=spfile sid=’*’;
alter system set sga_max_size=0 scope=spfile sid=’*’;
alter system set sga_target=0 scope=spfile sid=’*’;
alter system set pga_aggregate_target=0 scope=spfile sid=’*’;
*******************************************************
The voting disks are in the OCR_VOTE asm disk group, these will be moved to the OCR_VOT disk group.
|
1 |
[root@dba01 ~] $ crsctl query css votedisk |
## STATE File Universal Id File Name Disk group
— —– —————– ——— ———
1. ONLINE 116eee3911a94fd4bfdc32072f5178c5 (/dev/ASM/OCR_VOTE01) [OCR_VOTE]
2. ONLINE 2e7b89d8e3bf4ffcbf079de552f7e828 (/dev/ASM/OCR_VOTE02) [OCR_VOTE]
3. ONLINE 8e0516586f604f04bf73f2ee9be47925 (/dev/ASM/OCR_VOTE03) [OCR_VOTE]
Located 3 voting disk(s).
|
1 2 3 4 5 6 7 8 9 10 11 |
ASMCMD> lsdg State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name MOUNTED NORMAL N 512 4096 1048576 30717 23631 10239 6696 0 N DATA/ MOUNTED NORMAL N 512 4096 1048576 20478 10512 0 5256 0 N FRA/ MOUNTED NORMAL N 512 4096 1048576 3069 2916 1023 946 0 N OCR_VOT/ MOUNTED NORMAL N 512 4096 1048576 30717 29791 10239 9776 0 Y OCR_VOTE/ |
|
1 |
crsctl replace votedisk +OCR_VOT |
|
1 2 3 4 5 6 7 |
[root@dba01 bin] $ crsctl replace votedisk +OCR_VOT Failed to create voting files on disk group OCR_VOT. Change to configuration failed but was successfully rolled back. CRS-4000: Command Replace failed, or completed with errors. |
There wasn’t any useful information in the grid alert logs, however reviewing the asm log:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
tail -1000f /u01/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log |more NOTE: Creating voting files in diskgroup OCR_VOT Wed Jul 03 10:49:13 2013 NOTE: Voting File refresh pending for group 4/0x7bd855db (OCR_VOT) NOTE: Attempting voting file creation in diskgroup OCR_VOT ERROR: Voting file allocation failed for group OCR_VOT Errors in file /u01/app/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_ora_11164.trc: |
The error message in the trc file:
|
1 |
ORA-15221: ASM operation requires compatible.asm of 11.2.0.0.0 or higher |
Checking the OCR_VOT version:
|
1 2 3 4 5 6 7 8 9 10 11 |
set linesize 100 select inst_id, name, state, type, free_mb, substr(compatibility,1,10) compatibility from gv$asm_diskgroup where name='OCR_VOT' order by name,inst_id; INST_ID NAME STATE TYPE FREE_MB COMPATIBIL ---------- ------------------------------ ----------- ------ ---------- ---------- 1 OCR_VOT MOUNTED NORMAL 2790 10.1.0.0.0 2 OCR_VOT MOUNTED NORMAL 2790 10.1.0.0.0 |
Its still on version 10.1.0.0.0, so needs to be updated so that its the same version as the other disk groups.
As a grid user:
|
1 |
sqlplus / as sysadm |
ALTER DISKGROUP OCR_VOT SET ATTRIBUTE ‘compatible.asm’ = ‘11.2’;
To check
|
1 2 3 4 5 6 7 8 9 |
select inst_id, name, state, type, free_mb, substr(compatibility,1,10) compatibility from gv$asm_diskgroup where name='OCR_VOT' order by name,inst_id; INST_ID NAME STATE TYPE FREE_MB COMPATIBIL ---------- ------------------------------ ----------- ------ ---------- ---------- 1 OCR_VOT MOUNTED NORMAL 2784 11.2.0.0.0 2 OCR_VOT MOUNTED NORMAL 2784 11.2.0.0.0 |
Testing again
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
[root@dba01 bin] $ crsctl replace votedisk +OCR_VOT Successful addition of voting disk 32b87db627a24f8bbfd2ce9df33a2c85. Successful addition of voting disk 45f34d59c03b4f4dbfb924a06451f105. Successful addition of voting disk 97d43d268c334f83bf8e757c0d869606. Successful deletion of voting disk 116eee3911a94fd4bfdc32072f5178c5. Successful deletion of voting disk 2e7b89d8e3bf4ffcbf079de552f7e828. Successful deletion of voting disk 8e0516586f604f04bf73f2ee9be47925. Successfully replaced voting disk group with +OCR_VOT. CRS-4266: Voting file(s) successfully replaced |
Checking
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[root@dba01 bin] $ crsctl query css votedisk ## STATE File Universal Id File Name Disk group -- ----- ----------------- --------- --------- 1. ONLINE 32b87db627a24f8bbfd2ce9df33a2c85 (/dev/ASM/OCR_VOTE04) [OCR_VOT] 2. ONLINE 45f34d59c03b4f4dbfb924a06451f105 (/dev/ASM/OCR_VOTE05) [OCR_VOT] 3. ONLINE 97d43d268c334f83bf8e757c0d869606 (/dev/ASM/OCR_VOTE06) [OCR_VOT] This shows that the voting disks are now in the OCR_VOT disk group. The OCR is present in OCR_VOTE, DATA and FRA. Lets create them in the OCR_VOT group and delete the ones from the OCR_VOTE group. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
[root@dba01 bin] $ ocrcheck Status of Oracle Cluster Registry is as follows : Version : 3 Total space (kbytes) : 262120 Used space (kbytes) : 3268 Available space (kbytes) : 258852 ID : 538730918 Device/File Name : +OCR_VOTE Device/File integrity check succeeded Device/File Name : +DATA Device/File integrity check succeeded Device/File Name : +fra Device/File integrity check succeeded Device/File not configured Device/File not configured Cluster registry integrity check succeeded Logical corruption check succeeded |
|
1 2 3 4 5 6 7 8 9 |
[root@dba01 bin] $ cat /etc/oracle/ocr.loc #Device/file getting replaced by device +fra ocrconfig_loc =+OCR_VOTE ocrmirrorconfig_loc =+DATA ocrconfig_loc3 =+fra |
|
1 |
ocrconfig -add +OCR_VOT |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[root@dba01 bin] $ cat /etc/oracle/ocr.loc #Device/file getting replaced by device +OCR_VOT ocrconfig_loc =+OCR_VOTE ocrmirrorconfig_loc =+DATA ocrconfig_loc3 =+fra ocrconfig_loc4 =+OCR_VOT local_only =false |
|
1 |
ocrconfig -delete +OCR_VOTE |
|
1 2 3 4 5 6 7 8 9 10 11 |
[root@dba01 bin] $ cat /etc/oracle/ocr.loc #Device/file +OCR_VOTE getting replaced by device +DATA ocrconfig_loc =+DATA ocrmirrorconfig_loc =+fra ocrconfig_loc3 =+OCR_VOT |
|
1 2 3 4 5 6 7 8 9 |
[root@dba01 ~] $ ocrcheck -config Oracle Cluster Registry configuration is : Device/File Name : +DATA Device/File Name : +fra Device/File Name : +OCR_VOT |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
[grid@dba01 ~] $ cluvfy comp ocr -n all Verifying OCR integrity Checking OCR integrity... Checking the absence of a non-clustered configuration... All nodes free of non-clustered, local-only configurations ASM Running check passed. ASM is running on all specified nodes Checking OCR config file "/etc/oracle/ocr.loc"... OCR config file "/etc/oracle/ocr.loc" check successful Disk group for ocr location "+DATA" available on all the nodes Disk group for ocr location "+fra" available on all the nodes Disk group for ocr location "+OCR_VOT" available on all the nodes NOTE: This check does not verify the integrity of the OCR contents. Execute 'ocrcheck' as a privileged user to verify the contents of OCR. OCR integrity check passed Verification of OCR integrity was successful. |
********************************
<tab=Services>
srvctl add service
-d my_db_name
-s my_service_name
-r my_active_instance
-a my_passive_instance
-P BASIC
-e SELECT
-y AUTOMATIC
-z 180
-w 5
|
1 |
srvctl start/stop/status service -d my_db_name -s my_service_name |
Adding the service as Oracle_home’s owner or “oracle”
node_2 is used as a backup in case node_1 has problems (instance name: fugu, node_1=fugu1, node_2=fugu2)
|
1 |
srvctl add service -d fugu -s fugu_service -r fugu1 -a fugu2 -P BASIC -e SELECT -y AUTOMATIC -z 180 -w 5 |
rvctl start service -d fugu -s fugu_service
To retrieve information
|
1 |
srvctl status service −d datamart −s datamart_service |
srvctl config service -d fugu -s fugu_service
Updating the client’s tnsnames.ora
|
1 |
fugu_ser = |
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = raca-scan.sysdba.org)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = fugu_service)
)
)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
C:WindowsSystem32>sqlplus sys/oracle@fugu_ser as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Paz Mar 3 16:46:55 2013 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP Data Mining and Real Application Testing options |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SQL> show parameter name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string db_name string fugu db_unique_name string fugu global_names boolean FALSE instance_name string fugu1 lock_name_space string log_file_name_convert string processor_group_name string service_names string fugu_service |
Viewing the fugu service in the listener
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
lsnrctl status Service "+ASM" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "fugu" has 1 instance(s). Instance "fugu1", status READY, has 1 handler(s) for this service... Service "fuguXDB" has 1 instance(s). Instance "fugu1", status READY, has 1 handler(s) for this service... Service "fugu_service" has 1 instance(s). Instance "fugu1", status READY, has 1 handler(s) for this service... Service "nija" has 1 instance(s). Instance "nija1", status READY, has 1 handler(s) for this service... Service "nijaXDB" has 1 instance(s). Instance "nija1", status READY, has 1 handler(s) for this service... The command completed successfully |
************************
ora.dba02.vip FAILED OVER
|
1 2 3 4 5 |
crsctl status resource ora.dba02.vip crsctl stop resource ora.dba02.vip crsctl start resource ora.dba02.vip |
Useful resources for further reference:
1-OS Watcher Black Box User Guide (Doc ID 301137.1)
2-RAC and Oracle Clusterware Best Practices and Starter Kit (Platform Independent) (Doc ID 810394.1)
3-RACcheck – RAC Configuration Audit Tool [ID 1268927.1]
4-Top 11 Things to do NOW to Stabilize your RAC Cluster Environment [ID 1344678.1] To BottomTo Bottom
5-RAC and Oracle Clusterware Best Practices and Starter Kit (Solaris) (Doc ID 811280.1)
6-Grid Infrastructure Redundant Interconnect and ora.cluster_interconnect.haip (Doc ID 1210883.1)
7-The Basics of IPv4 Subnet and Oracle Clusterware (Doc ID 1386709.1)
8-Frequently Node Reboots, There Is Nothing In The Logs (Doc ID 437420.1)
9-CRS Diagnostic Data Gathering: A Summary of Common tools and their Usage (Doc ID 783456.1)
