Guardium VA and Oracle 12c datapatch

Resolving The Problem

The post-upgrade datapatch execution in Oracle 12.1.0.2 is not completed.

The solution is to apply the SQL changes manually after the DBA has completed the database upgrade to Oracle Database 12c:

cd $ORACLE_HOME/OPatch

./datapatch -verbose

https://www.pythian.com/blog/oracle-database-12c-patching-dbms_qopatch-opatch_xml_inv-and-datapatch/

With Oracle 11g the latest patch is applied within the database using:

SQL> @catbundle.sql psu apply

Summary

Oracle 12c introduced a new utility called datapatch which replaces the need to run the 11g command shown above.

As with Oracle 11g, you first install the patch into the Oracle Home with all services & databases down. But with Oracle Database 12c, after restarting the database, simply run datapatch from the OPatch directory:

cd $ORACLE_HOME/OPatch

./datapatch -verbose

Now, the customer is saying that they installed a fresh new Oracle 12c and then patched the server. Then they created the database and ran select * from dba_registry_sqlpatch and no record was found. That because they did not run the datapatch step. The customer then questioned whether they need to run datapatch or not since this database is created after the server patch. That is a good question.

Let's look at the possible scenario.

If you don't run datapatch, then dba_registry_sqlpatch will have 0 row. There is no way for Guardium's Vulnerability Assessment to know if you really did patch the database or not as there is no other way to find this information by doing a JDBC connection. That is why when the Guardium test ran, it showed "Unsupported CVE database patch detected" as Guardium found nothing or potentially something that Guardium does not recognize and would not be able to parse it. Guardium cannot simply fail the test as failing it may or may not be the desired result. One can argue that I don't need to run datapatch because this database is created after the server was patched. This assumption may or may not be correct depending on how the database is created.

Therefore, Guardium recommends running the datapatch step regardless. Also this is an abnormal case where it would only happen during the first few months of your database creation. After that, you would need to patch your database on a quarterly basis to satisfy Oracle security, so you will have to run datapatch each time.

In an Oracle database, when the server is patched and a new database is created, here is what happens in dba_registry_sqlpatch

SELECT VERSION, FLAGS, DESCRIPTION, BUNDLE_ID, BUNDLE_SERIES

FROM DBA_REGISTRY_SQLPATCH

WHERE ACTION = 'APPLY'

AND STATUS = 'SUCCESS'

AND VERSION = (SELECT SUBSTR(VERSION,1,LENGTH(VERSION) -2) FROM V$INSTANCE)

AND FLAGS LIKE '%B%'

AND ACTION_TIME = (SELECT MAX(ACTION_TIME)

FROM DBA_REGISTRY_SQLPATCH

WHERE ACTION = 'APPLY'

AND STATUS = 'SUCCESS'

AND VERSION = (SELECT SUBSTR(VERSION,1,LENGTH(VERSION) -2) FROM V$INSTANCE)

AND FLAGS LIKE '%B%');

SELECT VERSION, FLAGS, DESCRIPTION, BUNDLE_ID, BUNDLE_SERIES

FROM DBA_REGISTRY_SQLPATCH

WHERE ACTION = 'APPLY'

AND STATUS = 'SUCCESS'

AND VERSION = (SELECT SUBSTR(VERSION,1,LENGTH(VERSION) -2) FROM V$INSTANCE)

AND FLAGS LIKE '%J%'

AND ACTION_TIME = (SELECT MAX(ACTION_TIME)

FROM DBA_REGISTRY_SQLPATCH

WHERE ACTION = 'APPLY'

AND STATUS = 'SUCCESS'

AND VERSION = (SELECT SUBSTR(VERSION,1,LENGTH(VERSION) -2) FROM V$INSTANCE)

AND FLAGS LIKE '%J%');

VERSION FLAGS DESCRIPTION BUNDLE_ID BUNDLE_SERIES

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

0 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 0ms]

[Executed: 7/12/2016 12:55:47 PM] [Execution: 25ms]

VERSION FLAGS DESCRIPTION BUNDLE_ID BUNDLE_SERIES

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

0 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 0ms]

After running datapatch.

SELECT VERSION, FLAGS, DESCRIPTION, BUNDLE_ID, BUNDLE_SERIES

FROM DBA_REGISTRY_SQLPATCH

WHERE ACTION = 'APPLY'

AND STATUS = 'SUCCESS'

AND VERSION = (SELECT SUBSTR(VERSION,1,LENGTH(VERSION) -2) FROM V$INSTANCE)

AND FLAGS LIKE '%B%'

AND ACTION_TIME = (SELECT MAX(ACTION_TIME)

FROM DBA_REGISTRY_SQLPATCH

WHERE ACTION = 'APPLY'

AND STATUS = 'SUCCESS'

AND VERSION = (SELECT SUBSTR(VERSION,1,LENGTH(VERSION) -2) FROM V$INSTANCE)

AND FLAGS LIKE '%B%');

SELECT VERSION, FLAGS, DESCRIPTION, BUNDLE_ID, BUNDLE_SERIES

FROM DBA_REGISTRY_SQLPATCH

WHERE ACTION = 'APPLY'

AND STATUS = 'SUCCESS'

AND VERSION = (SELECT SUBSTR(VERSION,1,LENGTH(VERSION) -2) FROM V$INSTANCE)

AND FLAGS LIKE '%J%'

AND ACTION_TIME = (SELECT MAX(ACTION_TIME)

FROM DBA_REGISTRY_SQLPATCH

WHERE ACTION = 'APPLY'

AND STATUS = 'SUCCESS'

AND VERSION = (SELECT SUBSTR(VERSION,1,LENGTH(VERSION) -2) FROM V$INSTANCE)

AND FLAGS LIKE '%J%');

VERSION FLAGS DESCRIPTION BUNDLE_ID BUNDLE_SERIES

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

12.1.0.2 NB WINDOWS DB BUNDLE PATCH 12.1.0.2.160119(64bit):22310559 160119 PSU

1 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 0ms]

VERSION FLAGS DESCRIPTION BUNDLE_ID BUNDLE_SERIES

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

12.1.0.2 UJJ WINDOWS ORACLE JAVAVM COMPONENT BUNDLE PATCH 12.1.0.2.160119(64bit):22311086 (null) (null)

1 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 1ms]