Identify whether database is RAC or SI using crsctl

July 21, 2020 Leave a comment

Environment:

GI – configured as a cluster
orclsingle – configured as a Single Instance
orclrac – configured as RAC
orclone – configured as RACOneNode

Q1: How to identify whether database is RAC or SI?

A1: crsctl stat res shows parameter CLUSTER_DATABASE. In case of RAC or RACOneNode the value is TRUE otherwise FALSE

# crsctl stat res ora.orclrac.db -p|grep CLUSTER_DATABASE
CLUSTER_DATABASE=true

# crsctl stat res ora.orclone.db -p|grep CLUSTER_DATABASE
CLUSTER_DATABASE=true

# crsctl stat res ora.orclsingle.db -p|grep CLUSTER_DATABASE
CLUSTER_DATABASE=false

Please note that the same can be identified from sqlplus by selecting CLUSTER_DATABASE initialization parameter.

Q2: How to identify whether my database is RAC or RACOneNode?

A2: srvctl config database shows Type, that can have the following values SINGLE, RAC, or RACOneNode.

$ srvctl config database -db orclrac|grep Type
Type: RAC

$ srvctl config database -db orclone|grep Type
Type: RACOneNode

$ srvctl config database -db orclsingle|grep Type
Type: SINGLE

Filed under 19c, RAC Tagged with , , , , , ,

Resize ASM disks in GCP (FG enabled cluster)

July 20, 2020 Leave a comment

Increasing disks in GCP is an online procedure and you don’t have to stop the VM.

1. If the node is a database node, stop all local database instances running on the node.

2. Go to the Disks page -> click the name of the disk that you want to resize -> click Edit -> enter the new size in Size field -> Save.

Please note that all data disks (not quorum disk) must be increased under the same diskgroup, otherwise ASM will not let you to have different sized disks.

Choose another data disks and repeat the same steps.

3. Run the following on database nodes via root user:

# for i in /sys/block/*/device/rescan; do echo 1 > $i; done

4. Check new disk sizes:

If it is Fg cluster, Phys_GiB column must show increased size:

[root@rac1 ~]# flashgrid-dg show -G DATA
...
------------------------------------------------------------
FailGroup ASM_Disk_Name Drive Phys_GiB  ASM_GiB  Status
------------------------------------------------------------
RAC1 RAC1$SHARED_2 /dev/flashgrid/rac1.shared-2 15 10 ONLINE
RAC2 RAC2$SHARED_2 /dev/flashgrid/rac2.shared-2 15 10 ONLINE
RACQ RACQ$SHARED_3 /dev/flashgrid/racq.shared-3 1  1  ONLINE
------------------------------------------------------------

In case it is a normal cluster, OS_MB must show increased size:

# su - grid
$ sqlplus / as sysasm
SQL> select TOTAL_MB/1024,OS_MB/1024 from v$asm_disk where GROUP_NUMBER=2;

TOTAL_MB/1024 OS_MB/1024
------------- ----------
	   10	      15
	   10	      15
	    1	       1

5. Connect to the ASM from any database node and run:

# su - grid
$ sqlplus / as sysasm
SQL> ALTER DISKGROUP DATA RESIZE ALL;

The above command will resize all disks in the specified diskgroup based on their size returned by OS.

6. Check new sizes:

Fg cluster:

[root@rac1 ~]# flashgrid-dg show -G DATA
...
------------------------------------------------------------
RAC1 RAC1$SHARED_2 /dev/flashgrid/rac1.shared-2 15 15 ONLINE
RAC2 RAC2$SHARED_2 /dev/flashgrid/rac2.shared-2 15 15 ONLINE
RACQ RACQ$SHARED_3 /dev/flashgrid/racq.shared-3 1  1  ONLINE
------------------------------------------------------------

Normal cluster:

SQL> select TOTAL_MB/1024,OS_MB/1024 from v$asm_disk where GROUP_NUMBER=2 ;

TOTAL_MB/1024 OS_MB/1024
------------- ----------
	   15	      15
	   15	      15
	    1	       1

Phys_GiB and ASM_GiB should have the same increased size, which means disks are resized and you can use extended space.

Filed under ASM, GCP Tagged with , , , ,

Resize ASM disks in Azure (FG enabled cluster)

July 20, 2020 Leave a comment

1. If the node is a database node, stop all local database instances running on the node.

2. Stop database VM from Azure console. In azure you are not able to resize disks while VM is running, so we need to stop it first.

3. Increase all database disks belonging to the same diskgroup to the desired size. Make sure disks in the same diskgroup have the same sizes.

To resize disk, click VM -> Disks -> choose data disk (in my case 10GB disk is a DATA disk)

After clicking the above disk, you will be redirected to the following screen, choose Configuration -> enter desired disk size (in my case I’ve changed from 10 to 15) -> Save

