NFS Disk Configuration for Quorum

Before starting make sure that:

1. NFS server is accessible from DB nodes
2. nfs-utils is installed on all client and DB nodes
3. ASM is running on all nodes

NFS disk configuration steps:

1. On NFS server node (e.g. nfsserver)

# systemctl disable firewalld
# systemctl stop firewalld
# mkdir /NFS_DISKS

--Create quorum disk in nfs share:
# dd if=/dev/zero of=/NFS_DISKS/nfsdisk1 bs=1M count=1024

# chmod -R 755 /NFS_DISKS
# chown -R grid:oinstall /NFS_DISKS

# vim /etc/exports
/NFS_DISKS *(rw,async)

# systemctl enable nfs-server
# systemctl start nfs-server

2. On all DB nodes (e.g. rac1 rac2)

# mkdir /NFS_DISKS 
# chown -R grid:oinstall /NFS_DISKS
# vim /etc/fstab

nfsserver:/NFS_DISKS /NFS_DISKS nfs rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,noac,vers=3,timeo=600 0 0 0

# mount -a

--Update disk discovery string from grid user:
$ asmcmd dsset '/NFS_DISKS/*','/dev/flashgrid/*'

If this is Flashgrid-enabled cluster, then you can create diskgroup which contains NFS disk as quorum:

[grid@rac1 ~]$ flashgrid-dg create --name TEST --normal --asm-compat 19.0.0.0 --db-compat 19.0.0.0 --disks /dev/flashgrid/rac1.lun4 /dev/flashgrid/rac2.lun4 --quorum-disks /NFS_DISKS/nfsdisk1

FlashGrid 19.6.48.60391
~~~~
Querying nodes: rac1, rac2, racq …
Creating group…
Completing this operation may take long time. Please wait…
CREATE DISKGROUP "TEST" NORMAL REDUNDANCY
FAILGROUP "rac1" DISK '/dev/flashgrid/rac1.lun4' NAME "rac1$LUN4" SIZE 20480M
FAILGROUP "rac2" DISK '/dev/flashgrid/rac2.lun4' NAME "rac2$LUN4" SIZE 20480M
QUORUM FAILGROUP "QUORUM0" DISK '/NFS_DISKS/nfsdisk1' NAME "TEST_Q0"
ATTRIBUTE 'au_size' = '4M', 'compatible.asm' = '19.0.0.0', 'compatible.rdbms' = '19.0.0.0';
ALTER SYSTEM SET asm_preferred_read_failure_groups='DATA.rac1','FRA.rac1','GGDG.rac1','GRID.rac1','TEST.rac1' SID='+ASM1';
ALTER SYSTEM SET asm_preferred_read_failure_groups='DATA.rac2','FRA.rac2','GGDG.rac2','GRID.rac2','TEST.rac2' SID='+ASM2';
Success

Configure Bastion host for Azure VM

What is Azure Bastion?

The Azure Bastion is PaaS service that you create inside your virtual network. It provides secure RDP and SSH connectivity to all of the VMs in the virtual network in which it is provisioned so that your target virtual machines do not need a public IP address.

Configuration Steps:

1. Connect to the Azure portal and choose the VM to connect to.

2. From the left panel, choose Bastion under the Operations section, or click Connect from the Overview section.

3. To associate a virtual network with a Bastion, it must contain a subnet with the name AzureBastionSubnet. On the following page, click Manage subnet configuration

4. Add subnet named AzureBastionSubnet

5. After configuring subnet, click link rac1-vm – Bastion in the upper left corner. And then click Create button.

6. Connect to the VM via Azure Bastion

If you have ssh key based authentication, enter correct username and choose the right private key file.

Please note, that the private key file should be in PEM format. If you get the following error message, it means your key file is not in the correct format:

 If so, convert it using ssh-keygen (for windows PuTTYgen) :

$ ssh-keygen -p -m PEM -f my_private_key

Choose the key file again and click Connect.

Creating an Oracle RAC cluster on Azure cloud with FlashGrid SkyCluster

