Reflection of my thoughts and technical expriences

Tuesday, June 29, 2021

ORA-16692: operation disallowed for a database or far sync instance that sends redo data - Converting from Physical Standby Database to Snapshot Standby Database

Configured two physical standby databases for primary database and also setting the RedoRoutes property between these databases.

Primary Database: orcldb

Physical Standby Database: orcldbs

Physical Standby Database: orcldbp

1. Check the configuration

DGMGRL> show configuration;

Configuration - dg_rac

Protection Mode: MaxPerformance

Members:

orcldb - Primary database

orcldbs - Physical standby database

orcldbp - Physical standby database (receiving current redo)

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS (status updated 35 seconds ago)

DGMGRL>

2. Check the RedoRoutes property

DGMGRL> show database orcldb redoroutes;

RedoRoutes = '(LOCAL : orcldbs SYNC)'

DGMGRL>

DGMGRL> show database orcldbs redoroutes;

RedoRoutes = '(LOCAL : orcldb SYNC, orcldbp SYNC)(orcldb : orcldbp ASYNC)(orcldbp : orcldb ASYNC)'

DGMGRL>

DGMGRL> show database orcldbp redoroutes;

RedoRoutes = '(LOCAL : orcldbs SYNC)'

DGMGRL>

3. Configured using following commands

DGMGRL>edit database orcldb set property redoroutes='(LOCAL : orcldbs SYNC)';

Property "redoroutes" updated

DGMGRL>edit database orcldbs set property redoroutes='(LOCAL : orcldb SYNC, orcldbp SYNC)(orcldb : orcldbp ASYNC)(orcldbp : orcldb ASYNC)';

Property "redoroutes" updated

DGMGRL>edit database orcldbp set property redoroutes='(LOCAL : orcldbs SYNC)';

Property "redoroutes" updated

While trying to convert one of the physical standby database to snapshot standby database getting the following error.

DGMGRL> convert database orcldbs to snapshot standby;

Converting database "orcldbs" to a Snapshot Standby database, please wait...

Error: ORA-16692: operation disallowed for a database or far sync instance that sends redo data

Failed.

Failed to convert database "orcldbs"

DGMGRL>

Cause: An attempt was made to delete, disable, or convert (physical standby database) a member that

has a non-empty value set for its RedoRoutes property.

Action: Reset the value of the RedoRoutes property for the member to be deleted, disabled, or

converted (physical standby database) to the empty string and then retry the operation.

4. Resetting RedoRoutes property for orcldbs database

DGMGRL> edit database orcldbs set property redoroutes='';

Property "redoroutes" updated

DGMGRL>

DGMGRL> show configuration;

Configuration - dg_rac

Protection Mode: MaxPerformance

Members:

orcldb - Primary database

orcldbs - Physical standby database

Members Not Receiving Redo:

orcldbp - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS (status updated 25 seconds ago)

DGMGRL>

5. Converted to Snapshot Standby Database

DGMGRL> convert database orcldbs to snapshot standby;

Converting database "orcldbs" to a Snapshot Standby database, please wait...

Database "orcldbs" converted successfully

DGMGRL>

6. Login to orcldbs database and check the database role

[oracle@standby ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 29 19:39:09 2021

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

--------- -------------------- ----------------

ORCLDB READ WRITE SNAPSHOT STANDBY

SQL> exit

7. Converted back to physical standby database from snapshot standby database

DGMGRL> convert database orcldbs to physical standby;

Converting database "orcldbs" to a Physical Standby database, please wait...

Operation requires shut down of instance "orcldbs" on database "orcldbs"

Shutting down instance "orcldbs"...

Database closed.

Database dismounted.

ORACLE instance shut down.

Operation requires start up of instance "orcldbs" on database "orcldbs"

Starting instance "orcldbs"...

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Database mounted.

Continuing to convert database "orcldbs" ...

Database "orcldbs" converted successfully

DGMGRL>

DGMGRL> edit database orcldbs set property redoroutes='(LOCAL : orcldb SYNC, orcldbp SYNC)(orcldb : orcldbp ASYNC)(orcldbp : orcldb ASYNC)';

Property "redoroutes" updated

DGMGRL>

DGMGRL> show configuration;

Configuration - dg_rac

Protection Mode: MaxPerformance

Members:

orcldb - Primary database

orcldbs - Physical standby database

orcldbp - Physical standby database (receiving current redo)

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS (status updated 7 seconds ago)

DGMGRL>

8. Login to orcldbs database and check the database role

[oracle@standby ~]$ . oraenv

ORACLE_SID = [orcldbs] ?

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@standby ~]$