4. Start the database node.

5. Repeat 1-4 steps for the next database nodes (no need to increase disks for quorum, it is only necessary for the database nodes)

6. Check new disk sizes:

If it is Fg cluster, Phys_GiB column must show increased size:

[root@rac1 ~]# flashgrid-dg show -G DATA
...
------------------------------------------------------------
FailGroup ASM_Disk_Name Drive Phys_GiB  ASM_GiB  Status
------------------------------------------------------------
RAC1    RAC1$LUN2     /dev/flashgrid/rac1.lun2 15  10 ONLINE
RAC2    RAC2$LUN2     /dev/flashgrid/rac2.lun2 15  10 ONLINE
RACQ    RACQ$LUN3     /dev/flashgrid/racq.lun3  1  1  ONLINE
------------------------------------------------------------

In case it is a normal cluster, OS_MB must show increased size:

# su - grid
$ sqlplus / as sysasm
SQL> select TOTAL_MB/1024,OS_MB/1024 from v$asm_disk where GROUP_NUMBER=2 ;

TOTAL_MB/1024 OS_MB/1024
------------- ----------
	   10	      15
	   10	      15
	    1	       1

7. Connect to the ASM from any database node and run:

# su - grid
$ sqlplus / as sysasm
SQL> ALTER DISKGROUP DATA RESIZE ALL;

The above command will resize all disks in the specified diskgroup based on their size returned by OS.

8. Check new sizes:

Fg cluster:

[root@rac1 ~]# flashgrid-dg show -G DATA
...
------------------------------------------------------------
FailGroup ASM_Disk_Name Drive Phys_GiB  ASM_GiB  Status
------------------------------------------------------------
RAC1    RAC1$LUN2     /dev/flashgrid/rac1.lun2 15  15 ONLINE
RAC2    RAC2$LUN2     /dev/flashgrid/rac2.lun2 15  15 ONLINE
RACQ    RACQ$LUN3     /dev/flashgrid/racq.lun3  1  1  ONLINE
------------------------------------------------------------

Normal cluster:

SQL> select TOTAL_MB/1024,OS_MB/1024 from v$asm_disk where GROUP_NUMBER=2 ;

TOTAL_MB/1024 OS_MB/1024
------------- ----------
	   15	      15
	   15	      15
	    1	       1

Phys_GiB and ASM_GiB should have the same increased size, which means disks are resized and you can use extended space.

Filed under 19c, ASM, Azure Tagged with , , , ,

‘udev’ rules continuously being reloaded resulted in ASM nvme disks going offline

July 12, 2020 Leave a comment

Environment:

Linux Server release 7.2
kernel-3.10.0-514.26.2.el7.x86_64

Problem:

When Oracle processes are opening the device for writing and then closing it, this synthesizes a change event. And udev rules having  ACTION=="add|change" gets reloaded. This behavior causes ASM nvme disks to go offline:

Thu Jul 09 16:33:16 2020
WARNING: Disk 18 (rac1$disk1) in group 2 mode 0x7f is now being offlined

Fri Jul 10 10:04:34 2020
WARNING: Disk 19 (rac1$disk5) in group 2 mode 0x7f is now being offlined

Fri Jul 10 13:45:45 2020
WARNING: Disk 15 (rac1$disk8) in group 2 mode 0x7f is now being offlined

Solution:

To suppress the false positive change events disable the inotify watch for devices used for Oracle ASM using following steps:

  1.  Create /etc/udev/rules.d/96-nvme-nowatch.rules file with just one line in it:
ACTION=="add|change", KERNEL=="nvme*", OPTIONS:="nowatch"

2. After creating the file please run the following to activate the rule:

# udevadm control --reload-rules
# udevadm trigger --type=devices --action=change

The above command will reload the complete udev configuration and will trigger all the udev rules. On a busy production system this could disrupt ongoing operations, applications running on the server. Please use the above command during a scheduled maintenance window only.

Source: https://access.redhat.com/solutions/1465913 + our experience with customers.

Filed under ASM, Linux Tagged with , , , , , , , ,

Change AHF home from /opt/oracle.ahf to /u01/oracle.ahf

July 7, 2020 Leave a comment

Problem:

One of our customers had 2GB space for /opt mount point. After running root.sh script during GI configuration, 926M sized /opt/oracle.ahf folder was created which caused problems later with the available space in /opt.

Please note root.sh runs TFA installation using the following way:

2020-07-07 09:41:10: CLSRSC-594: Executing installation step 1 of 19: 'SetupTFA'.
2020-07-07 09:41:10: Executed stage SetupTFA in 0 seconds
2020-07-07 09:41:10: Executing cmd: /u01/app/19.3.0/grid/crs/install/tfa_setup -silent -crshome /u01/app/19.3.0/grid