opatch apply failed: /u01/app/oraInventory/ContentsXML/oui-patch.xml (Permission denied)

Problem:

In the two-node cluster. I applied patch 29342099 on the 1st node successfully. But the patch failed on the 2nd node:

[oracle@stbyrac2 29342099]$ $ORACLE_HOME/OPatch/opatch apply
...
Applying interim patch '29342099' to OH '/u01/app/oracle/product/19.3.0/dbhome_1'
Patching component oracle.rdbms, 19.0.0.0.0…
Patching component oracle.rdbms.rman, 19.0.0.0.0…
Patching component oracle.rdbms.rsf, 19.0.0.0.0…
ApplySession failed in system modification phase… 'ApplySession::apply failed: java.io.IOException: oracle.sysman.oui.patch.PatchException: 
java.io.FileNotFoundException: /u01/app/oraInventory/ContentsXML/oui-patch.xml (Permission denied)'

The patch failed in a bad phase, after failure while I was trying to check inventory on that node I was getting:

[oracle@stbyrac2 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory -detail
...
Inventory load failed… OPatch cannot load inventory for the given Oracle Home.
LsInventorySession failed: Unable to create patchObject
Possible causes are:
ORACLE_HOME/inventory/oneoffs/29342099 is corrupted.
java.lang.RuntimeException: No Patch exists,Please check.

Inventory knew about patch 29342099 but $ORACLE_HOME/inventory/oneoffs/29342099 did not exist.

I decided to copy folder from 1st node (where patch was successful):

[oracle@stbyrac1 ~]$ scp -r $ORACLE_HOME/inventory/oneoffs/29342099 stbyrac2:$ORACLE_HOME/inventory/oneoffs/

After that listing inventory was successful. And patch seemed to be part of the inventory. I tried to rollback it, which failed with the same permission error `/u01/app/oraInventory/ContentsXML/oui-patch.xml (Permission denied)`

Solution:

Check permissions on oui-patch.xml file on both nodes:

[oracle@primrac1 ~]$ ls -la /u01/app/oraInventory/ContentsXML/oui-patch.xml
-rw-rw---- 1 grid oinstall 174 Oct 9 20:46 /u01/app/oraInventory/ContentsXML/oui-patch.xml
[oracle@primrac1 ~]$ ssh primrac2 ls -la /u01/app/oraInventory/ContentsXML/oui-patch.xml
-rw-r--r-- 1 grid oinstall 174 Sep 29 19:38 /u01/app/oraInventory/ContentsXML/oui-patch.xml

They are different. Modify permission of that file to 660:

[root@stbyrac2 ContentsXML]# chmod 660 oui-patch.xml 

Rollback and reapply patch:

[oracle@stbyrac2 29342099]$ $ORACLE_HOME/OPatch/opatch apply
...
Applying interim patch '29342099' to OH '/u01/app/oracle/product/19.3.0/dbhome_1'
Patching component oracle.rdbms, 19.0.0.0.0…
Patching component oracle.rdbms.rman, 19.0.0.0.0…
Patching component oracle.rdbms.rsf, 19.0.0.0.0…
Patch 29342099 successfully applied.
Log file location: /u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/opatch/opatch2020-01-27_13-55-44PM_1.log
OPatch succeeded.

Apache Derby: “ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database.”

Problem:

During testing Oracle ZDM (Zero Downtime Migration), I’ve stopped ZDM host ungracefully. After that zdmservice was not able to start:

[zdmuser@primracq ~]$ /u01/app/zdmhome/bin/zdmservice start
...
CRS_ERROR:TCC-0004: The container was not able to start.
CRS_ERROR:One or more listeners failed to start. Full details will be found in the appropriate container log fileContext [/rhp] startup failed due to previous errors
...
Start operation could not start zdmservice.
zdmservice start failed…

Troubleshooting:

Check logs under [/rhp], in my case /u01/app/zdmbase/crsdata/primracq/rhp/logs/

[zdmuser@primracq ~]$ ll /u01/app/zdmbase/crsdata/primracq/rhp/logs/
 total 3748
-rw-r--r-- 1 zdmuser zdm   277774 Dec 17 19:19 catalina.2019-12-17.log
-rw-r--r-- 1 zdmuser zdm   186117 Dec 18 18:12 catalina.2019-12-18.log
-rw-r--r-- 1 root    root   24133 Dec 19 19:12 catalina.2019-12-19.log
-rw-r--r-- 1 root    root  541694 Dec 22 20:31 catalina.2019-12-22.log
-rw-r--r-- 1 zdmuser zdm    35666 Dec 23 12:40 catalina.2019-12-23.log
-rw-r--r-- 1 zdmuser zdm  2407571 Dec 23 12:40 catalina.out
-rw-r--r-- 1 zdmuser zdm    12541 Dec 23 12:40 derby.log
-rw-r--r-- 1 zdmuser zdm   273095 Dec 23 12:40 jwc_checker_stdout_err_0.log
-rw-r--r-- 1 zdmuser zdm      155 Dec 23 12:40 JWCStartEvent.log
-rw-r--r-- 1 zdmuser zdm     5083 Dec 17 19:19 localhost.2019-12-17.log
-rw-r--r-- 1 zdmuser zdm     5083 Dec 18 18:12 localhost.2019-12-18.log
-rw-r--r-- 1 root    root    5083 Dec 19 19:12 localhost.2019-12-19.log
-rw-r--r-- 1 root    root    5083 Dec 22 20:31 localhost.2019-12-22.log
-rw-r--r-- 1 zdmuser zdm    18708 Dec 23 12:40 localhost.2019-12-23.log

catalina.out showed the following error:

Internal Exception: java.sql.SQLException: An SQL data change is not permitted for a read-only connection, user or database.
...
Caused by: ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database.

Solution:

Remove db.lck file under /u01/app/zdmbase/derbyRepo:

# rm -rf /u01/app/zdmbase/derbyRepo/db.lck

And start the service again.

Change Oracle DB name using NID

Details:

Environment: RAC
Source name: orclA
Target name: orcl

1. Make sure that you have a recoverable whole database backup.

2. In Real Application Cluster we need to set cluster_database parameter to false and mount an instance on only one node:

[oracle@primrac1 ~]$ sqlplus / as sysdba
SQL> alter system set cluster_database=false scope=spfile;

[oracle@primrac1 ~]$ srvctl stop database orclA
[oracle@primrac1 ~]$ sqlplus / as sysdba
SQL> startup mount;

3. From the 1st node run nid utility. Specify username with sysdba privilege, target database name, and SETNAME parameter to yes.

[oracle@primrac1 ~]$ . oraenv
ORACLE_SID = [oracle] ? orclA1
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@primrac1 ~]$ nid TARGET=SYS DBNAME=orcl SETNAME=YES
DBNEWID: Release 19.0.0.0.0 - Production on Mon Dec 16 16:54:36 2019
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:
Connected to database ORCLA (DBID=3133348785)
Connected to server version 19.4.0
Control Files in database:
    +DATA/ORCLA/CONTROLFILE/current.290.1020357617
