Far Sync Targets are not Discovered Correctly by OEM Auto Discovery Process

If there are far sync instances in a data guard configuration then those will not be correctly identified as far sync instances by the OEM target discovery process. There were two posts related to the same issue where far sync types were not listed for template apply. The other issue was related to data guard performance page where far sync instace graphs had error status.
Root cause for all of this is that auto discovery will assign target type of "database instance" for the far sync instance.

After an SR that spaned year and half Oracle support said that currently OEM auto discovery process is unable to correctly detect the far sync instance types. Their solution was to manaully add the far sync instance.



To manually add the far sync go to add target -> add target manually and select far sync type and the host where far sync resides.

Afterwards the far sync instaces has be views via all targets and filtering by far syncs. They will not appear under database targets which is the incorrect behaviour.

The data guard performance graphs for far sync will appear without any error status.

As far sync has been around over 8 years now and for OEM to not correctly identify them was very puzzling to say the least. The issue was first discovered on 13.4 and exists on the 13.5 (latest at the time of this post). After further deliberations Oracle has created an enhancement request (ER-34673700) to address this issue. Whether this ER will be accepted and worked upon is still unknown.

The controlfile sequence# has now reached architectural limit

A test database (an out of support 11.2.0.4) running in noarchive log mode had the following message on the alert log.

*************************************************************************
ATTENTION: The controlfile sequence# has now reached 4294967295 (0xffffffff),
           which is the architectural limit. Further controlfile updates
           are no longer possible. To resume normal database operation
           it is necessary to shutdown abort all instances and perform
           the steps described in Doc ID 20324049.8 at My Oracle Support
           to reset the controlfile sequence# to 1.
*************************************************************************
Errors in file /opt/app/oracle/diag/rdbms/uat/uat/trace/uat_ora_28299.trc  (incident=1295436):
ORA-00600: internal error code, arguments: [kccfhb_3], [4294967295], [], [], [], [], [], [], [], [], [], []
Incident details in: /opt/app/oracle/diag/rdbms/uat/uat/incident/incdir_1295436/uat_ora_28299_i1295436.trc

In this situation database would not mount, only starting with nomount mode is possible.
The MOS doc 20324049.8 mentioned in the message is helpful in resolving the issue.
The workaround option given in it, which is to set the db_unrecoverable_scn_tracking=false did not work. It is mentioned in the MOS that setting this parameter is a proactive step not a reactive. So once the issue has occured this workaround is of no help.



The solution is to backup the controlfile to a trace file and then use it to recreate the controlfile. But for this the database must be (at least) in mount mode. As stated earlier database cannot be mounted at this stage. So opted to create a control file backup of a different database. Then modified that trace file by replacing the database name, online logfile and datafile names and locations with the names and locations from the problem database. Then started the database in nomount mode without setting the db_unrecoverable_scn_tracking parameter (MOS doc states "after installing this fix" without specifying what it is). Ran the create contfolfile script which resulted in new control files being created. Finally recover and open the database (without resetlogs). This resolved the issue and database is able to function as before without incuring any data loss.
The MOS doc mentions ORA-00227 associated with this issue. There were no ORA-00227 messages on the alert log. It was associated only with an ora-6000 [kccfhb_3] which is listed in the MOS doc as one of the symptoms of this issue.

Useful MOS doc
Bug 20324049 - ORA-227 Controlfile Corruption when reaching Maximum Value for Control Seq kccfhcsq [20324049.8]

Upgrading Oracle Restart from 19c to 21c on RHEL 7

This post shows the steps for upgrading the grid infrastructure portion of 19c Oracle Restart to 21c.

1. The current Oracle restart configuration is as below.