tfa_setup has an option -ahf_loc which is the Autonomous Health Framework home and the default value for it is /opt/oracle.ahf

There is a question, how can we avoid exhausting /opt space used by AHF?

Solution:

Choose only one: 1,2 or 3.

1. Increase /opt mount point size
2. Or, uninstall TFA (which deletes /opt/oracle.ahf folder and releases space) and reinstall it by indicating -ahf_loc option

# tfactl uninstall
# mkdir /u01/oracle.ahf
# chmod 755 /u01/oracle.ahf
# /u01/app/19.3.0/grid/crs/install/tfa_setup -ahf_loc /u01/oracle.ahf
...
AHF Location : /u01/oracle.ahf
Choose Data Directory from below options :
1. /u01/oracle.ahf [Free Space : 41347 MB]
2. /u01/app [Free Space : 41347 MB]
3. Enter a different Location

Choose Option [1 - 3] : 1
AHF Data Directory : /u01/oracle.ahf/data

Do you want to add AHF Notification Email IDs ? [Y]|N : N
...

3. Or, change default location for AHF home (AHF_HOME) before running root.sh script:

# mkdir /u01/oracle.ahf
# chmod 755 /u01/oracle.ahf
# export AHF_HOME=/u01/oracle.ahf
# /u01/app/19.3.0/grid/root.sh

Check that AHF home was created under /u01/oracle.ahf instead of /opt/oracle.ahf

# ll /opt|grep oracle.ahf

# ll /u01|grep oracle.ahf
drwxr-xr-x 10 root root 134 Jul 7 12:46 oracle.ahf

Filed under 19c, TFA Tagged with , , , , ,

INS-45511: Installer has detected that an Oracle Grid Infrastructure home is marked incorrectly as configured

July 4, 2020 Leave a comment

Problem:

After deconfiguring Oracle Restart stack using:

[root@rac1 ~]# /u01/app/19.3.0/grid/root.sh -deconfig
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/app/19.3.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/19.3.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/grid/crsdata/rac1/crsconfig/hadeconfig.log
2020/07/04 10:49:21 CLSRSC-332: CRS resources for listeners are still configured
2020/07/04 10:49:49 CLSRSC-337: Successfully deconfigured Oracle Restart stack

Tried to configure GI as clusterware stack and got the following error:

INS-45511: Installer has detected that an Oracle Grid Infrastructure home is marked incorrectly  as configured

Solution:

Remove CRS="true" accross GI home entry in /u01/app/oraInventory/ContentsXML/inventory.xml

Original:

<HOME NAME="OraGI19Home1" LOC="/u01/app/19.3.0/grid" TYPE="O" IDX="1" CRS="true"/>

After modification:

<HOME NAME="OraGI19Home1" LOC="/u01/app/19.3.0/grid" TYPE="O" IDX="1"/>

Retry configuration.

Filed under 19c, GI, RAC Tagged with , , , ,

How to change listener ports after cluster setup (FG enabled clusters)

July 1, 2020 Leave a comment

The document describes the steps required for modifying listener port numbers after setting up the Flashgrid cluster.

How to change SCAN listener port number

1) On the first node only, modify SCAN listener port number in CRS:

# su - grid
$ srvctl modify scan_listener -p <new_scan_listener_port>
$ srvctl stop scan_listener
$ srvctl start scan_listener

2) On each database node, update the following entry in /etc/flashgrid-scan.cfg (as root):

From:
scan_port: 1521

To:
scan_port: <new_scan_listener_port>

3) On each database node, restart Flashgrid SCAN Proxy service, and make sure that it is running successfully:

# systemctl restart flashgrid-scan-proxy.service
# systemctl status flashgrid-scan-proxy.service

How to change local listener port number

1) On each database node, modify port for NodeFQDN entry in <DATABASE_HOME>/network/admin/tnsnames.ora (as oracle):

DONOTDELETE,NODEFQDN = (ADDRESS = (PROTOCOL = TCP)(Host = <database node hostname>)(Port = <new_local_listener_port>))

2) On the first node only, modify local listener port number in CRS:

# su - grid
$ srvctl modify listener -p <new_local_listener_port>
$ srvctl stop listener
$ srvctl start listener

3) On each database node, update /etc/sysconfig/iptables (as root):

  • Remove immutable flag from iptables file:
  # chattr -i /etc/sysconfig/iptables
  • Modify value in /etc/sysconfig/iptables: From: --dport 1522 To: --dport <new_local_listener_port>
  • Add immutable flah to iptables file:
  # chattr +i /etc/sysconfig/iptables

4) On each database node, restart iptables:

# systemctl restart iptables.service

5) If you have already created database(s), connect to each database and run:

# su - oracle
$ sqlplus / as sysdba
SQL> alter system set local_listener='NODEFQDN';

Filed under Uncategorized