Huge Amount of Audit Records Generated After ASH Package Update

After a patch update on EM 13.4 cloud control the performance hub page showed the "ASH Package Version Status" warning (refer 2784094.1). Warning itself shows what needs to be done to address the issue.

If the CIS standard for 19c DB is strictly followed which asks to revoke execute privilege on dbms_lob from public then execute privilge on dbms_lob must be granted to either public of dbsnmp user (for all containers) for ash package update to work (refer 2699059.1).
Once the ash package is updated, activities on ash analytics page (simply monitroing and automatic page refreshes) results in huge amount of audit records being generated. For 24 hour period nearly 32GB of audit records were generated.
Majority of records are as below.

EVENT_TIMESTAMP                ACTION_NAM OBJECT_NAME          DBUSERNAME           OBJECT_SCHEMA        OS_USERNAM SQL_TEXT
------------------------------ ---------- -------------------- -------------------- -------------------- ---------- ----------------------------------------------------------------------------------------------------
                                                                                                                        FROM TABLE(GV$(CURSOR(

19-AUG-21 02.51.57.585441 PM   SELECT     USER_NAME_XML        C##MONITOR         DBSNMP               oracle     WITH  unified_ash AS (
                                                                                                                        SELECT /*+ NO_MERGE */ *
                                                                                                                        FROM TABLE(GV$(CURSOR(

19-AUG-21 02.51.57.594962 PM   SELECT     MAP_WAITCLASS_XML    C##MONITOR         DBSNMP               oracle     WITH  unified_ash AS (
                                                                                                                        SELECT /*+ NO_MERGE */ *
                                                                                                                        FROM TABLE(GV$(CURSOR(

19-AUG-21 02.51.57.606929 PM   SELECT     MAP_XID_XML          C##MONITOR         DBSNMP               oracle     WITH  unified_ash AS (
                                                                                                                        SELECT /*+ NO_MERGE */ *
                                                                                                                        FROM TABLE(GV$(CURSOR(

Everytime the performance page is refreshed (manual or auto) new set of audit records are added to the unified audit table.



The main cause of the audit record generation is the auditing of privilege "SELECT ANY DICTIONARY", which is also part of CIS standard. The auditing of this privilege was present even before the ash package update. What seem to have changed is now the user used for monitoring activity (c##monitor) seem to access the dbsnmp objects. c##montior user is granted only one role and that is EM_EXPRESS_BASIC.
To reduce the audit records and keep the ash package update, modify the audit policy by dropping the auditing of select any dictionary privilege. This will break the strict adherence to CIS.
Or simply do not update the ash package and ignore the warning. This was the case before patching and warning was shown. The number of audit records generated was low even with the use of select any dictionary privilege was being audited.
Only way to have the update and reduce the number of audit records being generated is to login to EM with dbsnmp user for monitoring instead of another user. DBSNMP user has more roles and privileges granted to it than EM_EXPRESS_BASIC. As such it may not be an option that is always be feasible.

Backup Configuraiton in DBCS VM DB With Data Guard

There is an earlier post on how to setup data guard association on DBCS VM DB.
This post looks at the backup configuraiton of such a setup.
Primary has automatic backup enabled.

The new data guard configuration is created for database between two regions.

DGMGRL> show configuration

Configuration - londb_lhr1cq_londb_cwl1zf

  Protection Mode: MaxPerformance
  Members:
  londb_lhr1cq - Primary database
    londb_cwl1zf - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 48 seconds ago)

Once the DG association is created the archive log deletion policy on primary gets changed from

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';

to

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE' SHIPPED TO ALL STANDBY;

Even though backed up time appears before the shipped clause this has not prevented archvie logs getting deleted.
On the standby the archive log deletion policy is

CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;

Even though primary had automatic backup enabled, the standby is created with automatic backup disabled.

There is no simple way to enable automatic backups on standby. There's no "configure automatic backup" button on the standby database detail page. Even with terraform there's no way to enable automatic backup on the standby database (this could change in the future).

Only way to enable automatic backup on the standby is to make a switchover to the standby. After the switchover the "configure automatic backup" button will appear on the new primary (old standby) database detail page. However, automatic backup will still be disabled.

Enable automatic backup on the new primary (old standby).

Afterwards automatic backup enabled on the new primary (old standby).

The archive log deletion policy on the new primary (old standby) gets changed from

CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;

to

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE' SHIPPED TO ALL STANDBY;

after the first automatic backup has taken place.
The old primary (new standby) will have automatic backup enabled status.

However, it doesn't take full or incremental backups. Only the hourly archive log backups run with automatic backup enabled on the standby.
Furthermore with standby in mount no manual backups are not allowed using dbcli.

dbcli create-backup -bt ArchiveLog -i dd2a13fb-46cf-4a51-9cce-485b7ff4ace1
DCS-10089:Database londb is in an invalid state '{ nwpdbvm: Mounted (Closed) }'.

There's also no way to disable automatic backup on a standby that already has it enabled. Same as enabling, DB must be made a primary DB to disable the automatic backups.

Related Posts
Data Guard on OCI PaaS DB
Data Guard With OCI PaaS DB (DBaaS) Primary and Standby on IaaS

Creating Database from Backup of a Terminated DB System

Once a DBCS VM DB system or a database is terminated there's no way to recover it. At least that's the officail word from Oracle and stated in bold before termianting a DB or a DB system. For DB

For DB system.

There are two options for creating a database from backup when database is up and running (before termianted). One is using the "Create database from backup" button. This gives sevearl options such as last backup or from a specified timestamp.

Other method is selecting a specific backup and then clicking the three dots at the end and choosing the create database. This only gives two options.

When a DB System is terminated it still listed in the DB system page with a terminated state.

After some time it's removed from the DB System page. During this time between being terminated and removed from DB System page it is possible to create a database from backup of the terminated database. This method is not offical. It could change in the future. But at the time of writing it is possible.
Even though database is terminated, it could traversed using the links. Most of the options and buttons are grayed out.

"Create database from backup" button is grayed out but on an individual backup the "create database" item is available. Select "Create Database" option. The backup used in this case is an automatic backup.

During the creationg is is possible to use a different ssh key and storage type (ASM could restored as LVM). However, the hostname must be different to that of the terminated DB system's node name. Using the same hostname prefix could result in confliciting node names and DB creation fails.

Restored database could have a different name. But the TDE wallet password of the terminated DB system must be specified. Withuot this the restore will fail.

A new DB system will start provisioning.

Work request shows the steps and last step is the restoring of the database.

If no issues encountered such as duplicated vm names, wrong TDE passwords the restore will complete without error.

The DB system provisioning will complete and DB system will be available for login.

Three different tests were done using this method. One had a DB system created with ASM but restored to LVM. Another had a LVM DB System restored as LVM DB System. Third was a DB System that had a standby DB at the time of the termiantion. The backup used for restore was created while it was in primary role. The restored DB system had a read/write DB.

If the terminated database is not visible on the DB system page then this method is a non starter.
This is not a supported method. If it fails no pointing in contacting Oracle support as Oracle has stated once terminated all is gone, including automatic backups.

KeyStore / TrustStore Password and '?' is interpreted as $ORACLE_HOME

One way to connect to ATP using JDBC thin is to load key/trust store password and locations using an ojdbc.properties file.
While atempting to connect to a customer ATP using given crednetails resulted in an error similar to below.

java.sql.SQLException: the connection properties file contains an invalid expression in the value of: javax.net.ssl.keyStorePassword

The customer given credentail for the ATP wallet (which is also the passwords for key/trust store) contained the character "?".
After some investigations it was found out that the JDBC driver treats the "?" as a subtitue for $ORACLE_HOME and complains that this is not set.

Caused by: java.io.IOException: Environment variable is not set: ORACLE_HOME. ('?' is interpreted as $ORACLE_HOME)
	at oracle.jdbc.driver.PropertiesFileUtil$Interpreter.readQuestionMark(PropertiesFileUtil.java:702)
	at oracle.jdbc.driver.PropertiesFileUtil$Interpreter.interpret(PropertiesFileUtil.java:669)
	at oracle.jdbc.driver.PropertiesFileUtil$Interpreter.access$000(PropertiesFileUtil.java:622)
	at oracle.jdbc.driver.PropertiesFileUtil.processExpressions(PropertiesFileUtil.java:591)

Similar to keystore, the truststore also result in an error. Since JDBC driver code load keystore password first and as it result in an error the trusture password doesn't get picked. But if truststore password contains ? and keystore didn't then error would indicate the same with regard to truststore.

java.sql.SQLException: the connection properties file contains an invalid expression in the value of: javax.net.ssl.trustStorePassword

This could affect not just ATP but any DB. Infact to recreate the problem don't even need a DB to connect to. Simply loading a ojdbc.properties file with either keystore or truststore password containing "?" character in them would result in the error. Below is an example java class with minimum number of lines of code needed to recreate the issue. The DB URL need not be valid as error occurs before URL is validated.

public class KeyStorePwd2 {

    public static void main(String[] args) throws Exception {

        PoolDataSource ds = PoolDataSourceFactory.getPoolDataSource();
        ds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
        ds.setURL("jdbc:oracle:thin:@test:1512/test");
        Connection con = ds.getConnection();

    }

}

The ojdbc.properties contain the following

javax.net.ssl.trustStore=C:\\Asanga\\java\\keystoretest\\truststore.jks
javax.net.ssl.trustStorePassword=test_123?4_ABC
javax.net.ssl.keyStore=C:\\Asanga\\java\\keystoretest\\keystore.jks
javax.net.ssl.keyStorePassword=test_123?4_ABC

Compile and run giving the ojdbc.properties file location in the tns_admin JVM option.

java -Doracle.net.tns_admin=. KeyStorePwd2

This result in a run time error and stack trace is shown below.

Exception in thread "main" java.sql.SQLException: Unable to start the Universal Connection Pool: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource: java.sql.SQLException: the connection properties file contains an invalid expression in the value of: javax.net.ssl.keyStorePassword
	at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:456)
	at oracle.ucp.util.UCPErrorHandler.throwSQLException(UCPErrorHandler.java:133)
	at oracle.ucp.jdbc.PoolDataSourceImpl.startPool(PoolDataSourceImpl.java:928)
	at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1961)
	at oracle.ucp.jdbc.PoolDataSourceImpl.access$400(PoolDataSourceImpl.java:201)
	at oracle.ucp.jdbc.PoolDataSourceImpl$31.build(PoolDataSourceImpl.java:4279)
	at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1917)
	at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1880)
	at oracle.ucp.jdbc.PoolDataSourceImpl.getConnection(PoolDataSourceImpl.java:1865)
	at KeyStorePwd2.main(KeyStorePwd2.java:18)
Caused by: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource: java.sql.SQLException: the connection properties file contains an invalid expression in the value of: javax.net.ssl.keyStorePassword
	at oracle.ucp.util.UCPErrorHandler.newUniversalConnectionPoolException(UCPErrorHandler.java:336)
	at oracle.ucp.util.UCPErrorHandler.throwUniversalConnectionPoolException(UCPErrorHandler.java:59)
	at oracle.ucp.jdbc.oracle.OracleDataSourceConnectionFactoryAdapter.createConnection(OracleDataSourceConnectionFactoryAdapter.java:134)
	at oracle.ucp.common.Database.createPooledConnection(Database.java:256)
	at oracle.ucp.common.Topology.start(Topology.java:247)
	at oracle.ucp.common.Core.start(Core.java:2361)
	at oracle.ucp.common.UniversalConnectionPoolBase.start(UniversalConnectionPoolBase.java:690)
	at oracle.ucp.jdbc.oracle.OracleJDBCConnectionPool.start(OracleJDBCConnectionPool.java:129)
	at oracle.ucp.jdbc.PoolDataSourceImpl.startPool(PoolDataSourceImpl.java:924)
	... 7 more
Caused by: java.sql.SQLException: the connection properties file contains an invalid expression in the value of: javax.net.ssl.keyStorePassword
	at oracle.jdbc.driver.PropertiesFileUtil.processExpressions(PropertiesFileUtil.java:596)
	at oracle.jdbc.driver.PropertiesFileUtil.loadDefaultFiles(PropertiesFileUtil.java:221)
	at oracle.jdbc.driver.PropertiesFileUtil.loadPropertiesFromFile(PropertiesFileUtil.java:139)
	at oracle.jdbc.driver.PhysicalConnection.getConnectionPropertiesFromFile(PhysicalConnection.java:10210)
	at oracle.jdbc.driver.PhysicalConnection.readConnectionProperties(PhysicalConnection.java:1049)
	at oracle.jdbc.driver.PhysicalConnection.init>(PhysicalConnection.java:747)
	at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:502)
	at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:56)
	at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:747)
	at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:413)
	at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:298)
	at oracle.jdbc.pool.OracleDataSource$1.build(OracleDataSource.java:1730)
	at oracle.jdbc.pool.OracleDataSource$1.build(OracleDataSource.java:1716)
	at oracle.ucp.jdbc.oracle.OracleDataSourceConnectionFactoryAdapter.createConnection(OracleDataSourceConnectionFactoryAdapter.java:103)
	... 13 more
Caused by: java.io.IOException: Environment variable is not set: ORACLE_HOME. ('?' is interpreted as $ORACLE_HOME)
	at oracle.jdbc.driver.PropertiesFileUtil$Interpreter.readQuestionMark(PropertiesFileUtil.java:702)
	at oracle.jdbc.driver.PropertiesFileUtil$Interpreter.interpret(PropertiesFileUtil.java:669)
	at oracle.jdbc.driver.PropertiesFileUtil$Interpreter.access$000(PropertiesFileUtil.java:622)
	at oracle.jdbc.driver.PropertiesFileUtil.processExpressions(PropertiesFileUtil.java:591)
	... 26 more

It makes no sense to treat "?" in a password field as a directory location i.e $ORACLE_HOME.



The issue is not there if the password doesn't contain "?". In case of ATP its just a matter of downloading a new wallet and giving it a password that doesn't contain "?" character in it.
However, if this is not possible there are several workarounds to overcome this.
One solution is to specify the keystore and trusttore password as JVM options instead of using ojdbc.properties file (key/trust sotre file location could still be loaded from ojdbc.properteis). Taking the previous example this would look like as below (it's assume password related lines are commented in the ojdbc.properties)

java -Doracle.net.tns_admin=. -Djavax.net.ssl.keyStorePassword=test_123?4_ABC -Djavax.net.ssl.trustStorePassword=test_123?4_ABC KeyStorePwd2

Other solution is to specify it as a connection pool property. Example sinppet shown below.

	PoolDataSource ds = PoolDataSourceFactory.getPoolDataSource();
        ds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");

        Properties p = new Properties();
        p.put(CONNECTION_PROPERTY_THIN_JAVAX_NET_SSL_TRUSTSTOREPASSWORD, "hello_DB?A_1234");
        p.put(CONNECTION_PROPERTY_THIN_JAVAX_NET_SSL_KEYSTOREPASSWORD, "hello_DB?A_1234");
        ds.setConnectionProperties(p);

SR has resulted in intenral bug 33473422.

Remote Log Archive Destinations are Not Mentioned in Alert Log After DBRU 19.12 Applied

A data guard configuration consists of several standby and far sync instances.

DGMGRL> show configuration

Configuration - db_dg

  Protection Mode: MaxAvailability
  Members:
  db2  - Primary database
    db1  - Physical standby database
    db3  - Physical standby database
      db4  - Physical standby database (receiving current redo)
    fs1  - Far sync instance
      db5  - Physical standby database
      db6  - Physical standby database
      db7  - Physical standby database
      db8  - Physical standby database
      db9  - Physical standby database
      db10 - Physical standby database
      db11 - Physical standby database
      db12 - Physical standby database
      db13 - Physical standby database
      db14 - Physical standby database

  Members Not Receiving Redo:
  fs2  - Far sync instance (alternate of fs1)

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 57 seconds ago)

When ever there is a log switch the alert log would have entries corresponding to all remote log archive destinations (denoted LAD). For example in the above configuration primary write sto db1,db3 and fs1. On the primary alert log entries like below would be visible.

LGWR (PID:107336): SRL selected to archive T-1.S-3023
LGWR (PID:107336): SRL selected for T-1.S-3023 for LAD:6
LGWR (PID:107336): SRL selected to archive T-1.S-3023
LGWR (PID:107336): SRL selected for T-1.S-3023 for LAD:4
LGWR (PID:107336): SRL selected to archive T-1.S-3023
LGWR (PID:107336): SRL selected for T-1.S-3023 for LAD:3

The LAD:n where n corresponds to log archive detination parameter number (log_archive_dest_n).
Similarly on the far sync alert log also there are entries corresponding to each log archive destination.

TT02 (PID:5781): SRL selected for T-1.S-36125 for LAD:12
TT02 (PID:5781): SRL selected for T-1.S-36125 for LAD:11
TT02 (PID:5781): SRL selected for T-1.S-36125 for LAD:10
TT02 (PID:5781): SRL selected for T-1.S-36125 for LAD:9
TT02 (PID:5781): SRL selected for T-1.S-36125 for LAD:8
TT02 (PID:5781): SRL selected for T-1.S-36125 for LAD:7
TT02 (PID:5781): SRL selected for T-1.S-36125 for LAD:6
TT02 (PID:5781): SRL selected for T-1.S-36125 for LAD:5
TT02 (PID:5781): SRL selected for T-1.S-36125 for LAD:4
TT02 (PID:5781): SRL selected for T-1.S-36125 for LAD:3

However, after applying DBRU 19.12 only one LAD is shown and that corresponds to local archive log destination. On primary

NET  (PID:27469): Archived Log entry 236855 added for T-1.S-77841 ID 0xfd561a70 LAD:1

One far sync

ARC3 (PID:3874): Archived Log entry 712212 added for T-1.S-77842 ID 0xfd561a70 LAD:1

LAD:1 corresponds to log_archive_dest_1 and has location=use_db_recovery_file_dest.

LAD entries related remote destinations are not visible in the alert logs of primary, cascade standby and far sync instasnces.

Further testing has shown that this change (missing alert log entries for remote LAD) started with DBRU 19.11 and could be seen on both 19.12 and 19.13.

ORA-16856: transport lag could not be determined on Far Sync After Archive Logs Delete With DBRU 19.12 Applied

The behaviour mentioned in this post only started to appear after applying the DBRU 19.12. Prior to applying the DBRU there was no issue of ORA-16856 with the combination of conditions mentioned in the post. System has been in operation for sometime.
After applying DBRU 19.12 noticed increase number of alerts related to ORA-16856 coming from a data guard configuration that has far sync instance in it. Behaviour was observed not one but several such environments. On the data guard broker log of the primary DB entries similar to below could be seen.

2021-08-19T14:08:17.271+00:00
Data Guard Broker Status Summary:
Type Name Severity Status
Configuration tst_dg Warning ORA-16608: one or more members have warnings
Primary Database tstdb1 Success ORA-0: normal, successful completion
Physical Standby Database tstdb2 Success ORA-0: normal, successful completion
Far Sync Instance tstfs1 Warning ORA-16856: transport lag could not be determined
Physical Standby Database tstdb6 Success ORA-0: normal, successful completion
Physical Standby Database tstdb5 Success ORA-0: normal, successful completion

No ORA-16856 related entreis were shown on the far sync instance logs. The data guard broker goes to a warning state for several minutes and rectify itself aftwards.
Analysis of the data guard broker logs showed that ORA-16856 was starting at the same minute of the hour and every hour. Further investigation showed that that first occurence of ORA-16856 corresponds to archive log deletion on the far sync instance. Far sync had archive deletion policy set to applied on all standby (later it was found that archive log deletion policy has no relavence to this issue) and deleting all the archive logs.
There were no overlapping rman activities between far sync and primary or other standby instances. Neverthless the archive log deletion on far sync was shifted to a different time. After that the ORA-16856 error started to appear at the newly scheduled time for the archive log deletion on far sync. This confirmed that issue is caused by the archive log deletion on far sync.
At this stage, looked at when did the new pattern of ORA-16856 started. It was confirmed current pattern of (every hour at the same minute) ORA-16856 startd after the DBRU 19.12 was applied. As the system has been operational for number of years there was enough historic log entries to compare against to see if pattern was there before as well. It was not.
To further isolate the possible root cause, archive log deletion was stopped on far sync in one of the non critical environments that exhibit the behaviour. Once the archive log deletion was stopped there was no recurring ORA-16856 as before. This indicated the log archive deletion could be the possible root cause.
As the next step the log archive deletion command was changed from

delete noprompt archivelog all;

to

delete noprompt archivelog all completed before 'sysdate - 1/24';

With this not all the archive logs were getting deleted. Some were omitted from the deletion as they fell out side the completed before time period. After the rman comamnd change the occurence of periodic ORA-16856 also stopped.



After this it was possible to manually recreate the issue, at will, by running a full archive log deletion on the far sync. Issue doesn't happen when all archive logs are deleted on standby databases. It only happens when all archive logs are deleted on the far sync instance.
As a workaround to the problem, in production systems, on far sync instances the archive log deletion command was changed so that some archive logs would always remain.
Hypothesis was that to calculate the transport lag far sync instnace uses archive log entries. Removing all of them makes it unable to calculate the transport lag hence the ORA-16856.
Raised a SR and Oracle support asked to recreate the issue with 'TraceLevel' = 'SUPPORT' in the DG broker. With this trace level when the issue was recreated far sync DG broker log also output the line related to ORA-16856 as shown below.

2021-10-01T11:10:21.900+00:00 DMON: Checking critical status of this database.
2021-10-01T11:10:21.900+00:00 00000000 1128079456 DMON: Broker configuration file successfully loaded for EDIT CONFIGURATION RE/SET PROPERTY
2021-10-01T11:10:21.900+00:00 INSV: Reply received for message with
2021-10-01T11:10:21.901+00:00 req ID 1.1.1128079456, opcode EDIT_DRC_PROP, phase RESYNCH
2021-10-01T11:10:21.901+00:00 00000000 1128079456 DMON: rfm_release_chief_lock() called for EDIT_DRC_PROP, called from rfmspd
2021-10-01T11:11:50.183+00:00 RSM0: HEALTH CHECK WARNING: ORA-16856: transport lag could not be determined
2021-10-01T11:11:50.234+00:00 03001000 1447213588 Operation HEALTH_CHECK continuing with warning, status = ORA-16856
2021-10-01T11:11:50.235+00:00 03001000 1447213588 Operation HEALTH_CHECK continuing with warning, status = ORA-16856

SR has resulted in an internal bug 33427973. No permanent solution yet. SR continues.

Flashback PDB in a Multi Standby Data Guard Configuraiton

There is an earlier post which looks flashback use with various configuration such as PDB, non-CDB and CDB on 12.2. This post looks flashback use when multiple standbys are invovlved and when each are open in different modes. The 19c release introduced a change which flashback the standby when primary is flashback. Therefore unlike the previous versions, there's no need to flashback the standby PDB after the primary is flashback. However, this transparent flashback of standby PDB doesn't work if any of them are open in read only mode at the time of primary flashback.
The DG configuration consits of 3 instances.

DGMGRL> show configuration

Configuration - test_dg

  Protection Mode: MaxAvailability
  Members:
  dgtest  - Primary database
    dgtest2 - Physical standby database
    dgtest3 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 34 seconds ago)

The dgtest2 instance is open in read only mode.

SQL> select open_mode,db_unique_name from v$database;

OPEN_MODE            DB_UNIQUE_NAME
-------------------- --------------
READ ONLY WITH APPLY dgtest2

DGMGRL> validate database dgtest2

  Database Role:     Physical standby database
  Primary Database:  dgtest

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Managed by Clusterware:
    dgtest :  YES
    dgtest2:  YES

The dgtest3 is open in mount mode.

SQL>  select open_mode,db_unique_name from v$database;

OPEN_MODE            DB_UNIQUE_NAME
-------------------- --------------
MOUNTED              dgtest3

DGMGRL> validtae database dgtest3
Unrecognized command "validtae", try "help"
DGMGRL> validate database dgtest3

  Database Role:     Physical standby database
  Primary Database:  dgtest

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Managed by Clusterware:
    dgtest :  YES
    dgtest3:  YES

To verify the flashback works correctly, create a guarantee restore point, truncate a table and flashback the PDB in primary.

SQL> alter session set container=dgpdb;

Session altered.

SQL> create restore point pdb_restore_point guarantee flashback database;

Restore point created.

truncate table x;

SQL> alter session set container=dgpdb;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
DGPDB

SQL> alter pluggable database close;

Pluggable database altered.


SQL> flashback pluggable database DGPDB to restore point PDB_RESTORE_POINT;

Flashback complete.


SQL> alter pluggable database open resetlogs;

Pluggable database altered.

On the primary DB alert log could see the flashback activity on the PDB

2021-09-23T13:27:24.756572+00:00
DGPDB(3):alter pluggable database close
2021-09-23T13:27:24.780090+00:00
DGPDB(3):Pluggable database DGPDB closing
DGPDB(3):JIT: pid 14510 requesting stop
DGPDB(3):Closing sequence subsystem (10152431219).
DGPDB(3):Buffer Cache flush started: 3
DGPDB(3):Buffer Cache flush finished: 3
Pluggable database DGPDB closed
DGPDB(3):Completed: alter pluggable database close
2021-09-23T13:27:52.164215+00:00
DGPDB(3):flashback database to restore point PDB_RESTORE_POINT
DGPDB(3):ORA-65040 signalled during: flashback database to restore point PDB_RESTORE_POINT...
2021-09-23T13:28:16.884252+00:00
DGPDB(3):flashback pluggable database DGPDB to restore point PDB_RESTORE_POINT
2021-09-23T13:28:17.044790+00:00
DGPDB(3):Flashback Restore Start
DGPDB(3):Restore Flashback Pluggable Database DGPDB (3) until change 4361208
DGPDB(3):Flashback Restore Complete
DGPDB(3):Flashback Media Recovery Start
2021-09-23T13:28:17.169821+00:00
DGPDB(3):Serial Media Recovery started
DGPDB(3):max_pdb is 3
2021-09-23T13:28:17.262801+00:00
DGPDB(3):Recovery of Online Redo Log: Thread 1 Group 1 Seq 22 Reading mem 0
DGPDB(3):  Mem# 0: +DATA/DGTEST/ONLINELOG/group_1.264.1067878075
DGPDB(3):  Mem# 1: +FRA/DGTEST/ONLINELOG/group_1.418.1067878077
2021-09-23T13:28:17.285159+00:00
DGPDB(3):Incomplete Recovery applied until change 4361208 time 09/23/2021 13:25:36
DGPDB(3):Flashback Media Recovery Complete
DGPDB(3):Flashback Pluggable Database DGPDB (3) recovered until change 4361208
DGPDB(3):Completed: flashback pluggable database DGPDB to restore point PDB_RESTORE_POINT
2021-09-23T13:29:06.616625+00:00
Thread 1 advanced to log sequence 23 (LGWR switch),  current SCN: 4362053
  Current log# 2 seq# 23 mem# 0: +DATA/DGTEST/ONLINELOG/group_2.262.1067878077
  Current log# 2 seq# 23 mem# 1: +FRA/DGTEST/ONLINELOG/group_2.419.1067878079
2021-09-23T13:29:06.733706+00:00
ARC8 (PID:13201): Archived Log entry 800 added for T-1.S-22 ID 0xf0d12d4d LAD:1
2021-09-23T13:29:22.740922+00:00
Control autobackup written to DISK device

handle '+FRA/DGTEST/AUTOBACKUP/2021_09_23/s_1084022962.365.1084022963'

2021-09-23T13:29:56.709000+00:00
DGPDB(3):alter pluggable database open resetlogs
2021-09-23T13:29:56.810137+00:00
Online datafile 11
Online datafile 10
Online datafile 9
Online datafile 8
DGPDB(3):Pluggable database DGPDB pseudo opening
DGPDB(3):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
DGPDB(3):Autotune of undo retention is turned on.
DGPDB(3):Endian type of dictionary set to little
DGPDB(3):Undo initialization recovery: Parallel FPTR failed: start:10304853 end:10304857 diff:4 ms (0.0 seconds)
DGPDB(3):Undo initialization recovery: err:0 start: 10304850 end: 10304912 diff: 62 ms (0.1 seconds)
DGPDB(3):[14628] Successfully onlined Undo Tablespace 2.
DGPDB(3):Undo initialization online undo segments: err:0 start: 10304912 end: 10305010 diff: 98 ms (0.1 seconds)
DGPDB(3):Undo initialization finished serial:0 start:10304850 end:10305014 diff:164 ms (0.2 seconds)
DGPDB(3):Database Characterset for DGPDB is AL32UTF8
DGPDB(3):Pluggable database DGPDB pseudo closing
DGPDB(3):JIT: pid 14628 requesting stop
DGPDB(3):Closing sequence subsystem (10305197570).
2021-09-23T13:29:57.761732+00:00
DGPDB(3):Buffer Cache flush started: 3
2021-09-23T13:29:57.831904+00:00
DGPDB(3):Buffer Cache flush finished: 3
DGPDB(3):Pluggable database DGPDB opening in read write
DGPDB(3):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
DGPDB(3):Autotune of undo retention is turned on.
DGPDB(3):Endian type of dictionary set to little
DGPDB(3):Undo initialization recovery: Parallel FPTR complete: start:10305988 end:10305999 diff:11 ms (0.0 seconds)
DGPDB(3):Undo initialization recovery: err:0 start: 10305987 end: 10305999 diff: 12 ms (0.0 seconds)
DGPDB(3):[14628] Successfully onlined Undo Tablespace 2.
DGPDB(3):Undo initialization online undo segments: err:0 start: 10305999 end: 10306236 diff: 237 ms (0.2 seconds)
DGPDB(3):Undo initialization finished serial:0 start:10305987 end:10306247 diff:260 ms (0.3 seconds)
DGPDB(3):Pluggable database DGPDB dictionary check beginning
2021-09-23T13:29:58.820047+00:00
DGPDB(3):Pluggable Database DGPDB Dictionary check complete
2021-09-23T13:29:58.831530+00:00
DGPDB(3):Database Characterset for DGPDB is AL32UTF8
DGPDB(3):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x18, new 0x0 (no suplog)
2021-09-23T13:30:00.195291+00:00
DGPDB(3):Opening pdb with no Resource Manager plan active
DGPDB(3):joxcsys_required_dirobj_exists: directory object exists with required path /opt/app/oracle/product/19.x.0/dbhome_2/javavm/admin/, pid 14628 cid 3
Control autobackup written to DISK device

handle '+FRA/DGTEST/AUTOBACKUP/2021_09_23/s_1084023000.364.1084023001'

Pluggable database DGPDB closed
DGPDB(3):Completed: alter pluggable database open resetlogs

On the alter log of the standby instance open in mount mode (dgtest3) entries show flashback happening transparaently.

2021-09-23T13:29:56.669475+00:00
(3):Recovery of pluggable database DGPDB aborted due to pluggable database open resetlog marker.
(3):To continue recovery, restore all data files for this PDB to checkpoint SCN lower than 4361208, or timestamp before 09/23/2021 13:25:36, and restart recovery
PR00 (PID:13309): MRP0: Detected orphaned datafiles!
PR00 (PID:13309): Recovery will possibly be retried after pluggable database flashback...
2021-09-23T13:29:56.670731+00:00
Errors in file /opt/app/oracle/diag/rdbms/dgtest3/dgtest3/trace/dgtest3_pr00_13309.trc:
ORA-39874: Pluggable Database DGPDB recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 4361208.
PR00 (PID:13309): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.46).
Datafiles are recovered to a consistent state at change 4363117 but controlfile could be ahead of datafiles.
Stopping change tracking
2021-09-23T13:29:56.878879+00:00
Errors in file /opt/app/oracle/diag/rdbms/dgtest3/dgtest3/trace/dgtest3_pr00_13309.trc:
ORA-39874: Pluggable Database DGPDB recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 4361208.
2021-09-23T13:30:16.929698+00:00
MRP0 (PID:13302): Recovery coordinator performing automatic flashback of pluggable database 3 to SCN:0x0000000000428bf7 (4361207)
Flashback Restore Start
Restore Flashback Pluggable Database DGPDB (3) until change 4356167
Flashback Restore Complete
 Started logmerger process
2021-09-23T13:30:17.324525+00:00
PR00 (PID:14265): Managed Standby Recovery starting Real Time Apply
max_pdb is 3
2021-09-23T13:30:17.694838+00:00
Parallel Media Recovery started with 8 slaves
2021-09-23T13:30:17.739944+00:00
Stopping change tracking
2021-09-23T13:30:17.802774+00:00
PR00 (PID:14265): Media Recovery Log +FRA/DGTEST3/ARCHIVELOG/2021_09_23/thread_1_seq_21.285.1084022085
2021-09-23T13:30:17.975446+00:00
PR00 (PID:14265): Media Recovery Log +FRA/DGTEST3/ARCHIVELOG/2021_09_23/thread_1_seq_22.282.1084022947
2021-09-23T13:30:18.373527+00:00
PR00 (PID:14265): Media Recovery Waiting for T-1.S-23 (in transit)
2021-09-23T13:30:18.378327+00:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 23 Reading mem 0
  Mem# 0: +FRA/DGTEST3/ONLINELOG/group_4.461.1069533181
(3):Applying tablespace dictionary check redo for tablespace #0
(3):Applying tablespace dictionary check redo for tablespace #1
(3):Applying tablespace dictionary check redo for tablespace #2
(3):Applying tablespace dictionary check redo for tablespace #3
(3):Dropping offline tempfile '+DATA'
(3):Applying tablespace dictionary check redo for tablespace #4
2021-09-23T13:30:19.374861+00:00
(3):Applying datafile dictionary check redo for datafile #8
2021-09-23T13:30:19.431584+00:00
(3):Applying datafile dictionary check redo for datafile #9
(3):Applying datafile dictionary check redo for datafile #10
(3):Applying datafile dictionary check redo for datafile #11

However, on the alert log of the standby instance open in read only mode (dgtest2) shows recovery process shutting down.

2021-09-23T13:29:56.441259+00:00
(3):Recovery of pluggable database DGPDB aborted due to pluggable database open resetlog marker.
(3):To continue recovery, restore all data files for this PDB to checkpoint SCN lower than 4361208, or timestamp before 09/23/2021 13:25:36, and restart recovery
PR00 (PID:12442): MRP0: Detected orphaned datafiles!
PR00 (PID:12442): Recovery will possibly be retried after pluggable database flashback...
2021-09-23T13:29:56.442191+00:00
Errors in file /opt/app/oracle/diag/rdbms/dgtest2/dgtest2/trace/dgtest2_pr00_12442.trc:
ORA-39874: Pluggable Database DGPDB recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 4361208.
PR00 (PID:12442): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 4363117
Stopping change tracking
2021-09-23T13:29:56.585631+00:00
Errors in file /opt/app/oracle/diag/rdbms/dgtest2/dgtest2/trace/dgtest2_pr00_12442.trc:
ORA-39874: Pluggable Database DGPDB recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 4361208.
2021-09-23T13:30:17.728791+00:00
MRP0 (PID:12431): Recovery coordinator encountered one or more errors during automatic flashback on standby
2021-09-23T13:30:17.728863+00:00
Background Media Recovery process shutdown (dgtest2)

This is expected as flashback cannot happen when database is in open mode even if it is read only. The DG broker shows an error state.

DGMGRL> show configuration

Configuration - test_dg

  Protection Mode: MaxAvailability
  Members:
  dgtest  - Primary database
    dgtest2 - Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the member

    dgtest3 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 48 seconds ago)