Resource Name             Type                      Target             State              Host
-------------             ------                    -------            --------           ----------
ora.DATA.dg               ora.diskgroup.type        ONLINE             ONLINE             ip-172-31-10-91
ora.FRA.dg                ora.diskgroup.type        ONLINE             ONLINE             ip-172-31-10-91
ora.LISTENER.lsnr         ora.listener.type         ONLINE             ONLINE             ip-172-31-10-91
ora.asm                   ora.asm.type              ONLINE             ONLINE             ip-172-31-10-91
ora.cssd                  ora.cssd.type             ONLINE             ONLINE             ip-172-31-10-91
ora.diskmon               ora.diskmon.type          OFFLINE            OFFLINE
ora.evmd                  ora.evm.type              ONLINE             ONLINE             ip-172-31-10-91
ora.ons                   ora.ons.type              OFFLINE            OFFLINE
ora.testcdb.db            ora.database.type         ONLINE             ONLINE             ip-172-31-10-91

The software and release version of the HAS are as below.

crsctl query has releaseversion
Oracle High Availability Services release version on the local node is [19.0.0.0.0]

crsctl query has softwareversion
Oracle High Availability Services version on the local node is [19.0.0.0.0]

2. The current patch on 19c GI is 19.12.

$ORACLE_HOME/OPatch/opatch lspatches
32918050;TOMCAT RELEASE UPDATE 19.0.0.0.0 (32918050)
32904851;Database Release Update : 19.12.0.0.210720 (32904851)
32585572;DBWLM RELEASE UPDATE 19.0.0.0.0 (32585572)
32915586;ACFS RELEASE UPDATE 19.12.0.0.0 (32915586)
32916816;OCW RELEASE UPDATE 19.12.0.0.0 (32916816)

3. Make the directory for GI home and unzip the 21c installer to this directory.

mkdir -p /opt/app/oracle/product/21.x.0/grid
unzip LINUX.X64_213000_grid_home.zip -d /opt/app/oracle/product/21.x.0/grid

4. Run the cluster verification tool from 21c GI home with hacfg option. The failure listed below is due to 0 SWAP size on AWS node. This could be ignored.

./runcluvfy.sh stage -pre hacfg

Performing following verification checks ...

  Physical Memory ...PASSED
  Available Physical Memory ...PASSED
  Swap Size ...FAILED (PRVF-7573)
  Free Space: ip-172-31-10-91:/usr,ip-172-31-10-91:/var,ip-172-31-10-91:/etc,ip-172-31-10-91:/sbin,ip-172-31-10-91:/tmp ...PASSED
  User Existence: oracle ...
    Users With Same UID: 501 ...PASSED
  User Existence: oracle ...PASSED
  Group Existence: dba ...PASSED
  Group Existence: oinstall ...PASSED
  Group Membership: oinstall(Primary) ...PASSED
  Group Membership: dba ...PASSED
  Run Level ...PASSED
  Architecture ...PASSED
  OS Kernel Version ...PASSED
  OS Kernel Parameter: semmsl ...PASSED
  OS Kernel Parameter: semmns ...PASSED
  OS Kernel Parameter: semopm ...PASSED
  OS Kernel Parameter: semmni ...PASSED
  OS Kernel Parameter: shmmax ...PASSED
  OS Kernel Parameter: shmmni ...PASSED
  OS Kernel Parameter: shmall ...PASSED
  OS Kernel Parameter: file-max ...PASSED
  OS Kernel Parameter: ip_local_port_range ...PASSED
  OS Kernel Parameter: rmem_default ...PASSED
  OS Kernel Parameter: rmem_max ...PASSED
  OS Kernel Parameter: wmem_default ...PASSED
  OS Kernel Parameter: wmem_max ...PASSED
  OS Kernel Parameter: aio-max-nr ...PASSED
  Package: kmod-20-21 (x86_64) ...PASSED
  Package: kmod-libs-20-21 (x86_64) ...PASSED
  Package: binutils-2.23.52.0.1 ...PASSED
  Package: libgcc-4.8.2 (x86_64) ...PASSED
  Package: libstdc++-4.8.2 (x86_64) ...PASSED
  Package: sysstat-10.1.5 ...PASSED
  Package: ksh ...PASSED
  Package: make-3.82 ...PASSED
  Package: glibc-2.17 (x86_64) ...PASSED
  Package: glibc-devel-2.17 (x86_64) ...PASSED
  Package: libaio-0.3.109 (x86_64) ...PASSED
  Package: nfs-utils-1.2.3-15 ...PASSED
  Package: smartmontools-6.2-4 ...PASSED
  Package: net-tools-2.0-0.17 ...PASSED
  Package: libxcb-1.11 (x86_64) ...PASSED
  Package: libX11-1.6.3 (x86_64) ...PASSED
  Package: libXau-1.0.8 (x86_64) ...PASSED
  Package: libXi-1.7.4 (x86_64) ...PASSED
  Package: libXtst-1.2.2 (x86_64) ...PASSED
  Package: policycoreutils-python-2.5-17 ...PASSED
  Package: policycoreutils-2.5-17 ...PASSED
  Users With Same UID: 0 ...PASSED
  Current Group ID ...PASSED
  Root user consistency ...PASSED