Change database name of database ORCLA to ORCL? (Y/[N]) => Y
Proceeding with operation
Changing database name from ORCLA to ORCL
     Control File +DATA/ORCLA/CONTROLFILE/current.290.1020357617 - modified
     Datafile +DATA/ORCLA/DATAFILE/system.283.102035744 - wrote new name
     Datafile +DATA/ORCLA/DATAFILE/sysaux.286.102035749 - wrote new name
     Datafile +DATA/ORCLA/DATAFILE/undotbs1.287.102035753 - wrote new name
     Datafile +DATA/ORCLA/DATAFILE/undotbs2.300.102035839 - wrote new name
     Datafile +DATA/ORCLA/DATAFILE/users.288.102035754 - wrote new name
     Datafile +DATA/ORCLA/TEMPFILE/temp.296.102035765 - wrote new name
     Control File +DATA/ORCLA/CONTROLFILE/current.290.1020357617 - wrote new name
     Instance shut down
 Database name changed to ORCL.
 Modify parameter file and generate a new password file before restarting.
 Succesfully changed database name.
 DBNEWID - Completed succesfully.

5. Change db_name parameter in the initialization parameter file:

[oracle@primrac1 dbs]$ . oraenv
ORACLE_SID = [orcl1] ? orclA1

[oracle@primrac1 dbs]$ sqlplus / as sysdba
SQL> startup nomount;
SQL> alter system set db_name=orcl scope=spfile;
SQL> alter system set cluster_database=true scope=spfile;
SQL> shut immediate;