DGMGRL> show database dgtest2

Database - dgtest2

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          2 minutes 15 seconds (computed 1 second ago)
  Average Apply Rate: 20.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    dgtest2

  Database Error(s):
    ORA-16766: Redo Apply is stopped

  Database Warning(s):
    ORA-16853: apply lag has exceeded specified threshold

Database Status:
ERROR

Stopping and starting the PDB on the standby instance does not start the recovery process.

Stopping and starting the apply process (apply-off/on) on the standby instance also does not start the recovery process.

Keeping the PDB in mount mode and stopping and starting the apply service also doesn't start the recovery process.

Trying to flashback the PDB while PDB is in mount also fails.

SQL> flashback pluggable database to scn 4361208;
flashback pluggable database to scn 4361208
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance

Intresting the error says DB must be in mount mode even while it's mounted. What it actually refers here is that the CDB must also be in a mounted state not just the PDB.

To resolve the error and start the recovery process stop the CDB and start it in mount mode.

srvctl stop database -db $ORACLE_SID
srvctl start database -db $ORACLE_SID -startoption mount

This will result in flashback happening transparently on the PDB similar to instance that was in mount mode and apply process will continue without any issue.
After recovery on the standby has caught up with primary, it could be open in read only mode again.

Related Post
Flashback Primary DB in a Data Guard - PDB vs non-CDB vs CDB