Pre-check for Oracle Restart configuration was unsuccessful.


Failures were encountered during execution of CVU verification request "stage -pre hacfg".

Swap Size ...FAILED
ip-172-31-10-91: PRVF-7573 : Sufficient swap size is not available on node
                 "ip-172-31-10-91" [Required = 15.5115GB (1.6265024E7KB) ;
                 Found = 0.0 bytes]


CVU operation performed:      stage -pre hacfg
Date:                         Apr 25, 2022 12:56:04 PM
CVU home:                     /opt/app/oracle/product/21.x.0/grid
Grid home:                    /opt/app/oracle/product/19.x.0/grid
User:                         oracle
Operating system:             Linux3.10.0-1062.9.1.el7.x86_64

5. ASM will be upgraded as part of the upgrade process. Therefore stop the database before starting the GI upgrade. Upgrade process will prompt to stop the database during gridSetup run as well.

Stop the DB.

srvctl stop database -d testcdb

6. Run the gridsetup with '-applyRU' to apply the latest RU on 21c. One the patch is applied the OUI will appear.

./gridSetup.sh -applyRU /opt/installs/21c/psu_apr22/33859395
Preparing the home to patch...
Applying the patch /opt/installs/21c/psu_apr22/33859395...

Select upgrade GI

The unzip location is the GI home. Only Oracle base could be changed at install time.

During the pre-req check the following two items failed.

First item is related to ASM compatibility.

As shown on the message the asm.compatibility must be 11.2.0.2 or above. This was lower on the system used for this upgrade test. Upgrade the asm.compatibility to above 11.2.0.2.

NAME                           VALUE                          GROUP_NUMBER 
------------------------------ ------------------------------ ------------ 
compatible.asm                 19.0.0.0.0                                1 
compatible.rdbms               19.0.0.0.0                                1 
compatible.asm                 19.0.0.0.0                                2 
compatible.rdbms               19.0.0.0.0                                2

Once the asm.compatibility was updated this pre-req failure went away.
The second failure is related to disk discovery string.

The message states there's a mismatch between values in GnP profile and ASM instance. The asm disk discovery string could be at ASM instance level either with

SQL> alter system set asm_diskstring='/dev/oracleasm/cg*' scope=both;

or

dsset /dev/oracleasm/cg*

However, this did not resolve this pre-req failure and kept being flagged. Finally decided to ignore this pre-req failure and continue.
Summary page is shown below.

7. When prompted run the rootupgrade.sh.