[oracle@standby ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 29 19:59:41 2021

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE

------------------- ------------------------

READ ONLY WITH APPLY PHYSICAL STANDBY

SQL>

Saturday, December 19, 2020

Oracle 19c (19.9) with ASMFD, Kernel 5.4.17 & Oracle Linux 8.3 (2-Node RAC)

Oracle has released the patches for Oracle 19c (19.8) and 19c (19.9) for ACFS/AFD drivers.

1. 30590023 UEK6 SUPPORT FOR ACFS/AFD (System Patch) - > 19.9.0.0.0ACFSRU

2. 30590023 UEK6 SUPPORT FOR ACFS/AFD (System Patch) - > 19.8.0.0.0ACFSRU

Please refer for more details:

ACFS Support On OS Platforms (Certification Matrix). (Doc ID 1369107.1)

Click Additional notes:

OL8 UEK6: 30590023 OL8 UEK6 SUPPORT FOR ACFS/AFD (NOTE: 19.x and later Grid only)

Environment:

1. Installed OEL 8.3

2. Upgraded Kernel "kernel-uek-4.18.0-193.13.2.el8_2.x86_64" to kernel-uek-5.4.17-2036.100.6.1.el8uek.x86_64

3. Installed GI 19.3 and applied patch 19.7 and applied Oracle 19c (19.9)

4. Applied "p30590023_199000ACFSRU_Linux-x86-64.zip" patch on Oracle 19c (19.9) for GRID

[oracle@rac2-oel77-123 ~]$ . oraenv

ORACLE_SID = [orcldb2] ? +ASM2

ORACLE_HOME = [/home/oracle] ? /u01/app/19.7.0/grid

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@rac2-oel77-123 ~]$

[oracle@rac2-oel77-123 ~]$ cd $ORACLE_HOME/OPatch

[oracle@rac2-oel77-123 OPatch]$ ./opatch lspatches

30898856;TOMCAT RELEASE UPDATE 19.0.0.0.0 (30898856)

30894985;OCW RELEASE UPDATE 19.7.0.0.0 (30894985)

30869304;ACFS RELEASE UPDATE 19.7.0.0.0 (30869304)

30869156;Database Release Update : 19.7.0.0.200414 (30869156)

OPatch succeeded.

[oracle@rac2-oel77-123 OPatch]$

[oracle@rac1-oel77-122 ~]$ su - root

Password:

[root@rac1-oel77-122 ~]# . oraenv

ORACLE_SID = [root] ? +ASM1

ORACLE_HOME = [/home/oracle] ? /u01/app/19.7.0/grid

The Oracle base has been set to /u01/app/oracle

[root@rac1-oel77-122 ~]#

[root@rac1-oel77-122 ~]# ps -ef | grep pmon

oracle 6506 1 0 15:21 ? 00:00:00 asm_pmon_+ASM1

oracle 8961 1 0 15:22 ? 00:00:00 mdb_pmon_-MGMTDB

oracle 9092 1 0 15:22 ? 00:00:00 ora_pmon_orcldb1

root 38171 37950 0 16:23 pts/1 00:00:00 grep --color=auto pmon

[root@rac1-oel77-122 ~]#

[root@rac1-oel77-122 ~]# sh /u01/app/19.7.0/grid/OPatch/opatchauto apply /home/oracle/31750108/

OPatchauto session is initiated at Sat Dec 19 16:24:20 2020

System initialization log file is /u01/app/19.7.0/grid/cfgtoollogs/opatchautodb/systemconfig2020-12-19_04-24-23PM.log.

Session log file is /u01/app/19.7.0/grid/cfgtoollogs/opatchauto/opatchauto2020-12-19_04-24-58PM.log

The id for this session is SKYE

Executing OPatch prereq operations to verify patch applicability on home /u01/app/19.7.0/grid

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/19.3.0/db_1

Patch applicability verified successfully on home /u01/app/19.7.0/grid