Patching OS in DBCS VM DB Using DBCLI

There's an earlier post which shows updating and patching the OS. This method used yum to update the OS. It is still valid and available in documentation as well.
Recently Oracle introduced a new way to patch the OS, that is using the dbcli. The dbcli is the tool to use if patching the DB is done using command line. With this new enhancement same tool dbcli could be used to patching and updating the OS as well.
1. To check if any OS updates are available run the get-availableospatches command. If patches are not available updateAvailable will show false.

#  dbcli get-availableospatches -j
{
  "updateAvailable" : false,
  "rebootIsRequired" : false,
  "updateableRpms" : null,
  "installedRpms" : null
}

2. If updates are available then it will show updateAvailable true and also if a reboot is required after update.

dbcli get-availableospatches
Update Available     Reboot Required
-------------------- --------------------
Yes                  Yes

To get a list of rpms update use -j option.

bcli get-availableospatches -j
{
  "updateAvailable" : true,
  "rebootIsRequired" : true,
  "updateableRpms" : [ "bind.x86_64::32:9.11.4-26.P2.el7_9.5", "bind-export-libs.x86_64::32:9.11.4-26.P2.el7_9.5", "bind-libs.x86_64::32:9.11.4-26.P2.el7_9.5", "bind-libs-lite.x86_64::32:9.11.4-26.P2.el7_9.5", "bind-license.noarch::32:  9.11.4-26.P2.el7_9.5", "bind-utils.x86_64::32:9.11.4-26.P2.el7_9.5", "curl.x86_64::7.29.0-59.0.3.el7_9.1", "device-mapper.x86_64::7:1.02.170-6.0.5.el7_9.5", "device-mapper-event.x86_64::7:1.02.170-6.0.5.el7_9.5", "device-mapper-event-l  ibs.x86_64::7:1.02.170-6.0.5.el7_9.5", "device-mapper-libs.x86_64::7:1.02.170-6.0.5.el7_9.5", "device-mapper-multipath.x86_64::0.4.9-134.0.3.el7_9", "device-mapper-multipath-libs.x86_64::0.4.9-134.0.3.el7_9", "dhclient.x86_64::12:4.2.5  -83.0.1.el7_9.1", "dhcp.x86_64::12:4.2.5-83.0.1.el7_9.1", "dhcp-common.x86_64::12:4.2.5-83.0.1.el7_9.1", "dhcp-libs.x86_64::12:4.2.5-83.0.1.el7_9.1", "dmidecode.x86_64::1:3.2-5.0.1.el7_9.1", "glib2.x86_64::2.56.1-9.el7_9", "glibc.i686:  :2.17-324.0.1.el7_9", "glibc.x86_64::2.17-324.0.1.el7_9", "glibc-common.x86_64::2.17-324.0.1.el7_9", "glibc-devel.x86_64::2.17-324.0.1.el7_9", "glibc-headers.x86_64::2.17-324.0.1.el7_9", "grub2.x86_64::1:2.02-0.87.0.9.el7_9.6", "grub2-  common.noarch::1:2.02-0.87.0.9.el7_9.6", "grub2-efi-x64.x86_64::1:2.02-0.87.0.9.el7_9.6", "grub2-pc.x86_64::1:2.02-0.87.0.9.el7_9.6", "grub2-pc-modules.noarch::1:2.02-0.87.0.9.el7_9.6", "grub2-tools.x86_64::1:2.02-0.87.0.9.el7_9.6", "g  rub2-tools-extra.x86_64::1:2.02-0.87.0.9.el7_9.6", "grub2-tools-minimal.x86_64::1:2.02-0.87.0.9.el7_9.6", "gssproxy.x86_64::0.7.0-30.el7_9", "hwdata.x86_64::0.252-9.7.0.1.el7", "iscsi-initiator-utils.x86_64::6.2.0.874-20.0.5.el7_9", "i  scsi-initiator-utils-iscsiuio.x86_64::6.2.0.874-20.0.5.el7_9", "kernel-devel.x86_64::3.10.0-1160.36.2.el7", "kernel-headers.x86_64::3.10.0-1160.36.2.el7", "kernel-uek.x86_64::4.1.12-124.53.5.el7uek", "kernel-uek-firmware.noarch::4.1.12  -124.53.5.el7uek", "kexec-tools.x86_64::2.0.15-51.0.3.el7_9.3", "kpartx.x86_64::0.4.9-134.0.3.el7_9", "krb5-libs.x86_64::1.15.1-50.0.1.el7", "libcurl.x86_64::7.29.0-59.0.3.el7_9.1", "libgudev1.x86_64::219-78.0.5.el7_9.3", "libipa_hbac.  x86_64::1.16.5-10.0.1.el7_9.8", "libldb.x86_64::1.5.4-2.el7_9", "libsmbclient.x86_64::4.10.16-15.el7_9", "libsss_autofs.x86_64::1.16.5-10.0.1.el7_9.8", "libsss_certmap.x86_64::1.16.5-10.0.1.el7_9.8", "libsss_idmap.x86_64::1.16.5-10.0.1  .el7_9.8", "libsss_nss_idmap.x86_64::1.16.5-10.0.1.el7_9.8", "libsss_sudo.x86_64::1.16.5-10.0.1.el7_9.8", "libwbclient.x86_64::4.10.16-15.el7_9", "libxml2.i686::2.9.1-6.0.3.el7.5", "libxml2.x86_64::2.9.1-6.0.3.el7.5", "libxml2-python.x  86_64::2.9.1-6.0.3.el7.5", "linux-firmware.noarch::999:20210617-999.8.git0f66b74b.el7", "lvm2.x86_64::7:2.02.187-6.0.5.el7_9.5", "lvm2-libs.x86_64::7:2.02.187-6.0.5.el7_9.5", "mokutil.x86_64::15.3-1.0.5.el7", "nettle.x86_64::2.7.1-9.el  7_9", "nfs-utils.x86_64::1:1.3.0-0.68.0.1.el7.1", "nss.x86_64::3.53.1-7.el7_9", "nss-sysinit.x86_64::3.53.1-7.el7_9", "nss-tools.x86_64::3.53.1-7.el7_9", "openldap.x86_64::2.4.44-23.el7_9", "openssl.x86_64::1:1.0.2k-21.0.3.el7_9", "ope  nssl-libs.x86_64::1:1.0.2k-21.0.3.el7_9", "oracle-database-preinstall-19c.x86_64::1.0-3.el7", "pciutils.x86_64::3.5.1-3.0.1.el7", "pciutils-libs.x86_64::3.5.1-3.0.1.el7", "python.x86_64::2.7.5-90.0.3.el7", "python-libs.x86_64::2.7.5-90  .0.3.el7", "python-sssdconfig.noarch::1.16.5-10.0.1.el7_9.8", "redhat-release-server.x86_64::1:7.9-6.0.1.el7_9", "rsyslog.x86_64::8.24.0-57.0.1.el7_9.1", "samba-client-libs.x86_64::4.10.16-15.el7_9", "samba-common.noarch::4.10.16-15.el  7_9", "samba-common-libs.x86_64::4.10.16-15.el7_9", "screen.x86_64::4.1.0-0.27.20120314git3c2946.el7_9", "selinux-policy.noarch::3.13.1-268.0.13.el7_9.2", "selinux-policy-targeted.noarch::3.13.1-268.0.13.el7_9.2", "shim-x64.x86_64::15.  3-1.0.5.el7", "sos.noarch::3.9-5.0.11.el7_9.6", "sssd.x86_64::1.16.5-10.0.1.el7_9.8", "sssd-ad.x86_64::1.16.5-10.0.1.el7_9.8", "sssd-client.x86_64::1.16.5-10.0.1.el7_9.8", "sssd-common.x86_64::1.16.5-10.0.1.el7_9.8", "sssd-common-pac.x  86_64::1.16.5-10.0.1.el7_9.8", "sssd-ipa.x86_64::1.16.5-10.0.1.el7_9.8", "sssd-krb5.x86_64::1.16.5-10.0.1.el7_9.8", "sssd-krb5-common.x86_64::1.16.5-10.0.1.el7_9.8", "sssd-ldap.x86_64::1.16.5-10.0.1.el7_9.8", "sssd-proxy.x86_64::1.16.5  -10.0.1.el7_9.8", "systemd.x86_64::219-78.0.5.el7_9.3", "systemd-libs.x86_64::219-78.0.5.el7_9.3", "systemd-sysv.x86_64::219-78.0.5.el7_9.3", "unzip.x86_64::6.0-22.el7_9" ],
  "installedRpms" : [ "bind.x86_64::32:9.11.4-26.P2.el7_9.3", "bind-export-libs.x86_64::32:9.11.4-26.P2.el7_9.3", "bind-libs.x86_64::32:9.11.4-26.P2.el7_9.3", "bind-libs-lite.x86_64::32:9.11.4-26.P2.el7_9.3", "bind-license.noarch::32:9  .11.4-26.P2.el7_9.3", "bind-utils.x86_64::32:9.11.4-26.P2.el7_9.3", "curl.x86_64::7.29.0-59.0.1.el7_9.1", "device-mapper.x86_64::7:1.02.170-6.0.3.el7_9.3", "device-mapper-event.x86_64::7:1.02.170-6.0.3.el7_9.3", "7:1.02.170-6.0.3.el7_9  .3::@pdit_ol7_latest/$releasever", "device-mapper-libs.x86_64::7:1.02.170-6.0.3.el7_9.3", "0.4.9-134.0.1.el7_9::@pdit_ol7_latest/$releasever", "0.4.9-134.0.1.el7_9::@pdit_ol7_latest/$releasever", "dhclient.x86_64::12:4.2.5-82.0.1.el7",   "dhcp.x86_64::12:4.2.5-82.0.1.el7", "dhcp-common.x86_64::12:4.2.5-82.0.1.el7", "dhcp-libs.x86_64::12:4.2.5-82.0.1.el7", "dmidecode.x86_64::1:3.2-5.el7", "glib2.x86_64::2.56.1-8.el7", "glibc.i686::2.17-322.0.1.el7_9", "glibc.x86_64::2.  17-322.0.1.el7_9", "glibc-common.x86_64::2.17-322.0.1.el7_9", "glibc-devel.x86_64::2.17-322.0.1.el7_9", "glibc-headers.x86_64::2.17-322.0.1.el7_9", "grub2.x86_64::1:2.02-0.87.0.5.el7", "grub2-common.noarch::1:2.02-0.87.0.5.el7", "grub2  -efi-x64.x86_64::1:2.02-0.87.0.5.el7", "grub2-pc.x86_64::1:2.02-0.87.0.5.el7", "grub2-pc-modules.noarch::1:2.02-0.87.0.5.el7", "grub2-tools.x86_64::1:2.02-0.87.0.5.el7", "grub2-tools-extra.x86_64::1:2.02-0.87.0.5.el7", "grub2-tools-min  imal.x86_64::1:2.02-0.87.0.5.el7", "gssproxy.x86_64::0.7.0-29.el7", "hwdata.x86_64::0.252-9.7.el7", "6.2.0.874-20.0.1.el7_9::@pdit_ol7_latest/$releasever", "6.2.0.874-20.0.1.el7_9::@pdit_ol7_latest/$releasever", "kernel-devel.x86_64::3  .10.0-1160.15.2.el7", "kernel-headers.x86_64::3.10.0-1160.15.2.el7", "kernel-uek.x86_64::4.1.12-124.47.3.el7uek", "kernel-uek-firmware.noarch::4.1.12-124.47.3.el7uek", "kexec-tools.x86_64::2.0.15-51.0.3.el7_9.1", "kpartx.x86_64::0.4.9-  134.0.1.el7_9", "krb5-libs.x86_64::1.15.1-50.el7", "libcurl.x86_64::7.29.0-59.0.1.el7_9.1", "libgudev1.x86_64::219-78.0.1.el7_9.3", "libipa_hbac.x86_64::1.16.5-10.0.1.el7_9.7", "libldb.x86_64::1.5.4-1.el7", "libsmbclient.x86_64::4.10.1  6-9.el7_9", "libsss_autofs.x86_64::1.16.5-10.0.1.el7_9.7", "libsss_certmap.x86_64::1.16.5-10.0.1.el7_9.7", "libsss_idmap.x86_64::1.16.5-10.0.1.el7_9.7", "libsss_nss_idmap.x86_64::1.16.5-10.0.1.el7_9.7", "libsss_sudo.x86_64::1.16.5-10.0  .1.el7_9.7", "libwbclient.x86_64::4.10.16-9.el7_9", "libxml2.i686::2.9.1-6.0.1.el7.5", "libxml2.x86_64::2.9.1-6.0.1.el7.5", "libxml2-python.x86_64::2.9.1-6.0.1.el7.5", "linux-firmware.noarch::999:20200902-999.5.gitd5f9eea5.el7", "lvm2.  x86_64::7:2.02.187-6.0.3.el7_9.3", "lvm2-libs.x86_64::7:2.02.187-6.0.3.el7_9.3", "mokutil.x86_64::15-2.0.9.el7", "nettle.x86_64::2.7.1-8.el7", "nfs-utils.x86_64::1:1.3.0-0.68.0.1.el7", "nss.x86_64::3.53.1-3.el7_9", "nss-sysinit.x86_64:  :3.53.1-3.el7_9", "nss-tools.x86_64::3.53.1-3.el7_9", "openldap.x86_64::2.4.44-22.el7", "openssl.x86_64::1:1.0.2k-21.el7_9", "openssl-libs.x86_64::1:1.0.2k-21.el7_9", "1.0-2.el7::@pdit_ol7_latest/$releasever", "pciutils.x86_64::3.5.1-3  .el7", "pciutils-libs.x86_64::3.5.1-3.el7", "python.x86_64::2.7.5-90.0.1.el7", "python-libs.x86_64::2.7.5-90.0.1.el7", "python-sssdconfig.noarch::1.16.5-10.0.1.el7_9.7", "1:7.9-3.0.1.el7::@pdit_ol7_latest/$releasever", "rsyslog.x86_64:  :8.24.0-57.el7_9", "samba-client-libs.x86_64::4.10.16-9.el7_9", "samba-common.noarch::4.10.16-9.el7_9", "samba-common-libs.x86_64::4.10.16-9.el7_9", "screen.x86_64::4.1.0-0.26.20120314git3c2946.el7", "selinux-policy.noarch::3.13.1-268.  0.1.el7_9.2", "3.13.1-268.0.1.el7_9.2::@pdit_ol7_latest/$releasever", "shim-x64.x86_64::15-2.0.9.el7", "sos.noarch::3.9-5.0.3.el7_9.2", "sssd.x86_64::1.16.5-10.0.1.el7_9.7", "sssd-ad.x86_64::1.16.5-10.0.1.el7_9.7", "sssd-client.x86_64:  :1.16.5-10.0.1.el7_9.7", "sssd-common.x86_64::1.16.5-10.0.1.el7_9.7", "sssd-common-pac.x86_64::1.16.5-10.0.1.el7_9.7", "sssd-ipa.x86_64::1.16.5-10.0.1.el7_9.7", "sssd-krb5.x86_64::1.16.5-10.0.1.el7_9.7", "sssd-krb5-common.x86_64::1.16.  5-10.0.1.el7_9.7", "sssd-ldap.x86_64::1.16.5-10.0.1.el7_9.7", "sssd-proxy.x86_64::1.16.5-10.0.1.el7_9.7", "systemd.x86_64::219-78.0.1.el7_9.3", "systemd-libs.x86_64::219-78.0.1.el7_9.3", "systemd-sysv.x86_64::219-78.0.1.el7_9.3", "unzi  p.x86_64::6.0-21.el7" ]
}