# /opt/app/oracle/product/21.x.0/grid/rootupgrade.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /opt/app/oracle/product/21.x.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 file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]: y
   Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]: y
   Copying coraenv to /usr/local/bin ...

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: /opt/app/oracle/product/21.x.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /opt/app/oracle/crsdata/ip-172-31-10-91/crsconfig/roothas_2022-04-25_01-50-58PM.log
2022/04/25 13:51:00 CLSRSC-595: Executing upgrade step 1 of 12: 'UpgPrechecks'.
2022/04/25 13:51:04 CLSRSC-595: Executing upgrade step 2 of 12: 'GetOldConfig'.
2022/04/25 13:51:06 CLSRSC-595: Executing upgrade step 3 of 12: 'GenSiteGUIDs'.
2022/04/25 13:51:06 CLSRSC-595: Executing upgrade step 4 of 12: 'SetupOSD'.
2022/04/25 13:51:07 CLSRSC-595: Executing upgrade step 5 of 12: 'PreUpgrade'.
2022/04/25 13:51:35 CLSRSC-595: Executing upgrade step 6 of 12: 'UpgradeAFD'.
2022/04/25 13:51:36 CLSRSC-595: Executing upgrade step 7 of 12: 'UpgradeOLR'.
clscfg: EXISTING configuration version 0 detected.
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
2022/04/25 13:51:42 CLSRSC-595: Executing upgrade step 8 of 12: 'UpgradeOCR'.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node ip-172-31-10-91 successfully pinned.
2022/04/25 13:51:45 CLSRSC-595: Executing upgrade step 9 of 12: 'CreateOHASD'.
2022/04/25 13:51:46 CLSRSC-595: Executing upgrade step 10 of 12: 'ConfigOHASD'.
2022/04/25 13:51:46 CLSRSC-329: Replacing Clusterware entries in file 'oracle-ohasd.service'
2022/04/25 13:52:03 CLSRSC-595: Executing upgrade step 11 of 12: 'UpgradeSIHA'.

ip-172-31-10-91     2022/04/25 13:52:55     /opt/app/oracle/crsdata/ip-172-31-10-91/olr/backup_20220425_135255.olr     498597253

ip-172-31-10-91     2021/08/27 12:17:16     /opt/app/oracle/crsdata/ip-172-31-10-91/olr/backup_20210827_121716.olr     1944883066
2022/04/25 13:52:56 CLSRSC-595: Executing upgrade step 12 of 12: 'InstallACFS'.
2022/04/25 13:53:54 CLSRSC-327: Successfully configured Oracle Restart for a standalone server

8. This would have successfully upgrade GI to 21c.

9. Current release and software version for HAS are as below.

crsctl query has releaseversion
Oracle High Availability Services release version on the local node is [21.0.0.0.0]

crsctl query has softwareversion
Oracle High Availability Services version on the local node is [21.0.0.0.0]

10. Run the cluvfy for post ugprade.

cluvfy stage -post hacfg

Performing following verification checks ...

  Oracle Restart Integrity ...PASSED
  OLR Integrity ...PASSED

Post-check for Oracle Restart configuration was successful.

CVU operation performed:      stage -post hacfg
Date:                         Apr 25, 2022 2:17:30 PM
CVU home:                     /opt/app/oracle/product/21.x.0/grid
Grid home:                    /opt/app/oracle/product/21.x.0/grid
User:                         oracle
Operating system:             Linux3.10.0-1062.9.1.el7.x86_64

Related Posts
Upgrading Oracle Single Instance with ASM (Oracle Restart) from 11.2.0.4 to 19c (19.6) on RHEL 7
Upgrading Oracle Restart from 18c (18.6) to 19c (19.3)
Upgrading Oracle Restart from 12.2.0.1 to 18c
Upgrading Oracle Single Instance with ASM (Oracle Restart) from 11.2.0.4 to 12.2.0.1
Upgrading Oracle Single Instance with ASM (Oracle Restart) from 12.1.0.2 to 12.2.0.1
Upgrading Single Instance on ASM from 11.2.0.3 to 11.2.0.4
Upgrading Grid Infrastructure Used for Single Instance from 11.2.0.4 to 12.1.0.2