Patch applicability verified successfully on home /u01/app/oracle/product/19.3.0/db_1

Verifying SQL patch applicability on home /u01/app/oracle/product/19.3.0/db_1

SQL patch applicability verified successfully on home /u01/app/oracle/product/19.3.0/db_1

Preparing to bring down database service on home /u01/app/oracle/product/19.3.0/db_1

Successfully prepared home /u01/app/oracle/product/19.3.0/db_1 to bring down database service

Bringing down CRS service on home /u01/app/19.7.0/grid

CRS service brought down successfully on home /u01/app/19.7.0/grid

Performing prepatch operation on home /u01/app/oracle/product/19.3.0/db_1

Perpatch operation completed successfully on home /u01/app/oracle/product/19.3.0/db_1

Start applying binary patch on home /u01/app/oracle/product/19.3.0/db_1

Binary patch applied successfully on home /u01/app/oracle/product/19.3.0/db_1

Performing postpatch operation on home /u01/app/oracle/product/19.3.0/db_1

Postpatch operation completed successfully on home /u01/app/oracle/product/19.3.0/db_1

Start applying binary patch on home /u01/app/19.7.0/grid

Binary patch applied successfully on home /u01/app/19.7.0/grid

Starting CRS service on home /u01/app/19.7.0/grid

CRS service started successfully on home /u01/app/19.7.0/grid

Preparing home /u01/app/oracle/product/19.3.0/db_1 after database service restarted

No step execution required.........

Trying to apply SQL patch on home /u01/app/oracle/product/19.3.0/db_1

SQL patch applied successfully on home /u01/app/oracle/product/19.3.0/db_1

OPatchAuto successful.

--------------------------------Summary--------------------------------

Patching is completed successfully. Please find the summary as follows:

Host:rac1-oel77-122

RAC Home:/u01/app/oracle/product/19.3.0/db_1

Version:19.0.0.0.0

Summary:

==Following patches were SKIPPED:

Patch: /home/oracle/31750108/31773437

Reason: This patch is not applicable to this specified target type - "rac_database"

Patch: /home/oracle/31750108/31780966

Reason: This patch is not applicable to this specified target type - "rac_database"

==Following patches were SUCCESSFULLY applied:

Patch: /home/oracle/31750108/31771877

Log: /u01/app/oracle/product/19.3.0/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2020-12-19_16-30-30PM_1.log

Patch: /home/oracle/31750108/31772784

Log: /u01/app/oracle/product/19.3.0/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2020-12-19_16-30-30PM_1.log

Host:rac1-oel77-122

CRS Home:/u01/app/19.7.0/grid

Version:19.0.0.0.0

Summary:

==Following patches were SUCCESSFULLY applied:

Patch: /home/oracle/31750108/31771877

Log: /u01/app/19.7.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-12-19_16-39-54PM_1.log

Patch: /home/oracle/31750108/31772784

Log: /u01/app/19.7.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-12-19_16-39-54PM_1.log

Patch: /home/oracle/31750108/31773437

Log: /u01/app/19.7.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-12-19_16-39-54PM_1.log

Patch: /home/oracle/31750108/31780966

Log: /u01/app/19.7.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-12-19_16-39-54PM_1.log

OPatchauto session completed at Sat Dec 19 16:54:51 2020

Time taken to complete the session 30 minutes, 31 seconds

[root@rac1-oel77-122 ~]#

[root@rac2-oel77-123 ~]# . oraenv

ORACLE_SID = [+ASM2] ?

ORACLE_HOME = [/home/oracle] ? /u01/app/19.7.0/grid

The Oracle base remains unchanged with value /u01/app/oracle

[root@rac2-oel77-123 ~]#

[root@rac2-oel77-123 ~]# sh /u01/app/19.7.0/grid/OPatch/opatchauto apply /home/oracle/31750108/

OPatchauto session is initiated at Sat Dec 19 17:19:31 2020

System initialization log file is /u01/app/19.7.0/grid/cfgtoollogs/opatchautodb/systemconfig2020-12-19_05-19-35PM.log.

Session log file is /u01/app/19.7.0/grid/cfgtoollogs/opatchauto/opatchauto2020-12-19_05-19-56PM.log

The id for this session is BKPL