3. Note the kernel before the update.

uname -r
4.1.12-124.47.3.el7uek.x86_64

4. Run the pre-update check for the os.

dbcli update-server -c os -p
{
  "jobId" : "9b02cf04-92a2-4299-8cd0-4ce649b962ec",
  "status" : "Created",
  "message" : null,
  "reports" : [ ],
  "createTimestamp" : "September 24, 2021 14:18:46 PM BST",
  "resourceList" : [ ],
  "description" : "OS Patching Prechecks",
  "updatedTime" : "September 24, 2021 14:18:47 PM BST",
  "percentageProgress" : "0%",
  "cause" : null,
  "action" : null
}

Check if pre-udpate check is successful.

dbcli describe-job -i "9b02cf04-92a2-4299-8cd0-4ce649b962ec"

Job details
----------------------------------------------------------------
                     ID:  9b02cf04-92a2-4299-8cd0-4ce649b962ec
            Description:  OS Patching Prechecks
                 Status:  Success
                Created:  September 24, 2021 2:18:46 PM BST
               Progress:  100%
                Message:

Task Name                                                                Start Time                          End Time                            Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------

5. Run the os update.

dbcli update-server -c os
{
  "jobId" : "a7097060-6234-401d-bfd7-1c1fcbb9de85",
  "status" : "Created",
  "message" : null,
  "reports" : [ ],
  "createTimestamp" : "September 24, 2021 15:41:34 PM BST",
  "resourceList" : [ ],
  "description" : "OS Patching",
  "updatedTime" : "September 24, 2021 15:41:34 PM BST",
  "percentageProgress" : "0%",
  "cause" : null,
  "action" : null
}

