Posts Tagged 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 ūüôā

,

3 Comments

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!”

, , ,

5 Comments

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.

, , ,

13 Comments

%d bloggers like this: