Beiträge getaggt mit RAC
Who is the Master Node in my Oracle Cluster?
I got this question during my present course Oracle Grid Infrastructure 11g: Manage Clusterware and ASM while we discussed backup of the OCR. That backup is done by the OCR Master, which can be any node in the cluster. It is therefore recommended to configure the backup location to a shared folder that is accessible from all cluster nodes. But back to the question – here is how to find the OCR Master of the Oracle Cluster:
[grid@host01 ~]$ cat /u01/app/11.2.0/grid/log/host01/crsd/crsd.log | grep -i 'ocr master' 2013-09-17 07:49:44.237: [ OCRMAS][3014282128]th_master:12: I AM THE NEW OCR MASTER at incar 1. Node Number 1 2013-09-17 07:53:00.305: [ OCRMAS][3009534864]th_master:12: I AM THE NEW OCR MASTER at incar 1. Node Number 1 2013-09-17 12:19:21.414: [ OCRMAS][3009604496]th_master: NEW OCR MASTER IS 2 [grid@host01 ~]$ olsnodes -n host01 1 host02 2 host03 3
(GRID_HOME and local hostname) The CRSD (Cluster Registry Service Demon) is the one who deals with the OCR, which is why I search through this log file.
The Master Node of the Cluster is the one who will become the only surviving node if the interconnect fails completely. It is also the node who will pass the time to the other nodes in the absence of NTP via CTSSD (Cluster Time Synchronization Service Demon). I suppose it is always the same as the OCR Master, but just to be on the safe side, you can check that this way:
[grid@host01 ~]$ cat /u01/app/11.2.0/grid/log/host01/cssd/ocssd.log | grep -i 'master node' 2013-09-17 07:48:53.541: [ CSSD][3004672912]clssgmCMReconfig: reconfiguration successful, incarnation 274866533 with 1 nodes, local node number 1, master node number 1 2013-09-17 07:49:47.427: [ CSSD][2990480272]clssgmCMReconfig: reconfiguration successful, incarnation 274866534 with 1 nodes, local node number 1, master node number 1 2013-09-17 07:52:27.595: [ CSSD][2989472656]clssgmCMReconfig: reconfiguration successful, incarnation 274866536 with 1 nodes, local node number 1, master node number 1 2013-09-17 07:59:20.783: [ CSSD][2989472656]clssgmCMReconfig: reconfiguration successful, incarnation 274866537 with 2 nodes, local node number 1, master node number 1 2013-09-17 11:34:59.157: [ CSSD][2989472656]clssgmCMReconfig: reconfiguration successful, incarnation 274866538 with 3 nodes, local node number 1, master node number 1 2013-09-17 12:18:48.885: [ CSSD][2992602000]clssgmCMReconfig: reconfiguration successful, incarnation 274866540 with 3 nodes, local node number 1, master node number 2 2013-09-17 12:22:52.660: [ CSSD][2992602000]clssgmCMReconfig: reconfiguration successful, incarnation 274866541 with 2 nodes, local node number 1, master node number 2 2013-09-17 12:23:32.836: [ CSSD][2992602000]clssgmCMReconfig: reconfiguration successful, incarnation 274866542 with 3 nodes, local node number 1, master node number 2 2013-09-17 12:26:29.474: [ CSSD][3016432528]clssgmCMReconfig: reconfiguration successful, incarnation 274866543 with 3 nodes, local node number 1, master node number 2 2013-09-17 12:28:42.960: [ CSSD][2987871120]clssgmCMReconfig: reconfiguration successful, incarnation 274866544 with 3 nodes, local node number 1, master node number 2
The CSSD (Cluster Synchronization Service Demon) is the one who deals with the Voting File that is used to determine which nodes must reboot and which nodes will survive in case of a problem with the Interconnect. Therefore, I search through its log file to determine the Master of the Cluster.
The question comes up quite often, so this little post will be handy to point to in the future. Hope you find it useful as well 🙂
Drop an ASM Disk that contains a Voting Disk?
That was a question I got during my present Oracle 11gR2 RAC accelerated course in Duesseldorf: What happens if we drop an ASM Disk that contains a Voting Disk? My answer was: „I suppose that is not allowed“ but my motto is „Don’t believe it, test it!“ and that is what I did. That is actually one of the good things about doing a course at Oracle University: We can just check out things without affecting critical production systems here in our course environment:
[grid@host01 ~]$ crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 48d3710843274f88bf1eb9b3b5129a7d (ORCL:ASMDISK01) [DATA]
2. ONLINE 354cfa8376364fd2bfaa1921534fe23b (ORCL:ASMDISK02) [DATA]
3. ONLINE 762ad94a98554fdcbf4ba5130ac0384c (ORCL:ASMDISK03) [DATA]
Located 3 voting disk(s).
We are on 11.2.0.1 here. The Voting Disk being part of an ASM Diskgroup was an 11gR2 New Feature that I introduced in this posting already. Now let’s try to drop ASMDISK01:
[grid@host01 ~]$ sqlplus / as sysasm SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 13 17:18:21 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Real Application Clusters and Automatic Storage Management options SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production SQL> select name,group_number from v$asm_diskgroup; NAME GROUP_NUMBER ------------------------------ ------------ DATA 1 ACFS 2 FRA 3 SQL> select name from v$asm_disk where group_number=1; NAME ------------------------------ ASMDISK01 ASMDISK02 ASMDISK03 ASMDISK04 SQL> alter diskgroup data drop disk 'ASMDISK01'; Diskgroup altered.
It just did it without error message! We look further:
SQL> select name from v$asm_disk where group_number=1; NAME ------------------------------ ASMDISK02 ASMDISK03 ASMDISK04 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Real Application Clusters and Automatic Storage Management options [grid@host01 ~]$ crsctl query css votedisk ## STATE File Universal Id File Name Disk group -- ----- ----------------- --------- --------- 1. ONLINE 354cfa8376364fd2bfaa1921534fe23b (ORCL:ASMDISK02) [DATA] 2. ONLINE 762ad94a98554fdcbf4ba5130ac0384c (ORCL:ASMDISK03) [DATA] 3. ONLINE 3f0bf16b6eb64f3cbf440a3c2f0da2fd (ORCL:ASMDISK04) [DATA] Located 3 voting disk(s).
It just moved the Voting Disk silently to another ASM Disk of that Diskgroup. When I try to drop another ASM Disk from that Diskgroup, the command seems to be silently ignored, because 3 ASM Disks are required here to keep the 3 Voting Disks. Similar behavior with External Redundancy:
[grid@host01 ~]$ 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 EXTERN N 512 4096 1048576 9788 9645 0 9645 0 N ACFS/ MOUNTED NORMAL N 512 4096 1048576 7341 6431 438 2996 0 N DATA/ MOUNTED EXTERN N 512 4096 1048576 4894 4755 0 4755 0 N FRA/
I will move the Voting Disk to the FRA Diskgroup. It is a bug of 11.2.0.1 that the Voting_files flag is not Y for the DATA Diskgroup here, by the way.
[grid@host01 ~]$ sudo crsctl replace votedisk +FRA Successful addition of voting disk 4d586fbecf664f8abf01d272a354fa67. Successful deletion of voting disk 354cfa8376364fd2bfaa1921534fe23b. Successful deletion of voting disk 762ad94a98554fdcbf4ba5130ac0384c. Successful deletion of voting disk 3f0bf16b6eb64f3cbf440a3c2f0da2fd. Successfully replaced voting disk group with +FRA. CRS-4266: Voting file(s) successfully replaced [grid@host01 ~]$ crsctl query css votedisk ## STATE File Universal Id File Name Disk group -- ----- ----------------- --------- --------- 1. ONLINE 4d586fbecf664f8abf01d272a354fa67 (ORCL:ASMDISK10) [FRA] Located 1 voting disk(s). [grid@host01 ~]$ sqlplus / as sysasm SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 13 17:36:06 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Real Application Clusters and Automatic Storage Management options SQL> alter diskgroup fra drop disk 'ASMDISK10'; Diskgroup altered. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Real Application Clusters and Automatic Storage Management options [grid@host01 ~]$ crsctl query css votedisk ## STATE File Universal Id File Name Disk group -- ----- ----------------- --------- --------- 1. ONLINE 0b051cf6e6a14ff1bf31ef7bc66098e0 (ORCL:ASMDISK11) [FRA] Located 1 voting disk(s).
Not sure whether I would dare that all in a production system, though 🙂
Conclusion: We can drop ASM Disks that contain Voting Disks as long as there are enough Disks left in the Diskgroup to retain the same number of Voting Disks (each inside a separate Failure Group) afterwards. Apparently – but: „Don’t believe it, test it!“
Monitor RAC I/O with gv$iostat_function
I was delivering an 11gR2 New Features course, followed by an 11gR2 RAC accelerated course. That lead me to combine the not so widely known new view v$iostat_function with the RAC area 🙂
We can now very easy monitor what DB function is doing I/O at what instances in our RAC DB:
SQL> select inst_id,function_name,
sum(small_read_megabytes+large_read_megabytes) as read_mb,
sum(small_write_megabytes+large_write_megabytes) as write_mb
from gv$iostat_function
group by cube (inst_id,function_name)
order by inst_id,function_name;
INST_ID FUNCTION_NAME READ_MB WRITE_MB
---------- ------------------ ---------- ----------
1 ARCH 0 0
1 Archive Manager 0 0
1 Buffer Cache Reads 610 0
1 DBWR 12 373
1 Data Pump 0 0
1 Direct Reads 1162 1
1 Direct Writes 1 167
1 LGWR 1 346
1 Others 5215 2116
1 RMAN 0 0
1 Recovery 0 0
1 Smart Scan 0 0
1 Streams AQ 1 0
1 XDB 0 0
1 7002 3003
2 ARCH 0 0
2 Archive Manager 0 0
2 Buffer Cache Reads 187 0
2 DBWR 11 520
2 Data Pump 0 0
2 Direct Reads 6 0
2 Direct Writes 0 0
2 LGWR 1 299
2 Others 3898 1030
2 RMAN 0 0
2 Recovery 0 0
2 Smart Scan 0 0
2 Streams AQ 1 0
2 XDB 0 0
2 4104 1849
3 ARCH 0 0
3 Archive Manager 0 0
3 Buffer Cache Reads 131 0
3 DBWR 2 79
3 Data Pump 0 0
3 Direct Reads 0 0
3 Direct Writes 0 0
3 LGWR 0 58
3 Others 1140 269
3 RMAN 0 0
3 Recovery 0 0
3 Smart Scan 0 0
3 Streams AQ 0 0
3 XDB 0 0
3 1273 406
ARCH 0 0
Archive Manager 0 0
Buffer Cache Reads 928 0
DBWR 25 972
Data Pump 0 0
Direct Reads 1168 1
Direct Writes 1 167
LGWR 2 703
Others 10253 3415
RMAN 0 0
Recovery 0 0
Smart Scan 0 0
Streams AQ 2 0
XDB 0 0
12379 5258
60 rows selected.
This is a 3 node cluster with not so much I/O done yet. Especially no Data Pump related I/O. We change that:
SQL> create directory dpdir as '+data'; Directory created. SQL> create directory logdir as '/home/oracle/logdir'; Directory created. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options [oracle@host01 ~]$ mkdir /home/oracle/logdir expdp full=y directory=dpdir logfile=logdir:mylog.txt -- Output not shown -- Dump file set for SYS.SYS_EXPORT_FULL_01 is: +DATA/expdat.dmp Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 18:00:33
The dumpfile was created into the ASM diskgroup DATA here, while the logfile was placed in a conventional directory on the local node. When we look into the diskgroup, we see that an alias was created:
ASMCMD> ls data -al
Type Redund Striped Time Sys Name
Y ORCL/
Y cluster01/
N expdat.dmp => +DATA/ORCL/DUMPSET/SYSSYS_EXPORT_FULL_01_74638_1.272.774121815
gv$iostat_function does show that Data Pump related I/O now:
SQL> select inst_id,function_name, sum(small_read_megabytes+large_read_megabytes) as read_mb, sum(small_write_megabytes+large_write_megabytes) as write_mb from gv$iostat_function group by cube (inst_id,function_name) order by inst_id,function_name; INST_ID FUNCTION_NAME READ_MB WRITE_MB ---------- ------------------ ---------- ---------- 1 ARCH 0 0 1 Archive Manager 0 0 1 Buffer Cache Reads 770 0 1 DBWR 14 425 1 Data Pump 795 540 1 Direct Reads 1194 1 1 Direct Writes 1 167 1 LGWR 1 451 1 Others 5297 2131 1 RMAN 0 0 1 Recovery 0 0 1 Smart Scan 0 0 1 Streams AQ 1 0 1 XDB 0 0 1 8073 3715 2 ARCH 0 0 2 Archive Manager 0 0 2 Buffer Cache Reads 191 0 2 DBWR 13 541 2 Data Pump 0 0 2 Direct Reads 6 0 2 Direct Writes 0 0 2 LGWR 1 309 2 Others 3955 1044 2 RMAN 0 0 2 Recovery 0 0 2 Smart Scan 0 0 2 Streams AQ 1 0 2 XDB 0 0 2 4167 1894 3 ARCH 0 0 3 Archive Manager 0 0 3 Buffer Cache Reads 142 0 3 DBWR 4 83 3 Data Pump 0 0 3 Direct Reads 0 0 3 Direct Writes 0 0 3 LGWR 0 68 3 Others 1233 283 3 RMAN 0 0 3 Recovery 0 0 3 Smart Scan 0 0 3 Streams AQ 0 0 3 XDB 0 0 3 1379 434 ARCH 0 0 Archive Manager 0 0 Buffer Cache Reads 1103 0 DBWR 31 1049 Data Pump 795 540 Direct Reads 1200 1 Direct Writes 1 167 LGWR 2 828 Others 10485 3458 RMAN 0 0 Recovery 0 0 Smart Scan 0 0 Streams AQ 2 0 XDB 0 0 13619 6043 60 rows selected.
In this case, all the Data Pump I/O was done by the Instance 1. There is also a relation to Exadata, when you look at the function Smart Scan 🙂
Addendum: The Data Pump export fails if the log file is not redirected out of the ASM diskgroup:
[oracle@host01 ~]$ expdp tables=scott.dept directory=dpdir
Export: Release 11.2.0.1.0 - Production on Thu Feb 2 17:42:37 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
Therefore I used a local directory – which is less elegant than using an ACFS based directory:
SQL> drop directory logdir; Directory dropped. SQL> create directory logdir as '/u01/app/grid/acfsmounts/data_myvol/logdir'; Directory created. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options [oracle@host01 ~]$ expdp tables=scott.dept directory=dpdir logfile=logdir:mylog.txt Export: Release 11.2.0.1.0 - Production on Thu Feb 2 17:06:46 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba -- output not shown -- Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: +DATA/expdat.dmp Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 17:07:26
On Exadata, we may use DBFS instead.
Pickleball spielen auf YouTube
Pickleball spielen 