The first run of the OS update failed with below output..

dbcli describe-job -i "a7097060-6234-401d-bfd7-1c1fcbb9de85"

Job details
----------------------------------------------------------------
                     ID:  a7097060-6234-401d-bfd7-1c1fcbb9de85
            Description:  OS Patching
                 Status:  Failure
                Created:  September 24, 2021 3:41:34 PM BST
               Progress:  78%
                Message:  DCS-10206:Patch bundle conflicts with: Yum Failed to resolve conflict
                  Cause:  Conflict check failed.
                 Action:  Rollback the conflicting patches and retry the operation.

Task Name                                                                Start Time                          End Time                            Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------
OS Component Patching                                                    September 24, 2021 3:41:34 PM BST   September 24, 2021 4:22:04 PM BST   Failure
task:TaskSequential_919                                                  September 24, 2021 3:56:45 PM BST   September 24, 2021 4:22:04 PM BST   Failure
Validating OS Patches                                                    September 24, 2021 3:56:45 PM BST   September 24, 2021 4:22:04 PM BST   Failure

After a SR Oracle support confimed that updating is hitting a bug (internal 33403308). After the bug was fixed by Oracle development team ran the command again and OS update was successful.

dbcli update-server -c os
{
  "jobId" : "b1ad604f-7961-4854-a05d-9e43958312b7",
  "status" : "Created",
  "message" : null,
  "reports" : [ ],
  "createTimestamp" : "September 29, 2021 09:32:50 AM BST",
  "resourceList" : [ ],
  "description" : "OS Patching",
  "updatedTime" : "September 29, 2021 09:32:51 AM BST",
  "percentageProgress" : "0%",
  "cause" : null,
  "action" : null
}