6. Modify database name in srvctl:

[oracle@primrac1 dbs]$ srvctl modify database -db orclA -dbname orcl

7. Remove existing password file entry and create a new one:

[oracle@primrac1 dbs]$ srvctl modify database -db orclA -pwfile

[oracle@primrac1 dbs]$ orapwd  dbuniquename=orclA file='+DATA/ORCLA/PASSWORD/pwdorcl.ora'
Enter password for SYS:

8. Start the database using srvctl:

[oracle@primrac1 dbs]$ srvctl start database -db orclA

Depending on your needs you may also update instance names. If so, please make sure that you don’t have instance-specific parameters (e.g orclA1.instance_number), otherwise, it’s better to recreate the parameter file with the correct instance names.

I’ve shown you just a simple example of the database name change. In your specific case, there may be other things that depend on the database name.

Common usage examples of cluvfy utility

1. Discovering all available network interfaces and verifying the connectivity between the nodes in the cluster through those network interfaces:

$ cluvfy comp nodecon -n all -verbose

2. Check the reachability of specified nodes from a source node:

$ cluvfy comp nodereach -n all -verbose

3. Check the integrity of Oracle Cluster Registry (OCR) on all the specified nodes:

$ cluvfy comp ocr -n all -verbose

4. Verifying the integrity of the Oracle high availability services daemon on all nodes in the cluster:

$ cluvfy comp ohasd -n all -verbose

5. Comparing nodes:

$ cluvfy comp peer -n all -verbose

6. Verifying the software configuration on all nodes in the cluster for the Oracle Clusterware home directory:

$ cluvfy comp software -n all -verbose

7. Check the integrity of the cluster on all the nodes in the node list:

$ cluvfy comp clu -n all -verbose

Retrieving the Public Key for Key Pair on Linux or Mac OS

ssh-keygen command can be used on Linux or Mac OS to retrieve the public key from the private SSH key:

$ ssh-keygen -y -f MyKeyPair.pem 
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQChauLwkBK/vIiFY/t7uY6lzxESqZkZNvCAA3L42OH2fWzKptqGF+N32zjmLLSPFpYjoEHoHpi5e7yypTmiljtHcKUTwJTs3xclQrApCQvR+LneOi/5P5WaYl61G76osJesXiunLTa+RVr3LDR96LjPcql7JDnuh1RFhDqZ87nDfcGmXGV8iG7w3bk3R/2LuzzMYTgEVdv91S1OF1roH1baPXSV8MaYbOKhMUqV61+eP6/F5ZhT5Gk0BKX1KnQ3/gbgMqjMWRMZzYUeVjUbC52lYwrrBTQX5tHphAJtOTNJ/CpyuEuZ7ED+XYhX9Q1DNOZ47K51xbg5lsnyOBYSUqHz

-y – This option will read a private OpenSSH format file and print its public key.
-f – Specifies the filename of the key file.

Find Oracle patch description

Some patches contain several sub-patches inside. For example, when we download GI RU and unzip it, the unzipped folder contains several patch folders. To find out information about these patches you can do any of the following:

1. OPen README.html and find the section “Patch Numbers Getting Installed as Part of this Bundle Patch

2. Each patch sub-folder contains etc/config/inventory.xml, that contains description about patch:

[oracle@primrac1 29708769]$ pwd
 /home/oracle/Downloads/29708769

[oracle@primrac1 29708769]$ cat ./29834717/etc/config/inventory.xml|grep patch_description
     Database Release Update : 19.4.0.0.190716 (29834717)

[oracle@primrac1 29708769]$ cat ./29850993/etc/config/inventory.xml |grep patch_description
     OCW RELEASE UPDATE 19.4.0.0.0 (29850993)

Or we can see inventory.xml in all folders:

[oracle@primrac1 29708769]$ cat ./*/etc/config/inventory.xml |grep patch_description
     TOMCAT RELEASE UPDATE 19.0.0.0.0 (29401763)
     Database Release Update : 19.4.0.0.190716 (29834717)
     OCW RELEASE UPDATE 19.4.0.0.0 (29850993)
     ACFS RELEASE UPDATE 19.4.0.0.0 (29851014)

3. Another option is to use opatch lspatches or opatch query -all and query/list patch information:

[oracle@primrac1 29708769]$ pwd
 /home/oracle/Downloads/29708769

[oracle@primrac1 29708769]$ $ORACLE_HOME/OPatch/opatch lspatches ./29850993|grep patch_description
 patch_description:OCW RELEASE UPDATE 19.4.0.0.0 (29850993)

[oracle@primrac1 29708769]$ $ORACLE_HOME/OPatch/opatch lspatches ./29834717|grep patch_description
 patch_description:Database Release Update : 19.4.0.0.190716 (29834717)

[oracle@primrac1 29708769]$ $ORACLE_HOME/OPatch/opatch query -all 29850993|grep description
  Patch description: "OCW RELEASE UPDATE 19.4.0.0.0 (29850993)"

[oracle@primrac1 29708769]$ $ORACLE_HOME/OPatch/opatch query -all 29834717|grep description
  Patch description: "Database Release Update : 19.4.0.0.190716 (29834717)"

Patch 28729234 requires component(s) that are not installed in OracleHome. These not-installed components are oracle.crs:11.2.0.4.0

Problem:

Applying OCW patch 28729234 on RDBMS home failed with the following error:

[oracle@rac1 28729234]$ $ORACLE_HOME/OPatch/opatch apply -oh $ORACLE_HOME

Oracle Interim Patch Installer version 11.2.0.3.20
...
UtilSession failed: Patch 28729234 requires component(s) that are not installed in OracleHome. These not-installed components are oracle.crs:11.2.0.4.0

Reason:

The error message is confusing, because the real reason is in the patch itself. There is a duplication of folders and files. Custom/server directory under 28729234, also contains 28729234 directory.

Solution:

As a workaround run opatch from inner 28729234 directory:

[oracle@rac1 ~]$ cd /home/oracle/28813878/28729234/custom/server/28729234

Generate emocmrsp file:

[oracle@rac1 28729234]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/ocm/bin/emocmrsp

OCM Installation Response Generator 10.3.7.0.0 - Production
Copyright (c) 2005, 2012, Oracle and/or its affiliates.  All rights reserved.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name: 
You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y
The OCM configuration response file (ocm.rsp) was successfully created

Apply patch using the following command:

[oracle@rac1 28729234]$ $ORACLE_HOME/OPatch/opatch apply -oh $ORACLE_HOME -ocmrf ./ocm.rsp 
...
Start OOP by Prereq process.
Launch OOP…
Oracle Interim Patch Installer version 11.2.0.3.20
Copyright (c) 2019, Oracle Corporation.  All rights reserved.
...
Applying interim patch '28729234' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
Patching component oracle.rdbms, 11.2.0.4.0…
Patch 28729234 successfully applied.

Checking that OCW is applied:

[oracle@rac1 28729234]$ $ORACLE_HOME/OPatch/opatch lspatches
28729234;OCW Patch Set Update : 11.2.0.4.190115 (28729234)
28729262;Database Patch Set Update : 11.2.0.4.190115 (28729262)