dbcli describe-job -i "b1ad604f-7961-4854-a05d-9e43958312b7"

Job details
----------------------------------------------------------------
                     ID:  b1ad604f-7961-4854-a05d-9e43958312b7
            Description:  OS Patching
                 Status:  Success
                Created:  September 29, 2021 9:32:50 AM BST
               Progress:  100%
                Message:

Task Name                                                                Start Time                          End Time                            Status
------------------------------------------------------------------------ ----------------------------------- ----------------------------------- ----------

The issue seems to have been fixed permanently as subsequent OS patching of other DB systems didn't have any issue.

6. Reboot the node and verify the kernel is updated.

uname -r
4.1.12-124.53.5.el7uek.x86_

Related Post
Updating the OS and Patching OCI PaaS DB Using DBCLI

Active Data Guard and PDB Parameters

In an earlier post it was shown how undo_retention value in a PDB that is part of an ADG standby could only be changed by updating the value on the primary PDB. In the case of undo_retention parameter Oracle introduced change 30577591 which prevented it from inheriting the value from the standby CDB.
This post looks at the effects of setting parameter values at PDB level in a ADG enviornment. It seems that the PDB in a standby CDB will inherit the init values from cdb$root spfile as long as the parameter is not set at PDB level in the primary.
open_cursors parameter is used as the test case here. The initial values at primary and standby are same and no value has been set at PDB level.

SQL> show parameter open_cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
open_cursors                         integer     3000

On the primary the value is changed at CDB level.

SQL> alter system set open_cursors=3500 scope=both;

System altered.

SQL> show parameter open_cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
open_cursors                         integer     3500

As no value has been set at PDB level it will inherit the value from CDB$root.

SQL> alter session set container=dgpdb;

Session altered.

SQL> show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
open_cursors                         integer     3500

Next the value is changed at PDB level.

SQL> alter session set container=dgpdb;

SQL>  alter system set open_cursors=2500;

System altered.

SQL> show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
open_cursors                         integer     2500

At this stage primary cdb$root has value 3500 and PDB has 2500. No changes has been made to standby and value there remains 3000.



On the standby checking the value in the PDB shows the initial value.

SQL>  alter session set container=dgpdb;

Session altered.

SQL> show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
open_cursors                         integer     3000

Changing the parameter value at CDB level and check value in PDB.

SQL>  alter system set open_cursors=4500 scope=both;

System altered.

SQL>  show parameter open_cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
open_cursors                         integer     4500

Value checked at PDB level.

SQL> alter session set container=dgpdb;

Session altered.

SQL> show parameter open_cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
open_cursors                         integer     4500

This shows value being inherited from standby cdb$root.
Next the PDB in the standby instance is restarted and parameter value checked.

SQL> shutdown ;
Pluggable Database closed.

SQL> startup;
Pluggable Database opened.

SQL> show parameter open_cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
open_cursors                         integer     2500

It not longer shows the 4500 value inherited from standby but the value set at primary PDB. It appears during PDB start the table PDB_SPFILE$ is read for PDB level parameter values. Since PDB_SPFILE$ is a database table, any writes to it will generate redo. As such changes made in primary PDB will cascade to all standby instances and to all relevant PDBs. When values are read from this table they are set for PDB overwriting values inherited from cdb$root spfile.
Any subsequent change to the init parameter at cdb$root doesn't override the value set at PDB level.

SQL> show con_name

CON_NAME
---------
CDB$ROOT

SQL> alter system set open_cursors=4600 scope=both;

System altered.

SQL> alter session set container=dgpdb;

Session altered.

SQL>  show parameter open_cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
open_cursors                         integer     2500

This behaviour, inheriting from local cdb$root spfile vs primary PDB could have consequences for asymmetric data guard deployments where primary and standy differ in the amount of resources (memory,cpu) and user requirments (BI vs OLTP, user counts, etc). If for whatever an init value is set on PDB level at primary, remember that will get applied on all standby PDBs next time those are restarted.